Passing a SQL parameter to an IN() clause using typed datasets in .NET

by

You can’t use a single parameter for a list of values in this way. But there may be database-specific ways to achieve what you want. For example, with SQL Server 2005 or later you could create a table-valued function to split your string parameter, something like:

CREATE FUNCTION dbo.F_Split
(
@InputString VARCHAR(MAX)
,@Separator VARCHAR(MAX)
)
RETURNS @ValueTable TABLE (Value VARCHAR(MAX))
AS
BEGIN

    DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX)
    SET @TotalLength=LEN(@InputString)
    SET @StartIndex = 1

    IF @Separator IS NULL RETURN

    WHILE @StartIndex <= @TotalLength
    BEGIN
        SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex)
        IF @SeparatorIndex > 0
        BEGIN
            SET @Value = SUBSTRING(@InputString, @StartIndex, @SeparatorIndex-@StartIndex)
            SET @StartIndex = @SeparatorIndex + 1
        END
        ELSE
        BEGIN
            Set @Value = SUBSTRING(@InputString, @StartIndex, @TotalLength-@StartIndex+1)
            SET @StartIndex = @TotalLength+1
        END
        INSERT INTO @ValueTable
        (Value)
        VALUES
        (@Value)
    END

    RETURN
END

You would then use it as follows:

SELECT * from Table WHERE ID IN (SELECT CAST(Value AS INT) FROM F_Split(@IDs, ','))

More Related Contents:

  1. Search SQL by date ASP.NET
  2. I want to insert a record in DB and then need to return a row
  3. What are good ways to prevent SQL injection? [duplicate]
  4. How can I add user-supplied input to an SQL statement?
  5. How to get last inserted id?
  6. What is the syntax for an inner join in LINQ to SQL?
  7. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  8. LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression
  9. C# SQL Server – Passing a list to a stored procedure
  10. linq case statement
  11. NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  12. How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
  13. What represents a double in sql server?
  14. SQL WHERE clause matching values with trailing spaces
  15. Equivalent of SQL ISNULL in LINQ?
  16. Distinct in Linq based on only one field of the table
  17. SqlBulkCopy from a List
  18. SQL Insert Query Using C#
  19. Executing query with parameters
  20. How to fill Dataset with multiple tables?
  21. SqlConnection.Close() inside using statement
  22. Getting the Last Insert ID with SQLite.NET in C#
  23. Join and Include in Entity Framework
  24. Add WHERE clauses to SQL dynamically / programmatically
  25. How to pass string array in SQL parameter to IN clause in SQL
  26. Check if a SQL table exists
  27. How to use SQL ‘LIKE’ with LINQ to Entities? [duplicate]
  28. How do I extract data from a DataTable?
  29. Use SQL Server time datatype in C#.NET application?
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment