March 30, 2009 at 10:18 am
I would like to hear about your opinions about the best way (or the least worst) you believe it would be to fetch data from the server. (From C# to SQL)
I need to implement a SP that will return data based on a set of keys (an array in C#). As simple as that. I have 3 solutions to propose but one is bothering me about a potential security issue. I'll list the 3 I have in mind and you could comment on them or giving maybe a new solution:
1- Returning all the data and filtering in the app for the wanted keys (return more rows than needed)
2- Make a call for each keys in the array (huge overhead each time a connection is made, a lot of keys might be queried so I would like to avoid doing a call to the DB for each individual key)
3- Make a call having the array of keys stored inside a NVARCHAR(MAX) parameter and implement the SP dynamically with "EXIST IN ('string of keys')" in the query (I believe it could be a security issue having dynamic code to support the array of keys from the application)
Solution 1 could be improved a bit but that's the idea. I dislike all of them but option 1 seems the less worst scenario.
What are your though about that?
March 30, 2009 at 11:14 am
I think I would use another approach. A slight modification to your option 3 without the dynamic SQL.
Pass the keys in VARCHAR.
Call a function to convert it's elements to a table variable.
Use the table variable to link to your target table.
You can use a function similar to this to split your VARCHAR into table.
Look for Jeff's article about tally tables. And I think there is an article here about passing arrays into SPs as well.
CREATE FUNCTION [dbo].[SplitElements](@ElementList VARCHAR(8000), @Separator CHAR(1))
RETURNS @Elements TABLE
(
Element VARCHAR(8000)
)
BEGIN
SET @ElementList = @Separator + @ElementList + @Separator
INSERT INTO @Elements
(
Element
)
SELECT
SUBSTRING(@ElementList, N+1, CHARINDEX(@Separator, @ElementList, N + 1) -N - 1)
FROM dbo.Tally
WHERE N < LEN(@ElementList)
AND SUBSTRING(@ElementList, N, 1) = @Separator
RETURN
END
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 30, 2009 at 11:46 am
yes! Thanks JacekO It does help a lot! Forgot that tally table (never used before, so easier to forget!)
I have modified a bit the tally table method to include it inside the query and it work pretty well even for a larger number of items!
My day just got better 🙂 Thanks to you!
March 30, 2009 at 11:49 am
Here's the code in case: (it does not look like the tally table method but it got from there to this solution)
DECLARE @keysArray VARCHAR(200)
SET @keysArray = '1,10,15,100,200'
SELECT neededRows FROM theTable
WHERE CHARINDEX
(
','+CONVERT(VARCHAR(12), intColumn )+',',
','+@keysArray+','
) > 0
March 30, 2009 at 11:54 am
For optione 3, you could also pass the array of variables via an xml paramater, and use that to load the temp table to use for the lookup.
March 30, 2009 at 11:56 am
yes you're right Michael Valentine Jones, thanks.
However from the application point of view it will be easier to have a string array.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply