Converting String List into Int List in SQL

by

It is possible to send an int list to your stored procedure using XML parameters. This way you don’t have to tackle this problem anymore and it is a better and more clean solution.

have a look at this question:
Passing an array of parameters to a stored procedure

or check this code project:
http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

However if you insist on doing it your way you could use this function:

CREATE FUNCTION [dbo].[fnStringList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item int
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

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

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN
END

Call it like this:

SELECT      *
FROM        Table
WHERE status IN (SELECT * from fnStringList2Table(@statuslist))

More Related Contents:

  1. Paging with Oracle
  2. Can I pass column name as input parameter in SQL stored Procedure
  3. SQL Server Insert if not exists
  4. Strings as Primary Keys in SQL Database [closed]
  5. Pass multiple values in single parameter
  6. Stored procedure or function expects parameter which is not supplied
  7. How to capitalize the first letter of each word in a string in SQL Server
  8. How do I execute a stored procedure once for each row returned by query?
  9. Dynamic Sorting within SQL Stored Procedures
  10. What is the difference between function and procedure in PL/SQL?
  11. Call a stored procedure with another in Oracle
  12. SQL: Select dynamic column name based on variable
  13. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  14. Which is better: Ad hoc queries or stored procedures? [closed]
  15. Double colon `::` notation in SQL
  16. Why execute stored procedures is faster than SQL query from a script?
  17. Solution to “cannot perform a DML operation inside a query”?
  18. Custom Date/Time formatting in SQL Server
  19. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  20. In SQL Server, what does “SET ANSI_NULLS ON” mean?
  21. Inserting rows into a table with one IDENTITY column only [duplicate]
  22. How to get Return Value of a Stored Procedure
  23. Functions with variable number of input parameters
  24. UPDATE and REPLACE part of a string
  25. Eliminate and reduce overlapping date ranges
  26. Functions vs Stored Procedures
  27. How to execute a stored procedure inside a select query
  28. Check if a parameter is null or empty in a stored procedure
  29. converting int to real in sqlite
  30. Call stored procedure with table-valued parameter from java

Leave a Comment