| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2012-02-16 : 10:39:38
|
| A few weeks ago I thought I read that there is a better way to call a stored proc from within another proc then by using the EXEC command but I can not find that web page. Am I just miss remembering something or is there a better way to do it?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-16 : 12:54:08
|
| I don't know of a better way of calling a stored procedure other than EXEC. Might you be thinking about dynamic SQL? You can use EXEC or sp_executesql to run dynamic SQL. Most people recommend using sp_executesql over Exec for a variety of reasons. |
 |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-02-16 : 13:09:38
|
| sp_executesql was what I was thinking of, now I just have to figure out how to use it and convert my current EXEC command.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-02-16 : 13:30:32
|
quote:
Originally posted by X002548
EXEC @rc = Sprocname????Brett8-)
Not so easy, I need to pass in 5 paramaters and assign the output. The current is:exec @ErrorNum = dbo.spSubMenuItems @SectionID, 'Information', 'Races', 'Races', 'Races';-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-02-16 : 13:32:58
|
This is what I doDECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; EXEC usp_SEL_PERSON @AKA_ID = 'X002548', @LAST_NAME = null, @FIRST_NAME = null, @User ='X002548', @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount] Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-02-17 : 06:45:44
|
| I did not even look at your run command until just now, was just looking at the variables. Your still using EXEC, I need to figure out how to use sp_executesql to call a stored proc.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-17 : 07:36:40
|
| If you just want to call a stored procedure, what Brett suggested would be the best thing to do. You would use sp_executesql if you needed to use dynamic sql (i.e., you had to construct the query string at runtime). While dynamic sql has its place, most people try to avoid it if they can. If you do want to use it, take a look at Sommerskog's blog - he describes it in great detail, analyzing the "curses and blessings" of it: http://www.sommarskog.se/dynamic_sql.html |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-17 : 07:40:08
|
quote:
Originally posted by Eagle_f90
Your still using EXEC, I need to figure out how to use sp_executesql to call a stored proc.
Why? sp_executesql is for dynamic SQL, it's not for executing stored procedures, EXEC is for procedures.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-17 : 08:33:40
|
You can't do this with EXEC:exec @ErrorNum = dbo.spSubMenuItems @SectionID, 'Inform' + 'ation', 'Races', 'Races', 'Races'; (trivialised example), but then you could do the expression "'Inform' + 'ation'" into a working variable first, and pass that working variable.Slim chance that using sp_ExecuteSQL would make that particular twist easier, but you then wind up having to deal with all the doubled up quotes and stuff, so I don't think that would be any "easier", just "different", and maybe that would suit some people.How do you execute SProcs using sp_ExecuteSQL? Is this OK?EXEC sp_ExecuteSQL N'MySproc @param1=@param1', N'@param1 varchar(10)', @param1 or do you have to do:EXEC sp_ExecuteSQL N'EXEC MySproc @param1=@param1', N'@param1 varchar(10)', @param1 which would rather defeat the object?  note also that the definition of the parameters, in the call to sp_ExecuteSQL, is a duplication of the definition in both the Calling and Called Sprocs - so just one-more-place where you will have to keep everything in Sync.Having typed that I've gone off the idea! |
|
|
|
|
A better way of calling stored procs than EXEC?