how to pass values to IN keyword in sql query from front end [Resolved]

Hi Amritha,
The function in the above link is old & non-optimized (time-consumed) approach.....

For small number of IDs, You can simply use following method:

declare @id varchar(300) 

set @id='2,3' 

SELECT * FROM TestIn 

where ','+@id+',' LIKE '%,'+CAST(id AS VARCHAR)+',%'  ;

If there are large number of IDs, go ahead with Split() function...
Alternate of Split() function is as follows:

CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000))

RETURNS table

AS

RETURN (

WITH Pieces(n, start, stop) AS (

SELECT 1, 1, CHARINDEX(@sep, @s)

UNION ALL

SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)

FROM Pieces

WHERE stop > 0

)

SELECT n,

SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Val

FROM Pieces

)

GO

SELECT Val FROM dbo.CustomSplit(',', '1,2,3')

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

SQl will consider as a single value, you need to go some trick to get the output

CREATE FUNCTION idToTable
(
@idString VARCHAR(8000)
)
RETURNS @idTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @Temp VARCHAR(20)

WHILE LEN(@idString) > 0
BEGIN
SET @Temp = LEFT(@idString, ISNULL(NULLIF(CHARINDEX(',', @idString) - 1, -1),
LEN(@idString)))
SET @psCSString = SUBSTRING(@idString,ISNULL(NULLIF(CHARINDEX(',', @idString), 0),
LEN(@idString)) + 1, LEN(@idString))
INSERT INTO @idTemp VALUES (@Temp)
END

RETURN
END

CREATE TABLE TestIn (name VARCHAR(15), id int identity)
insert into TestIn(name) values('Ammu'),('unni'),('geethu'),('tintu'),('joby'),('jinu')
declare @id varchar(300)
set @id='2,3'
SELECT * FROM TestIn where id in (select * from idToTable(@id))

Regards,
Jayakumar Selvakani

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

i got solution check this link
http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

For small set of records, you can use the following method:

CREATE TABLE TestIn (name VARCHAR(15), id int identity) 

insert into TestIn(name) values('Ammu'),('unni'),('geethu'),('tintu'),('joby'),('jinu') 

declare @id varchar(300) 

set @id='2,3' 

SELECT * FROM TestIn 

where ','+@id+',' LIKE '%,'+CAST(id AS VARCHAR)+',%'  ;

 DROP TABLE TestIn 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Another way to do this using split function.

CREATE TABLE TestIn (name VARCHAR(15), id int identity)
insert into TestIn(name) values('Ammu'),('unni'),('geethu'),('tintu'),('joby'),('jinu')
declare @id varchar(300)
set @id='2,3'
SELECT * FROM TestIn
where id in (Select id from Split(@id, ','))

Happy coding

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Hi Mahesh,
There is no pre-defined Split function in SQL Server...
We only have to create user-defined function Split(CSValues, delimiter) and then
SELECT * FROM TestIn
where id in (Select id from Split(@id, ','))

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

can you mark it as answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

i got solution check this link
http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator