Monday, 16 January 2017

Comma separated value parameter in SQL IN clauses

How to use comma separated values parameter in SQL IN Clause in SQL Queries-

The SQL IN clause is very useful, since it allows you to specify exactly which values you want to return.
For this tip,  let's assume we have a database with this table:

CREATE TABLE [dbo].[Employee](
 [ID] [int] NOT NULL,
 [Name] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY 

And the Data is:

ID Name
1 Suraj
2 Aden
3 Richerd
4 Shekhar
5 Shankar

We can get the rows from table for whatever Id's we need


SELECT [ID] ,[Name] FROM [dbo].[Employee] WHERE Id IN (1, 3)

And we get the expected data

ID Name
1 Suraj
3 Richerd

But what, if we pass the parameter in string variable

DECLARE @ids VARCHAR(10)
SET @ids = '1,3'
SELECT [ID] ,[Name] FROM [dbo].[Employee] WHERE Id IN (@ids)

You will get the below Error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '1,3' to data type int.

So, What is the Solution

What we need to do is, convert the comma separated values into a table first. My initial version was inline, and rather messy, so I re-worked it to a user defined function and made it a bit more general purpose. 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTable] (@ids VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
     -- Ensure input ends with comma
 SET @ids = REPLACE(@ids + ',', ',,', ',')
 DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @ids ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@ids)
   SELECT  @VALUE = LEFT(@ids , @SP - 1)
   SELECT  @ids = STUFF(@ids, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
 RETURN
END
GO

This creates a user defined function that takes a comma separated value string and converts it into a table that SQL does understand - just pass it the sting, and it works it all out.

How to Use the above UDF
Example:


DECLARE @idList VARCHAR(10)
SET @idList = '1,3'
SELECT [ID] ,[Name] FROM [dbo].[Employee] WHERE Id IN (SELECT * FROM dbo.CSVToTable(@idList))

Please Share if you liked this Post.

References: Codeproject

0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)