Assigning a value to variable in Dynamic SQL

See more:

SQL-Server-2005

SQL-Server

Hi all,

How do we assign a value to scalar variable based on some condition in Dynamic SQL .After assigning i have to return
the value.

Example.

CREATEPROCEDURE PROC_LOCALVAR --'1'
(@V aS varchar(1))
AS
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @LOCAL AS VARCHAR(20)

SET @SQL='IF '+@V+'=1
         BEGIN
                      SET @LOCAL=''DIL''
         END
         ELSE
         BEGIN
                        SET @LOCAL=''DILN''
         END  '

PRINT(@SQL)
EXEC(@SQL)
SELECT @LOCAL

In the above example how do we compare variable 'V' and return the value.

Error:

Must declare the scalar variable "@LOCAL".