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]

mEmENT0m0RI (an*******@devdex.com) writes:

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'.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mEmENT0m0RI]

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!