How to set variable/output parameter in Dynamic SQL

To execute Dynamic SQL in
SQL Server you can use the
sp_executesql system sp or
EXEC() operator.







When you use the sp_executesql you can pass input and output
parameters. The EXEC() operator does not allow this.
By specifying the "OUTPUT" word in parameters definition
you can get the value back from the dynamic string execution.


Here are examples of setting a variable(s) from Dynamic SQL:
DECLARE @ID INT,@Str VARCHAR(10)
EXEC sp_executesql N'SELECT @ID=10,@Str=''Test''', N'@ID INT OUTPUT,@Str VARCHAR(10) OUTPUT',@ID OUTPUT,@Str OUTPUT
SELECT @ID,@Str

DECLARE @SQLString NVARCHAR(200),@ParmDefinition NVARCHAR(100)
DECLARE @IntVariable INT,@Lastlname VARCHAR(30)
SET @IntVariable=35
SET @SQLString = N'SELECT @LastlnameOUT = max(lname) FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,@LastlnameOUT varchar(30) OUTPUT'
EXECUTE sp_executesql @SQLString,@ParmDefinition,@level=@IntVariable,@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname
Here is an example of getting output parameter from a SP:

CREATE PROCEDURE dbo.usp_TestProc
(
@SomeValueToReturnFromSP VARCHAR(10) OUTPUT
)ASSELECT @SomeValueToReturnFromSP='TestString'GO

-----------------------------------------------------------------
DECLARE @OutputValueFromSP VARCHAR(10)
EXEC sp_executesql N'EXEC dbo.usp_TestProc @SomeValueToReturnFromSP=@OutputValueFromSP OUTPUT',N'@OutputValueFromSP VARCHAR(10) OUTPUT',@OutputValueFromSP OUTPUT
SELECT @OutputValueFromSP

GO

For more information:

http://support.microsoft.com/kb/262499

NULL value in Dynamic SQL becames Empty String

Recently,I encountered a strange (for me) situation.
Consider that you need to use some string parameter
in Dynamic SQL query using EXEC(@String) statement.
And the string parameter is NULL (by mistake).

USE AdventureWorks
GO
DECLARE @FirsName NVARCHAR(50)

EXEC(' SELECT ContactID
FROM Person.Contact
WHERE (FirstName = '''+@FirsName+''')')

What will happend is that the value of the @FirsName
will be empty string in execution runtime.
In SQL Profiler we will see the following executed:

SELECT ContactID
FROM Person.Contact
WHERE (FirstName = '')

Do you know why?
Anybody?

I got the answer from Ami Levin(Israel MVP):
This behavior explained by the settings of
"CONCAT_NULL_YIELDS_NULL" option.
http://msdn.microsoft.com/en-us/library/ms176056.aspx