Dynamic SQL Question

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!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!