|
|
Posted by Tony Rogerson on 09/29/07 08:45
> CREATE PROCEDURE Foobar
> (@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
> AS
> SELECT ..
> FROM Floob
> WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
> AND ..;
>
> YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>
Amazing, you just don't learn do you.
What is the risk and resource assessment of adding value number 6?
Resource assessment...
1) Change the stored procedure to accept an extra parameter
2) Change the query IN to accept an extra parameter
3) Change all the applications that call the stored procedure to accept
the extra parameter - that can be '1' to 'n' in a real environment where
applications share common logic (stored procedures).
Risk assessment...
1) Database changes - requires application to be taken offline while the
release to add the extra parameter is done
2) Application changes - each application binary needs to be updated to
use the new parameter; for fat clients that would be quite an involved task
for a couple of thousand clients even with SMS.
3) Testing - did you capture all applications using the procedure, each
application requires a test plan and testing.
Now, if you had used CSV instead - you'd pass a single parameter to the
stored procedure containing 1 to 'n' values then you wouldn't have any of
the above, it would just work; there would be no requirement to take the
application offline, there would be no risk that some clients didn't get
updated properly so weren't using the correct version of the executable
etc...
I really do wish you'd start listening to people who actually do this type
of thing day in day out and have done so for 20 + years, sitting writing
books for 30+ years and teaching people is no replacement for solid
industrial experience.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1191027166.216455.160600@50g2000hsm.googlegroups.com...
>>> In my stored procedure, I want to parse @ArrayOfDays into @d1 through
>>> @d5 .. execute a dynamically built SELECT. <<
>
> You are doing almost everything wrong. SQL does not work this way.
> Just pass a simple five parameter list. Then clean up your data in the
> procedure body. Try this for a skeleton
>
> CREATE PROCEDURE Foobar
> (@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
> AS
> SELECT ..
> FROM Floob
> WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
> AND ..;
>
> YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>
Navigation:
[Reply to this message]
|