|
|
Posted by serge on 11/24/06 04:24
> Beware that this query will always scan the table a, even if you provide
> a value in @pk. If the table is modest in size this may not be an issue
> to you, but I like to point it out.
Actually we realized that and we already moved the WHERE
logic in the SP using dynamic SQL.
> Personally, I find adding one function and two views being bit too much.
> I would probably only show it all into variables in the stored procedure
> that builds the dynamic SQL.
> Whether to use dynamic SQL or not for the task... well, on the one hand
> you don't have to repeat code, on the other hand there may be issues
> with giving users permissions. (Then again, there may be not.) One
> alternative would be insert the big result set into a temp table of
> table variable, and then use static SQL to select from the temp
> table. But obviously bouncing the data an extra time has a performance
> cost.
Two reasons why we chose not to use a single SP with dynamic SQL:
1- If we only use dynamic SQL, we are passing the limit for storing text
and we don't want to use EXEC calls to handle this.
2- It is possible that we end up re-using the Inline Table Function in
other SPs.
> The ideal solution would be to use a preprocessor and define the logic
> that needs to be repeated as a macro. But most people don't use any
> preprocessor with their SQL. (We do in our shop.)
In our situation we can not.
Thanks Erland. At least we now know what we are using is not bad.
Navigation:
[Reply to this message]
|