February 12, 2011 at 1:18 pm
I'll preface this by saying dynamic SQL is unfortunately required in this situation.
I have a table that contains several rows of dynamic SQL, we'll call it tblGroups.
The dynamic SQL from tblGroups is for queries against another table, we'll call it tblContacts.
Normally I can just do something like this and it works fine for my needs:
Select @DynSQL = Query FROM tblGroups WHERE ...
Declare @SQLStatement nvarchar(max)
SELECT @SQLStatement = 'SELECT .... FROM tblContacts WHERE ' + @DynSQL
EXEC(@SQLStatement)
However, now I need to do the above for many rows in tblGroups. I'd like to avoid just calling an SP over and over for each row in tblGroups. I certainly don't want to have cursor inside the SP even though that would work.
I suppose a while loop would work but I am wondering if I could do this whole thing with a Set operation.
Any ideas on how to execute dynamic SQL in a Set operation?
February 12, 2011 at 7:47 pm
No, you cannot do that. You could maybe build your query with UNION ALL of the queries and get the result you wanted.
Are you sure you cannot avoid dynamic queries ? Do you know how to use sp_execute with parametrized queries ?
February 12, 2011 at 10:08 pm
Thanks for letting me know it's not possible, I had a feeling that wasn't the case. In the end I just went ahead with a while loop solution.
February 17, 2011 at 2:41 am
Maybe this could work for you:
build a batch of sql commands and store them into varchar(max).
Call it with parameters using sp_executesql.
Example with two sql command in one batch:
EXEC sys.sp_executesql N'select top 2 * from sysobjects where id > @p1 and name like @p2; select top 3 * from syscolumns where id < @p1',
N'@p1 int, @p2 varchar(100)',
5, '%a%'
February 17, 2011 at 3:16 am
would a CURSOR for each row in tblGroups not work, or is that a bit overkill?
February 17, 2011 at 4:39 am
It is by definition a row by row problem - whether it's a while loop or cursor is largely irrelevent.
I hope there's no user access to be able to add rows to the table holding the SQL statements or that's a very clear SQL Injection issue.
I'd like to see the architecture that means it *must* be sets of dynamic SQL - sounds like it could do with a re-design...
February 17, 2011 at 6:29 am
Correct, it's a row by row problem.
I ended up writing a while loop (instead of the dreaded CURSOR) to go through the tblGroup rows.
There is a UI that builds the SQL, so end users can't write their own SQL.
It's probably too long to describe here but unfortunately there isn't a way around using sets of dynamic SQL.
Thanks for the input guys!
February 17, 2011 at 6:32 am
I ended up writing a while loop (instead of the dreaded CURSOR) to go through the tblGroup rows.
Just to clarify, the CURSOR is dreaded because it's row by row, bot because it's a cursor. It's no less efficient (in fact if properly configured it's quicker) than a while loop.
The reason not to use them in most cases is because you could/should replace them with set based statements.
February 17, 2011 at 6:37 am
HowardW (2/17/2011)
The reason not to use them in most cases is because you could/should replace them with set based statements.
thanks for clarifying. I have used CURSORS where I only work with T-SQL and need to loop through rows and do other calcs with it. will read up on set based statements.
February 17, 2011 at 6:41 am
I understand set based is always faster than a row by row operation. That was the reason for my initial post....I wanted to run dyn sql in a set based operation, but it looks like that's not possible.
As far as while loop vs CURSOR, I've read many times that the while loop was faster, I think MS has even stated this, no?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply