Using EXEC with dynamic SQL

Question

Using EXEC with dynamic SQL

Jamie Thomson

SSChampion

Points: 11805
More actions
January 28, 2005 at 4:29 am

#65516
Hello,
Need a bit of help with the following scenario...
I want to execute an SP called sp_MySP.
sp_MySP takes 2 required parameters, @p1 & @p2
This is a generic bit of code therefore I want to build the name of the SP that I am calling dynamically and execute it using EXEC. But...how do I pass in parameters when I build the execute string dynamically?
This is what I have at the moment:
```
SET @vSPName = 'My'
SET @vSQL = 'sp_' + @vSPName + 'SP'
EXEC @vReturnStatus = @vSQL, @p1 = @some_value, @p2 = @another_value
```
This doesn't compile but hopefully you can see what I'm trying to do here. i.e. Dynamically build an execution string for EXEC that calls an SP with some parameter values. Anyone know how to do this?
Do I have to use sp_executesql?
Thanks in advance.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

Hi,

I *think* I've worked this out. Removing the first comma from the EXEC statement seems to have done the trick. I'm still not absolutely sure of what behaviour this exhibits because the 2nd parameter is actually an output param and I'm hoping that what I'm passing in is a variable rather the current value of that variable.

Its doing what its supposed to though so all seems good.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

David Burrows SSC Guru Points: 65111 More actions · Answer 2

Removing the first comma from the EXEC statement seems to have done the trick.

Correct.

I'm still not absolutely sure of what behaviour this exhibits because the 2nd parameter is actually an output param and I'm hoping that what I'm passing in is a variable rather the current value of that variable.

You will pass the value of @another_value to the procedure, if it is truly an output parameter (the proc sets it to a value) then must use OUTPUT to get the value back.

EXEC @vReturnStatus = @vsql @p1 = @some_value, @p2 = @another_value OUTPUT

Far away is close at hand in the images of elsewhere.
Anon.

Jamie Thomson SSChampion Points: 11805 More actions · Answer 3

Well spotted. Thanks David.

This is working fine which is great news.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson