|
|
Posted by Erland Sommarskog on 11/23/06 22:33
serge (sergea@nospam.ehmail.com) writes:
> select
> a.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2, b.bf3, b.bf4, c.cf1, c.cf2,
> c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4
> from
> a
> join b on a.apk = b.bpk
> join c on b.bpk = c.cpk
> join d on c.cpk = d.dpk
> where
> a.apk = ISNULL(@pk, a.apk)
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.
> For modifying the stored procedure in order to get a
> variable name of columns returned and avoiding
> repeating code, we built 4 objects: the stored
> procedure being called, one table function and 2 views.
> One table function so that we are able to pass a parameter.
> The views since they do not accept parameters they are
> always joined at least with the inline table function.
>
> The stored procedure generates in its body a dynamic
> SQL statement, where it queries the table function and
> the views, depending which set is required. Here is a
> code sample of our current design (you need to run the
> previous code in order for this to work).
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.
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.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|