How to use dynamic SQL string?

Question

Instead of this:

-- Method call
SELECT @FunctionResult = DWH_Staging.adm.SalesPlanExists(@Year, @Month, @CountryID, @Type)

I want to use something like this:

DECLARE
    @TestedDatabase = 'DWH_Staging',
    @TestedSchema   = 'adm',
    @TestedObject   = 'SalesPlanExists' 

DECLARE @sql NVARHCAR(4000) = '@TestedDatabase+'.'+@TestedSchema+'.'+@TestedObject (@Year, @Month, @CountryID, @Type)'

-- Method call
SELECT @FunctionResult = @sql

Hints would be appreciated.

What do you want to achieve exactly and what is not working? — Onkel Toob
– Onkel Toob, Commented Feb 2, 2018 at 13:25
Use an OUTPUT variable when you call sp_executesql stackoverflow.com/questions/803211/… — Zack
– Zack, Commented Feb 2, 2018 at 13:28

Mithgroth · Accepted Answer · 2018-02-02 13:31:02Z

2

What you can do is to set a template for the query, and replace the string while execution:

DECLARE @sql NVARHCAR(4000) = '{DATABASENAME}.{SCHEMANAME}.{OBJECTNAME} (' + @Year + ', ' + @Month + ', ' + @CountryID + ', ' + @Type + ')'
SET @SQL_SCRIPT = REPLACE(@sql, '{DATABASENAME}', @DBNAME)

and then, execute it:

EXECUTE (@SQL_SCRIPT)

answered Feb 2, 2018 at 13:31

Mithgroth

1,2122 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Guzman · Accepted Answer · 2018-02-02 13:38:12Z

2

Below is a parameterized SQL example, guessing at your data types:

DECLARE
     @TestedDatabase sysname     = 'DWH_Staging'
    ,@TestedSchema sysname     = 'adm'
    ,@TestedObject sysname     = 'SalesPlanExists' 
    ,@FunctionResult int
    ,@Year int
    ,@Month int
    ,@CountryID int
    ,@Type int;

DECLARE @sql nvarchar(MAX) = N'SELECT @FunctionResult = '
    +QUOTENAME(@TestedDatabase)
    +N'.'
    +QUOTENAME(@TestedSchema)
    +N'.'
    +QUOTENAME(@TestedObject)
    + N'(@Year, @Month, @CountryID, @Type)';
SELECT @sql

-- Method call
EXECUTE sp_executesql
    @sql
    ,N'@FunctionResult int OUTPUT
        ,@Year int
        ,@Month int
        ,@CountryID int
        ,@Type int'
    ,@FunctionResult = @FunctionResult OUTPUT
    ,@Year = @Year
    ,@Month = @Month
    ,@CountryID = @CountryID
    ,@Type = @Type;

answered Feb 2, 2018 at 13:38

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

1 Comment

Ruud Helderman Over a year ago

Upvoted for using QUOTENAME; something neglected way too often.

Collectives™ on Stack Overflow

How to use dynamic SQL string?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related