What's the best way to SELECT from a stored procedure, but with different WHERE clauses?

[0to60]

I have a fairly complicated sproc that returns data. I'd like to use this
sproc in a buncha different places, but I'll need differing conditions in my
WHERE clause. Like, sometimes I'll be selecting all the customers where
State = 'IL', or maybe where CustomerName LIKE "ABC%'.

What's the best way to do this? Right now I'm DECLAREing a table, inserting
the rows from the sproc, then selecting from the table and applying my where
clause to that.

 

[Aaron Bertrand [SQL Server MVP]]

http://www.sommarskog.se/dyn-search.html

 

[Alex Kuznetsov]

I have a fairly complicated sproc that returns data.  I'd like to use this
sproc in a buncha different places, but I'll need differing conditions inmy
WHERE clause.  Like, sometimes I'll be selecting all the customers where
State = 'IL', or maybe where CustomerName LIKE "ABC%'.

What's the best way to do this?  Right now I'm DECLAREing a table, inserting
the rows from the sproc, then selecting from the table and applying my where
clause to that.

Alternatively you can reuse your code as a table-valued UDF, not as a
stored procedure, and apply additional conditions as follows:

SELECT <columns> FROM(

SELECT <columns> FROM Readers.MyUDF(<parameters>) AS t
) AS t
WHERE <add your conditions here>

 

[Ray Costanzo]

I'm no SQL expert, but I often do something like this:

CREATE PROC GetStuff (
@State varchar(2) = NULL,
@CustomerNameContains = NULL,
@CustomerNameStartsWith = NULL
--, other filtering options

) AS

SELECT
this, this, theOther
FROM
theTable
WHERE
((State = @State) OR (@State IS NULL))
AND
((CustomerName LIKE '%' + @CustomerNameContains + '%') OR
(@CustomerNameContains IS NULL))
AND
((CustomerName LIKE @CustomerNameStartsWith + '%') OR
(@CustomerNameStartsWith IS NULL))

So, it'd work like so:

EXEC GetStuff
--returns everything

EXEC GetStuff @State='IL'
--returns stuff in IL

EXEC GetStuff @CustomerNameContains = 'shipping'
--returns customers with "shipping" in name

EXEC GetStuff @State = 'KY', @CustomerNameStartsWith = 'Joe'
--returns customers whose name starts with "Joe" and are in Kentucky

 

[--CELKO--]

I have a fairly complicated sproc that returns data.  I'd like to use this sproc in a buncha different places, but I'll need differing conditions in my WHERE clause.  Like, sometimes I'll be selecting all the customers where state_code = 'IL', or maybe where customer_name LIKE "ABC%'. <

First you un-learn your procedural coding mindset. Write this "fairly
complicated" table expression as a VIEW. You put the VIEW in queries
that qualify it with more predicates.

You just re-invented the 1950's scratch tape from obsolete magnetic
tape file systems! So much for RDBMS.

 

[Cor Ligthert[MVP]]

Depends where you are using it, as it is in AdoNet then it is different then
direct in SQL management studio.

Therefore maybe posting to the right group, without crossposting can help
you to get the right answer.

Cor

 

[Erland Sommarskog]

I have a fairly complicated sproc that returns data. I'd like to use
this sproc in a buncha different places, but I'll need differing
conditions in my WHERE clause. Like, sometimes I'll be selecting all
the customers where State = 'IL', or maybe where CustomerName LIKE
"ABC%'.

What's the best way to do this? Right now I'm DECLAREing a table,
inserting the rows from the sproc, then selecting from the table and
applying my where clause to that.

That is difficult to answer without more knowledge about the problem
at hand.

If the "fairly complicated stored procedure" is a single (but complicated)
SELECT, or can be written as one, then the best solution is do as Celko
suggests and put in a view on an inline table function. This will give
you the best performance when you add certain subconditions.

However, it may not be possible to write the procedure that way, one
way or another. The disadvantage with running the basic SELECT, and
then do the final filtering is that if the basic SELECT to get all
possible rows takes five seconds, it still take five seconds to get
all customers whose names start with ABC, when a direct query would have
run in 100 ms, thanks to an index on customer name.

Therefore it may be better to add these possible conditions as parameters
to the procedure, but you need to use them with care to get good
performance. (And this is often contradictory with getting maintainable
code in these contexts.)

Of course, if this complicated stored procedure itself completes in 100 ms,
and is not executed 100 times a second, there is no performance issue
at all, and in that case your current approach is probably the best.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[0to60]

First you un-learn your procedural coding mindset. Write this "fairly

complicated" table expression as a VIEW. You put the VIEW in queries
that qualify it with more predicates.

I can't make it a VIEW. The "complicated" part comes from the fact that the
sproc goes something like this:

IF @getItTheHardWay
SELECT colums
FROM lots of joins,
WHERE a buncha subqueries do stuff
ELSE
SELECT columns
FROM table

You just re-invented the 1950's scratch tape from obsolete magnetic

tape file systems! So much for RDBMS.

That's why I was asking for other opinions...

 

[sloan]

You can look at my take on it here:
http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/

But read the
http://www.sommarskog.se/dyn-search.html
as previously mentioned as well.

 

[--CELKO--]

I can't make it a VIEW.  The "complicated" part comes from the fact that the stored procedure goes something like this:

IF @getItTheHardWay
    SELECT columns
    FROM lots of joins,
    WHERE a buncha subqueries do stuff
ELSE
    SELECT columns
    FROM table <<

I make a fair amount of my consulting work converting procedural IF-
THEN-ELSE logic into CASE expressions. Once you learn SQL and
declarative programming, it is actually a bitch to write stuff like
your skeleton. You start with the "SELECT <column list>" and ask how
each column is declared, then you re-factor things, etc. -

You can usually get 1 order of magnitude improvement on small tables,
but I have found that it goes up to 2-3 orders of magnitude as the
tables get larger. The best I have done was ~2500 times the thru-put
by actual measurement.