Dynamic SQL Question

[mEmENT0m0RI]

Spent couple of hours trying to figure out this issue. How can I pass a
parameter in dynamic SQL?
Here is a simplified example:


CREATE PROCEDURE dbo.spTest
@LastName varchar(25)
AS

exec ('SELECT *
FROM SubmissionLog
WHERE LastName LIKE @LastName')


exec spTest 'Doe'


Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@LastName'.


Thank's for your help,
Igor


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Dynamic SQL Question

mEmENT0m0RI (anonymous@devd ex.com) writes:[color=blue]
> Spent couple of hours trying to figure out this issue. How can I pass a
> parameter in dynamic SQL?
> Here is a simplified example:
>
>
> CREATE PROCEDURE dbo.spTest
> @LastName varchar(25)
> AS
>
> exec ('SELECT *
> FROM SubmissionLog
> WHERE LastName LIKE @LastName')
>
>
> exec spTest 'Doe'
>
>
> Server: Msg 137, Level 15, State 2, Line 3
> Must declare the variable '@LastName'.[/color]

The dynamic SQL constitutes a scope of its own, so you cannot refer
to parameters declared in the surrounding procedure.

This case is best handled with sp_executesql, look at
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[mEmENT0m0RI]

Re: Dynamic SQL Question

Hey, thanks for answering.
I alreasy coded it without dynamic SQL, but will deffinately try
sp_executesql tomorrow.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!