Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
 Posted - 2005-02-14 : 01:26:07
|
| Hi all, Is there a way to pass the output of a function to a stored proc with out actually using an intermediate variable to hold the function output.Like..EXEC SP_XYZ @parm = DB_NAME()TIA...Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-14 : 01:34:39
|
You can't have expressions in SProc parameters, can you?Apart from concatenating strings ...... but what does BOL mean with this? (referring to parameter values)quote:
value...If the value of a parameter is an object name, character string, or qualified by a database name or owner name, the entire name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.
Kristen |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2005-02-14 : 05:42:45
|
| Hmm.. I guess not. Tried everywhich way....Anyways, here's what I need this for..We move the stored procedures from DEV to QA and then to PRODUCTION. I want to make sure that the testing team is actually "testing" the stored procedures after they are moved to QA. To do this I want log everytime the stored procedure is executed. !!! 24X7 trace is not an optionSo, every time the proc is moved to QA, I add code (one liner) in it to execute a stored proc X (in ADMIN_ONLY database) which inserts a record in a log table. Now this stored proc X needs two parameters.. database name [db_name()]and proc name [object_name(@@PROCID)]. Now the problems are.... - I can't hard code the parameter as I need the one-liner to be static, so it's easy to search and replace when moving few tens/hundreds of procs. - To pass it in one line code, SQL is not accepting functions as parameters to stored proc.An alternative to this is to put the proc X in each of the user database in the server. The proc would still update the log table in ADMIN_ONLY database.That solved, I, now, still need to give each user access to the ADMIN_ONLY database; which we don't do today.Any ideas to improve this ...Any experiences that you've had with this knida requirement...Any thing, at all (however radical) ;-) ....... are most welcome.Thanks a lot..Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-14 : 12:40:55
|
Can't you put it on one line?DECLARE @strDB varchar(256), @strProcName varchar(256) SELECT @strDB=db_name(), @strProcName=object_name(@@PROCID) EXEC ADMIN_ONLY.dbo.SP_XYZ @strDB, @strProcNameHere's radical then:We log our SProcs all the time.CREATE PROCEDURE MyProc @Param1 int, @Param2 varchar(10), ...ASDECLARE @strParameters varchar(1000), @intLogIDSELECT @strParameters = COALESCE(CONVERT(varchar(20), @Param1), 'NULL') + '~' + COALESCE(@Param2, 'NULL') EXEC @intLogID = USP_LogSProcStart 'MyProc', @strParameters... EXEC USP_LogSProcEnd @intLogID, @intSomeResultCode Thus I can tell which Sprocs have/havenot been executed. The USP_LogSProcStart and USP_LogSProcEnd Sprocs store the Start and End time, so I can calculate average/max elapsed time. I can also report on SProcs that returned error, and so on.USP_LogSProcStart sets an error code of -999 - which USP_LogSProcEnd overwrites; thus any SProcs that Start but never Finish can be listed too - syntax errors, deadlock candidates and so on.Kristen |
|
|
|
|
|
|
|
|
Passing function output as parameter to a SP