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 |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2012-02-18 : 10:46:48
|
| I have been trying to figure out how to run a stored proc using sp_executesql but all my searching just shows how to use it for normal SQL queries, is it not meant to be used for procs? If it is how can I call a proc and pass it some input variables and get the output variable?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-18 : 12:47:59
|
| No, it's not intended for procedures, it's intended for dynamic SQL. Why do you want to use sp_executesql for procedures?Calling a procedure with input and output parameters:EXEC ProcedureName @InputParameter, @OutPutParameter OUTPUT--Gail ShawSQL Server MVP |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-18 : 15:26:33
|
quote:
Originally posted by Eagle_f90
I have been trying to figure out how to run a stored proc using sp_executesql but all my searching just shows how to use it for normal SQL queries, is it not meant to be used for procs? If it is how can I call a proc and pass it some input variables and get the output variable?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia
didnt understand why you need sp_executesql for executing a procedureIs there anything that makes procedure call dynamic?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-02-18 : 15:36:04
|
| I was already using EXEC but when I ran accross sp_executesql in an artical about improving performance of SQL I thought it mentioned using it for procs also. After thinking about it last night and doing some searching it seemed that using it for procs was a bit odd.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-18 : 15:37:17
|
quote:
Originally posted by Eagle_f90
I was already using EXEC but when I ran accross sp_executesql in an artical about improving performance of SQL I thought it mentioned using it for procs also. After thinking about it last night and doing some searching it seemed that using it for procs was a bit odd.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia
that was for dynamic sql------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-18 : 16:09:01
|
| It can improve dynamic SQL because it allows parameterisation of queries (which procedures intrinsically are) and hence better plan reuse (as stored procedures already have)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
How to use sp_executesql to execut stored procs