Query Help

Question

Post reply

Query Help

vijayarani87.s

SSC Eights!

Points: 890
More actions
July 18, 2012 at 5:11 am

#264010

Hi I need to take the result by passing more ID, below is the sample query, I am getting error converting vachar to INT ERROR. Because data type for metric id is INT.
Is there any way to get the result
declare @v3 varchar(21) = '66,67,68'
select * from MasMetrics where MetricsId in ( cast(@v3 as int)) -- error

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Edited: Incorrect response removed.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

vijayarani87.s SSC Eights! Points: 890 More actions · Answer 2

Hi,

If i use ur Query I am getting the result like 666768. But I need to pass the value as 66,67,68

Lowell SSC Guru Points: 323518 More actions · Answer 3

vijayarani87.s (7/18/2012)
Hi I need to take the result by passing more ID, below is the sample query, I am getting error converting vachar to INT ERROR. Because data type for metric id is INT.
Is there any way to get the result
declare @v3 varchar(21) = '66,67,68'
select * from MasMetrics where MetricsId in ( cast(@v3 as int)) -- error

you need to use a split function here

@v3 is a string which happens to contain commas; instead you need to split the string into a table so your query can use the IN function correctly.

(search for DelimitedSplit8K on this site)

declare @v3 varchar(21) = '66,67,68'

SELECT *

from MasMetrics

where MetricsId in ( SELECT Item from dbo.DelimitedSplit8K (@v3) )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

venkatarao.swarna SSC Journeyman Points: 77 More actions · Answer 4

try the following solution

http://www.codeproject.com/Articles/6083/Passing-comma-delimited-parameter-to-stored-proced

vijayarani87.s SSC Eights! Points: 890 More actions · Answer 5

yes this is the correct solution, Do u have any function for that

vijayarani87.s SSC Eights! Points: 890 More actions · Answer 6

vijayarani87.s

SSC Eights!

Points: 890

July 18, 2012 at 5:41 am

#1514142

Thank u Swarna 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 7

the function attached to this article is recommended by myself any my peers:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

Lowell (7/18/2012)
the function attached to this article is recommended by myself any my peers:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

+1

Definitely. The other method uses a While loop which might give performance issues.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Kingston Dhasian (7/18/2012)
Lowell (7/18/2012)
the function attached to this article is recommended by myself any my peers:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
+1
Definitely. The other method uses a While loop which might give performance issues.

+1 again. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/