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 @myVar AS INT 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.                    @parameterDifinition,                    --This describes the parameter. Is it a varchar,                    -- int, or some other type.                    @valuePassed                    --This holds the value you wish to pass to the                    -- parameter.