filtering data from lots of procedures

Question

filtering data from lots of procedures

Tudor Gabriel

SSC-Addicted

Points: 483
More actions
July 31, 2006 at 5:24 am

#91239

hi,
30-40 forms which show different data in different ways ... grouped by different criteria.
now, this translates in dozens of stored procedures.
i need a way to filter the data in the forms, using some dinamic filters (the user has to chooose from lots of conditions which to apply).
i can't use dinamic sql ... the first idea that comes in mind ... because there are lots of procedures in sqlserver which must be written in the client code ... and the executed like comands.
if i just generate the where part ... i need the ENTIRE procedure and some of them are huge writen as EXEC ('SELECT ...') and then just add the filter, this is bad because, some of them are bigger then 4000 chars , and i can't use sp_executesql.
i hope that some of you had this problem ... and somehow you managed to find a way around dinamic sql .

Viewing 5 posts - 1 through 4 (of 4 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I'm not clear why you cannot use "Dynamic SQL" within a Stored Procedure passing the selected values by your client.

If you can use this approach, please read the following to ensure SQL Injection does not kill your server: Curses & Blessings of Dynamic SQL

If that is not possible within your environment, you may want to think in terms of setting up tables with possible choices, (these can have new entries added easily) or a very complicated CASE statement...

(Others should be able to give you alternative ideas as well)

I wasn't born stupid - I had to study.

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 2

it's possible to pass dinamic sql to the procs, but imagine that a proc is huge, the main select inside is huge. i dont want to do the entire main select as dinamic sql, it will make it unmaintainable (no more error checking from the compiler etc) + it's bigger the 4000 so no sp_executeSQL is posible ... and exec isn't the way/

i was thinking about inserting in a variable table the ouput of the procedure , and then applying somehow the filter. but in the dinamic sql, i can't use variable tables because of the scope . i tried using temp tables, but they cause a lot of cache miss and even a recompile inside the proc. i'm spinning around my tail here, trying to find a solution ... and even worse, one that wont require a lot of modifications in the procs

a table function would be nice ... but guess what .. can't use exec inside functions

i tried to insert the filters output inside a variable table but again the server comes and throws another rule : can't use INSERT EXEC with variable(@) tables

everytime i think of something, the server will find a way to stop me from doing it.

im just beeing frustrated and scared at the thought of making the entire procedures red (color of strings) and then exec- ing inside

Lance Sanders SSC Eights! Points: 903 More actions · Answer 3

I'm not a big fan of dynamic SQL, but sometimes the situation requires it. A couple of possibilities:

To minimize code clutter, you could code the main select statement as a view or table function, then generate your dynamic sql against the view or TF. Either of these should be faster than storing intermediate results in a temp table. I've done this with table functions to generate pivot tables with very good performance results.

To get around the size limit of sp_executesql, use EXECUTE. You can concatenate strings within the statement to reach well past 8000 bytes, ie: EXEC (@str1+@str2+...)

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 4

thank you, i just never thought of views as part of the solution and i think it might work.

i was blinded by thinking the other way around, store the filters in a table function and then join with the huge select, in functions i couldn't use dinamic sql and i just dismissed the idea...

thank you