How to Specify in Clause in a Dynamic Query Using a Variable

How do you specify IN clause in a dynamic query using a variable?

Like in @Sathya link, you can bind the varray (I took @Codo example):

CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
/
DECLARE
  l_str_tab str_tab_type;
  l_count NUMBER;
  v_sql varchar2(3000);
BEGIN
  l_str_tab := str_tab_type();
  l_str_tab.extend(2);
  l_str_tab(1) := 'TABLE';
  l_str_tab(2) := 'INDEX';

  v_sql := 'SELECT COUNT(*) FROM all_objects WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(:v_list))';

  execute immediate v_sql into l_count using l_str_tab;

  dbms_output.put_line(l_count);
END;
/

UPDATE: the first command can be replaced with:

CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
    /

then call:

l_str_tab.extend(1);

when ever you add a value

SQL Server - In clause with a declared variable

You need to execute this as a dynamic sp like

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)

Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('+@ExcludedList+')'

exec sp_executesql @sql

Dynamic query 'in clause' parameter not working

Try this:

    alter PROCEDURE [dbo].[Test_In_Clause]

    -- Add the parameters for the stored procedure here
    @name nvarchar(50) = NULL,
    @class nvarchar(50) = NULL

AS
BEGIN

    declare 
    @sql nvarchar(max),
    @ParameterDef NVARCHAR(500)

    set @ParameterDef = N'@name nvarchar(50),
                        @class nvarchar(50)'

    set @sql  = 'Select * from aaa_Students where Name = @name and Class in (@class)'
    print @sql
    execute sp_Executesql @sql, N'@name nvarchar(50),@class nvarchar(50)', @name = @name, @class = @class

Query with dynamic variables in clause WHERE

You can change your query to:

SELECT name FROM users WHERE name = ? AND surname like ?

and when you want to search by name then the arguments of test($name,$surname) will be --> test('yourname','%');

here note that % is wild card and surname in query is using "like" so at the end the query will be:

SELECT name FROM users WHERE name = 'yourname' AND surname like '%'.
What it will search is data will 'yourname' as name and any value for surname.

Now, if you want to search by name and surname then you can call the function as
--> test('yourname','yoursurname')
so the query will become:

SELECT name FROM users WHERE name = 'yourname' AND surname like 'yoursurname'.
What it will search is data will 'yourname' as name and 'yoursurname' as surname. Thus just using a single query you can solve both purpose

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 getting value of dynamic query into a variable

You need to assign your @Output variable to your result count.

@Output = COUNT(ServerName)

complete script

DECLARE @Output INT 

SELECT @SqlCommand = 'SELECT @Output = COUNT(ServerName) FROM ' + @TableReference + ' WITH (NOLOCK) WHERE ServerName = ''' + @PackageEndPoint + ''''

EXEC sp_executesql @SqlCommand, N'@Output INT OUTPUT',@Output = @Output OUTPUT
SELECT  @StagingRecordCount = @Output
SELECT @StagingRecordCount

Dynamic Where clause in sql with IN statement in it

Increase your @sql variable's length to MAX like

DECLARE @sql varchar(MAX)

You have defined 20 length which is not enough to store whole query in that variable. Also remove @clause variable and change your code like below. You can also print whole query by print @sql and check what is wrong in that.

DECLARE @var varchar(500)
DECLARE @sql varchar(MAX)

SET @var= '1,2,3'

SET @sql = 'SELECT  [ID], 
                    [SOURCE], 
                    [LAST_KEY] 
            FROM    [oms].[dbo].[MIGRATION]' 

IF @var IS NOT NULL AND @var <> ''
BEGIN
    SET @sql = @sql + ' WHERE ID IN ('+ @var + ')'
END

EXEC (@sql)

How to use variable in where clause in dynamic sql

Use sp_executesql. That way, you can pass variables in and out of a statement. In your case, the syntax is something like this:

declare @UI varchar(50);
declare @ClientId varchar(50);
set @UI = 'IDNumber';
declare @sql nvarchar(max);

SET @sql = N' select top 1 @ClientId = clientid
           from Clients
           where '+ QUOTENAME(@UI) + N' = ''6001016119085'' or ' 
           + QUOTENAME(@UI) + N' = ''None'' and cancelled <> 1';

exec sp_executesql @sql
                 , N'@ClientId varchar(50) OUTPUT'
                 , @ClientId = @ClientId OUTPUT

set @ClientExist = coalesce(@ClientId, 0); 

The documentation for sp_executesql is here.

---

Related Topics