October 5, 2010 at 3:46 am
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?
October 5, 2010 at 4:18 am
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.
October 5, 2010 at 4:35 am
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
October 5, 2010 at 7:06 am
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.
October 5, 2010 at 11:10 am
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 🙂
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply