How to pass string parameter with `IN` operator in stored procedure SQL Server 2008

by

Here’s how I solved it: Working SQL Fiddle

First I have create a function which splits the string value i.e. '1,2,4,5'

Split function:

CREATE  FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Later in my query I use that split function

declare @startdate1 varchar(100) ='20120201'
declare @enddate1 varchar(100)='20130601'
declare @dptId varchar(100)='3,4'

select * from dummy
where DateJoining >=@startdate1 and DateJoining < @enddate1 
  and departmentID IN (SELECT Value FROM fn_Split(@dptId, ','));

More Related Contents:

  1. MongoDB Stored Procedure Equivalent
  2. How to pass an array into a SQL Server stored procedure
  3. Call a stored procedure with parameter in c#
  4. Calling stored procedure with return value
  5. Escape a string in SQL Server so that it is safe to use in LIKE expression
  6. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  7. Fetch Oracle table type from stored procedure using JDBC
  8. Is there a way to call a stored procedure with Dapper?
  9. Dapper.NET and stored proc with multiple result sets
  10. MySQL create stored procedure syntax with delimiter
  11. Adding stored procedures complex types in Entity Framework
  12. T-SQL Dynamic SQL and Temp Tables
  13. CASE WHEN statement for ORDER BY clause
  14. Can a stored procedure/function return a table?
  15. Varchar variable is not working in WHERE clause
  16. Handling date in SQL Server
  17. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  18. How can I use transactions in my MySQL stored procedure?
  19. Stored Procedure and Permissions – Is EXECUTE enough?
  20. Creating stored procedure and SQLite?
  21. What is passing parameters to SQL and why do I need it?
  22. Stored procedure – return identity as output parameter or scalar
  23. Using IF ELSE statement based on Count to execute different Insert statements
  24. Inserting into Oracle and retrieving the generated sequence ID
  25. How to use Table output from stored MYSQL Procedure
  26. Optional multi-valued parameters in SSRS
  27. Converting String List into Int List in SQL
  28. Stored procedure hangs seemingly without explanation
  29. Creating MYSQL Procedure in Laravel 4 Migrations
  30. is it possible to call a sql script from a stored procedure in another sql script?

Leave a Comment