November 12, 2001 at 3:04 am
Hello,
A trivial task that is bugging me.
I have a list of Keys that a multi-select dropdown in the frontend.
I do not want to write dynamic sql to avoid generation of Query Plan every time I hit the DB server.
How do I manange to get all rows corresponding to the Keys ?
I have tried the following :
Passed the list of keys as a comma separated string (varchar(50)) as a parameter to a stored proc.
I was assuming that
Create Proc GetValues
@sKeys varchar(50)
As
Select * from Table Where Key in (@sKeys)
would work.
But it does not.
Any ideas how to avoid Dynamic SQL yet achieve this?
Regards,
TIA,
RB
November 12, 2001 at 4:46 am
I don't know that dynamic sql is always bad. You could create a new table that would hold the user spid and a key, then insert your 50 values or whatever into that table and then join to it in your proc. Should be pretty fast, but you definitely should benchmark the two options.
Andy
November 12, 2001 at 6:25 am
You can create dynamic SQL within you stored procedure. You would not get the benefit of compiled SQL but you would have the benefit of all your database code inside of a stored procedure. I use sp_executesql for this.
SET @l_SQL_TXT = "Select * from Table Where Key in ( " + RTRIM( @sKeys ) + ")"
EXECUTE @l_rc = sp_executesql @l_SQL_TXT
This will do it, not sure of performance though.
Are you avoiding dynamic SQL for performance or maintenance reasons?
Cheers.
November 12, 2001 at 7:15 am
Hi,
I was avoiding Dynamic SQL for Performance benefit of the compiled Query Plan.
One more reason for this is that I expect this function to be called the most in my application.
So any performance benefit would be have been
good.
Any way , I will go ahead with Dynamic SQL in stored procedure for now.
But there should be a better way.
Like I had a number of values.
They had a priority
I had to compare a column "A" with values a1,a2,a3 with logic
if a1 is NULL the Check a2,
if a2 also is NULL then Check a3
I went the "IS NULL" way for sometime and soon realised that if a value a4 gets added , my stored procedure would have to change tremendously
Then I hit upon
COALESCE that did the trick
Now the whole "if" structure in the stored proc has been replaced by one comparison
Where A= COALESCE(a1,a2,a3)
And I am confident that even if a4 gets added , I dont have to worry.
Any way, the search for better SQL continues.
Thanx Everybody,
Regards,
RB
November 12, 2001 at 10:10 am
November 22, 2001 at 6:17 am
I've found that if you want to pass a string to use as the IN list in a WHERE clause it doesn't work!
What I did was put a small header that lets you pass a comma delimited list. This is then broken out into a temporary table that can then be used to join to your table in the normal way.
See script below for the example of breaking out the delimited list.
DECLARE @stGroupList VARCHAR(50)
DECLARE@lGroupListLengthInt
DECLARE @lPositionInt
DECLARE @lCommaInt
SET @stGroupList='1,2,3,4,5'
SET@lGroupListLength = LEN ( @stGroupList )
SET @lPosition=1
SET@lComma=1
CREATE TABLE #Tbl_Groups ( GroupID Int )
WHILE @lPosition <= @lGroupListLength
BEGIN
SET @lComma = CHARINDEX ( ',' , @stGroupList , @lPosition)
IF @lComma = 0
BEGIN
INSERT #Tbl_Groups ( GroupID )
SELECT CAST( SUBSTRING( @stGroupList , @lPosition ,1+ @lGroupListLength - @lPosition) AS Int )
SET @lPosition= @lGroupListLength + 1
END
ELSE
BEGIN
INSERT #Tbl_Groups ( GroupID )
SELECT CAST ( SUBSTRING( @stGroupList , @lPosition , @lComma - @lPosition) AS INT )
SET @lPosition = @lComma+1
END
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply