Monday, May 3, 2010

Using sp_executesql for a Script Based “Function”

On the SQL Team forums a user was requesting a way to have a function run from inside a script, because he was unable to create a UDF as he lacked the required permissions to do so. My suggestion was to use sp_executesql. The syntax is a little hard to understand. Here is an example.
DECLARE @test AS nvarchar(500)
DECLARE @paramDef nvarchar(500)

SET @paramDef = N'@myParam INT'
SET @myVar = 10248

SET @test = N'USE Northwind; SELECT * from dbo.Orders where dbo.Orders.OrderID = @myParam'

EXEC sp_executesql @test, @paramDef, @myParam = @myVar;

The syntax is explained below.
EXEC sp_executesql @variable,
                   --This represents the SQL code to be executed.

                   --This describes the parameter. Is it a varchar,

                   -- int, or some other type.
                   --This holds the value you wish to pass to the

                   -- parameter.