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

Posted by Amritha444 under Sql Server on 8/21/2013 | Points: 10 | Views : 1077 | Status : [Member] | Replies : 8
Hi all

I want to pass values to IN keyword in sql query from front end .when i give values directly in query its getting. but pass values from front end its not getting. getting error message
Conversion failed when converting the varchar value '2,3' to data type int.
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 (@id)

DROP TABLE TestIn

how to do this


Thanks in Advance
Amritha




Responses

Posted by: Bandi on: 9/2/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Ssj_Kumar on: 8/21/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Amritha444 on: 8/21/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Allemahesh on: 8/21/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Amritha444 on: 9/2/2013 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response