Parameter in select statement

Question

Post reply

Parameter in select statement

Viewing 5 posts - 16 through 19 (of 19 total)

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

Håvard Mr or Mrs. 500 Points: 569 More actions · Answer 1

Yes, I am using SQL Server 2008. I haven't used TVP before, thanks for the tip!

I don't know what version he who started this thread is using.

I searched a little bit and found some interesting articles on the topic.

But... If the input to the query/stored procedure is a string in the format "1,2,3", how should I then use a TVP?

It seems as if one still has to loop through the string to extract the integers and load then into a table variable, or am I mistaken?

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 2

Im assuming that the sp is being called by a .net application which a developer has some form of control over.

In which case the app shouldnt concatenate the ids together but use a TVP.

If the data is already existing within sqlServer as a concatenated string then it would be best to continue as you have done.

Clear Sky SQL
My Blog[/url]

faheemahmad14 SSC Veteran Points: 246 More actions · Answer 3

If your countryId's are dynamic you can pass them to stored procedure in a comma seperated string manner and then can use any function like fnsplit to transform those CSV values to a datatable.

which can be used simply in query like

DECLARE @CityId NVARCHAR(50)

Set @CityId ='1,3,4,5'

SELECT VendorId From Vendors

WHERE CountryId = @CountryId AND CityId IN( Select Id from fnSplit(@CityId ))

ORDER BY CreatedDate

David Burrows SSC Guru Points: 65111 More actions · Answer 4

Another alternative (although will cause a scan)

SELECT VendorId

FROM Vendors

WHERE CountryId = @CountryId

AND CHARINDEX(','+CAST(CityId as nvarchar(10))+',',','+@CityId+',') > 0

ORDER BY CreatedDate

Far away is close at hand in the images of elsewhere.
Anon.

kuna.sharma SSC Veteran Points: 201 More actions · Answer 5

Hi All,

Thanks for your valuable inputs. I'm working on this and will update here as soon get any result.

This forum i rocking 🙂