Converting data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting data type

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-10-16 : 02:36:53

Hi,
Consider the below table with 5 records.Need to filter the result whose id is 104 and 105 which will be passed as parameters of stored procedure.But it is throwing error
'Conversion failed when converting the varchar value '104,105' to data type int'.
Please advice.
===================================

Declare @test table
(
CID int,
CityName varchar(50)
)
declare @filter varchar(10) = '104,105'

Insert into @test
select 101,'Delhi' union all
select 102,'Bombay' union all
select 103,'Jaipur' union all
select 104,'Kochi' union all
select 105,'Chennai'

select * from @test where CID IN (@filter)

=============================================

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-10-16 : 02:51:35

Hi

CREATE TABLE #Test
(
CID int,
CityName varchar(50)
)
declare @filter varchar(10)
SET @filter = '104,105'

Insert into #Test
select 101,'Delhi' union all
select 102,'Bombay' union all
select 103,'Jaipur' union all
select 104,'Kochi' union all
select 105,'Chennai'

EXEC (N'select * from #Test where CID IN (' + @filter + ')')
DROP TABLE #Test

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-16 : 02:56:52

Also see here:
http://www.sqlteam.com/search.aspx?cx=011171816663894899992%3Aaow51lf_dim&cof=FORID%3A9&q=multiple+value+parameter

Too old to Rock'n'Roll too young to die.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-10-16 : 03:06:58

Hi,
This is Another Way.

CREATE FUNCTION [dbo].[f_Split]
(
@Keyword VARCHAR(8000),
@Delimiter VARCHAR(255)
)
RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000))
AS
BEGIN
DECLARE @Word VARCHAR(255)
DECLARE @TempKeyword TABLE (Keyword VARCHAR(8000))

WHILE (CHARINDEX(@Delimiter, @Keyword, 1)>0)
BEGIN
SET @Word = SUBSTRING(@Keyword, 1 , CHARINDEX(@Delimiter, @Keyword, 1) - 1)
SET @Keyword = SUBSTRING(@Keyword, CHARINDEX(@Delimiter, @Keyword, 1) + 1, LEN(@Keyword))
INSERT INTO @TempKeyword VALUES(@Word)
END

INSERT INTO @TempKeyword VALUES(@Keyword)

INSERT @SplitKeyword
SELECT * FROM @TempKeyword
RETURN
END

Declare @test table
(
CID int,
CityName varchar(50)
)
declare @filter varchar(10) = '104,105'

Insert into @test
select 101,'Delhi' union all
select 102,'Bombay' union all
select 103,'Jaipur' union all
select 104,'Kochi' union all
select 105,'Chennai'

--SELECT * FROM dbo.f_Split(@filter,',')

select CID,CityName from @test INNER JOIN dbo.f_Split(@filter,',') AS SP ON CID = SP.Keyword

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 09:20:09

another way without using udf


select CID,CityName from @test where ',' + @filter + ',' LIKE '%,' + CAST(CID AS Varchar(10)) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-10-17 : 05:46:29

It worked! Could u plz explain this?

quote:
Originally posted by visakh16

another way without using udf

select CID,CityName from @test where ',' + @filter + ',' LIKE '%,' + CAST(CID AS Varchar(10)) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 23:02:14

it works based on string pattern

',' + @filter + ',' makes parameter values as ,104,105 etc

this is then compared against each column value prefixed and suffixed by , to get ,104, ,105 etc

so any of values involved in long string will match the pattern and will be returned

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources