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!mEmENT0m0RI (anonymous@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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!
