How to Pass a Variable That Contains a List to a Dynamic SQL Query

How do I pass a variable that contains a list to a dynamic SQL query?

Simply

EXECUTE ('select id from  [dbo].[CSVToTable] ('''+@listOfIDs+''')')
        declare @listOfIDs varchar(1000);

Or, which is the better way

SET @listOfIDs = '5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61'; 

EXECUTE sp_executesql N'select id from  [dbo].[CSVToTable] (@listOfIDs)',
                      N'@listOfIDs VARCHAR(1000)',
                      @listOfIDs;

• Why I get this error?
  

  Procedure or function dbo.CSVToTable has too many arguments specified.

Because you really pass too much parameters, more then needed, to understand this run this query and see what you are really pass to your function

SELECT 'select id from  [dbo].[CSVToTable] ('+@listOfIDs+')';

which will return (and this is what you really trying to execute)

select id from  [dbo].[CSVToTable] (5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61)

instead of (which is what you need)

SELECT 'select id from  [dbo].[CSVToTable] ('''+@listOfIDs+''')';

---

• Ok, but why sp_executesql is better than exec?

Simply, EXEC will forces you to concatenate all of your variables into one single string, that's the worst thing about it, and that makes your code fully open to SQL injection. See Bad Habits to Kick : Using EXEC() instead of sp_executesql, this doesn't mean that sp_executesql is 100% secure, but it allows for statements to be parameterized while EXEC() dosn't, therefore It’s more secure than EXEC in terms of SQL injection.

---

Finally, since you tag sql-server and you don't specify the version, I suggest that you use SPLIT_STRING() function (2016+) rathar than yours, and if you don't have 2016+ version, than create your own without using WHILE loop to gain more good performance, cause WHILE loop will perform slow, thus you should avoid it.

Examples:

• How to split a comma-separated value to columns

• Tally OH! An Improved SQL 8K “CSV Splitter” Function

• Reaping the benefits of the Window functions in T-SQL

SQL Server 2017 - How to pass a parameter in a SELECT inside a dynamic SQL

when you do:

set @sql = '
select ' + @Name + ' = colName from dbo.TestABC where colID = ''3''
'

the actual value inside variable @Name is null. And according to sql server logic string + null is null.

to do what you want you need:

DECLARE @sql nvarchar(max)
DECLARE @Name nvarchar(max)
set @sql = '
select @someVariable = colName from dbo.TestABC where colID = ''3''
'
EXECUTE sp_executesql @sql, N'@someVariable varchar(max) OUTPUT', @someVariable = @Name OUTPUT;
select @Name; -- this will print your colName

and do not use (max) as your default its a bad practice and might slow your queries down.

how to pass variables this in dynamic query in sql

I would use the sp_executesql command.

Some more documentation is here: http://msdn.microsoft.com/en-us/library/ms188001.aspx

Basically, you define a sql query, and parameter list, and then pass those in along with your actual parameters into that method.

So, something like this (real basic)

CREATE PROCEDURE dbo.yourProc
  @customerId INT
AS
DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT * FROM Customers WHERE CustomerId = @customerId'

DECLARE @params NVARCHAR(1000)
SET @params = '@customerId INT'

EXEC dbo.sp_executesql @sql, @params, @customerId

Passing values to dynamic query in SQL Server procedure

If you are wanting a variable to be part of the text such as:

'select 1 from values v@pk+1 where v1.e_id = @pk+1.e_id'

Then you need to escape the string and add the variable -- but beware this is prone to SQL Injection attacks if you don't strictly control the source of the variable data!

Example:

'select 1 from values v'+convert(varchar(15),@pk+1)+' where v1.e_id = '+convert(varchar(15),@pk+1)+'.e_id'

If you simply want to pass the variable into the string such as:

'and v5.f_id=@fid and(value=@val))'

Then you leave those variables unescaped, and pass them into sp_executesql

Example:

exec sp_executesql @SQL, N'@fid int, @val varchar(max)', @fid=@fid, @val=@val

More info on sp_executesql and its parameters:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

Using variables within a dynamic sql script

You can pass through variables to dynamic SQL via sp_executesql

• Note that you should always use QUOTENAME to escape object names
• Also, dynamic SQL variables should always be nvarchar
• You also should not use variable coalescing to aggregate, instead use STRING_AGG or FOR XML

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))

            
SET @sAlterTableDynamicSQL =
'
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N'','')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N''tempdb..' + QUOTENAME(@sNomTableTemporaire, '''') + ''');
';

PRINT (@sAlterTableDynamicSQL);

EXEC sp_executesql
  @sAlterTableDynamicSQL,
  N'@sColumns nvarchar(max) OUTPUT'
  @sColumns = @sColumns OUTPUT;

But you don't actually need dynamic SQL here at all. You can pass the table name straight to object_id()

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))
            
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N',')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N'tempdb..' + QUOTENAME(@sNomTableTemporaire));

For SQL Server 2016 and earlier, you can use the FOR XML PATH('') method

Passing a variable into an IN clause within a SQL function?

Passing a string directly into the IN clause is not possible. However, if you are providing the list as a string to a stored procedure, for example, you can use the following dirty method.

First, create this function:

CREATE FUNCTION [dbo].[fnNTextToIntTable] (@Data NTEXT)
RETURNS 
    @IntTable TABLE ([Value] INT NULL)
AS
BEGIN
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)

    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1

    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)

        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(@vv, '') + @v
        END

        SET @Ptr = @Ptr + 1
    END

    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))

    RETURN
END

(Note: this is not my original code, but thanks to versioning systems here at my place of work, I have lost the header comment linking to the source.)

Then use it like so:

SELECT  *
FROM    tblMyTable
        INNER JOIN fnNTextToIntTable(@MyList) AS List ON tblMyTable.ID = List.Value

Or, as in your question:

SELECT  *
FROM    tblMyTable
WHERE   ID IN ( SELECT Value FROM fnNTextToIntTable(@MyList) )

How to pass an output variable in dynamic sql query

may be this will help you

http://msdn.microsoft.com/en-us/library/ms188001.aspx

procedure sp_executesql have parameters @stmt, which is actual statement to run, @params - declaration of parameters, and then all parameters declared in @params

It's also better to pass parameters by names

declare @QuestionInclude varchar(10), @stmt nvarchar(max), @params nvarchar(max)

select @stmt = 'select @QuestionInclude = 1 from ##Stg_Prelim'
select @params = '@QuestionInclude varchar(10) output'

exec sp_executesql
    @stmt = @stmt,
    @params = @params,
    @QuestionInclude = @QuestionInclude output

How to insert Dynamic SQL in list and labels?

This is something that's actively being worked on. This blog post covers the basics for parametrized data sources within List & Label itself. Thorough support within the Report Server, including support for stored procedure parameters, is on the roadmap and will be released this fall.

---

Related Topics