SQL - Stored Procedure with Select Statement Using in (@Variable_Commadelimitedlistofids)

SQL - Stored Procedure with Select Statement using IN (@Variable_CommaDelimitedListOfIDS)

For those cases I use this table function, which you can adapt to your needs:

CREATE FUNCTION dbo.f_params_to_list (@par VARCHAR(500))
returns @result TABLE (value VARCHAR(30))
AS  
begin
     DECLARE @TempList table
          (
          value VARCHAR(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @par = LTRIM(RTRIM(@par))+ ','
     SET @Pos = CHARINDEX(',', @par, 1)

     IF REPLACE(@par, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@par, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @par = RIGHT(@par, LEN(@par) - @Pos)
               SET @Pos = CHARINDEX(',', @par, 1)

          END
     END    
     INSERT @result
     SELECT value 
        FROM @TempList
     RETURN
END    

In your stored procedure you would use it like this:

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(SELECT value FROM dbo.f_params_to_list(@Ids))

T-SQL stored procedure that accepts multiple Id values

Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server.

There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons.

• Table-Valued Parameters. SQL Server 2008 and higher only, and probably the closest to a universal "best" approach.
• The Iterative Method. Pass a delimited string and loop through it.
• Using the CLR. SQL Server 2005 and higher from .NET languages only.
• XML. Very good for inserting many rows; may be overkill for SELECTs.
• Table of Numbers. Higher performance/complexity than simple iterative method.
• Fixed-length Elements. Fixed length improves speed over the delimited string
• Function of Numbers. Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table.
• Recursive Common Table Expression (CTE). SQL Server 2005 and higher, still not too complex and higher performance than iterative method.
• Dynamic SQL. Can be slow and has security implications.
• Passing the List as Many Parameters. Tedious and error prone, but simple.
• Really Slow Methods. Methods that uses charindex, patindex or LIKE.

I really can't recommend enough to read the article to learn about the tradeoffs among all these options.

How to optionally apply a WHERE IN clause based on a variable?

Why not use JOIN instead of a subquery in the WHERE clause.

set @userIds = nullif(ltrim(@userIds),'')
select u.*
from Users u
left join string_split(@userIds,',') s on u.Id=s.value
where s.value is not null or @userIds is null

passing a list of columns into a stored procedure

Do you mean that suppose you have a table as follows

CREATE TABLE bits
(
id INT PRIMARY KEY,
col1 BIT,
col2 BIT,
col3 BIT,
col4 BIT
)

Populated as follows

INSERT INTO bits 
VALUES (1,0,0,0,0), 
       (2,1,1,1,1),
       (3,1,1,0,0)

And you pass in the string

DECLARE @ColumnsList VARCHAR(MAX) = 'col1,col2,col3'

You want to get back row 2 because that's the only one where the value for all these columns is 1?

If so probably dynamic SQL or some sort of bitwise query will be the most sensible solution. In the meantime here's a solution without either.

SELECT id
FROM   bits UNPIVOT(val FOR col IN (col1, col2, col3, col4)) unpvt
       JOIN (SELECT col
             FROM   (SELECT CAST('<c>' + REPLACE(@ColumnsList, ',', '</c><c>') + '</c>' AS XML) AS x) x
                    CROSS APPLY (SELECT t.split.value('.', 'sysname') AS col
                                 FROM   x.nodes('/c') t(split)) ca) cols
         ON cols.col = unpvt.col
GROUP  BY id
HAVING COUNT(CASE
               WHEN val = 0 THEN 1
             END) = 0  

Boundless Stored Proceedure Input

varchar(MAX) does not cap at 8000 characters. It's a replacement for the text data type (which is now deprecated). The MAX is not just a shortcut for a constant value of 8000; it actually indicates that the variable should be able to store an arbitrarily large amount of data.

If you're declaring a varchar variable without MAX as the qualifier, though, it will cap at 8000.

How to loop through more than one values of select statement and perform some operations using those values in stored procedure?

Can you use a WHILE loop? Along the lines of

WHILE (SELECT COUNT(*) FROM SalaryHead WHERE Total IS NULL AND EmployeeID = @EmpID) > 0
BEGIN

  /*Calculate Salary Head */

END

---

Related Topics