Stored Procedure Polymorphism ??
-
Is there any way to change the interpretation of a stored procedure parameters? Let us say, I have a procedure that takes one parameter, Servername, and returns the details of that server. Now, If I call the procedure with no or null parameter, I would like to return details of all the servers.
I have the procedure that looks like this:
CREATE PROCEDURE usp_GetServerServiceListByName @ServerName Varchar(15) = NULL
AS
IF @ServerName IS NULL
SELECT ServerName, Environment, ServerRole FROM V_ServerDetail
ORDER BY ServerName
ELSE
SELECT ServerName, Environment, ServerRole FROM V_ServerDetail
WHERE ServerName = @ServerName
ORDER BY ServerName
GO
The problem is, I am going to have a couple more parameters, which would work the same way, ie, if null then return all values for that parameter. Is there any better alternative than running this in a nested loops. Can this be done in a single select statement?
-
hey, this should work:
SQL for one parameter:
SELECT ServerName, Environment, ServerRole FROM V_ServerDetail
WHERE ServerName = @ServerName OR @ServerName IS NULL
ORDER BY ServerName
SQL for two parameter(s):
SELECT ServerName, Environment, ServerRole FROM V_ServerDetail
WHERE ( (ServerName = @ServerName OR @ServerName IS NULL) AND (Environment = @Environment OR @Environment IS NULL) ) ORDER BY ServerName
just keep on ANDing and ORing as needed ...
-
Good J..P..
Sometimes I have implemented something like this:
SELECT ServerName, Environment, ServerRole FROM V_ServerDetail
WHERE ServerName = ISNULL( @ServerName, ServerName )
ORDER BY ServerName
But your solution is better since it works for NULL columns
/rockmoose
You must unlearn what You have learnt -
Interesting reading this after yesterday's excellent article regarding potential pitfalls in the use of the ISNULL function! Might want to double-check the article to ensure you dion't 'step into' the same mess the author struggled so valiantly with.
http://qa.sqlservercentral.com/columnists/dpoole/anisnullgotcha.asp
SET
-
Thanks to Journeyman and Rockmoose, these are good solutions.
-
The following is what I use to handle optional Parameters:
SELECT * FROM Customer
WHERE coalesce(CustName, '') = coalesce( @CustName, CustName, '')
and coalesce(CustCity, '') = coalesce( @CustCity, CustCity, '')
and coalesce(CustState, '') = coalesce( @CustState, CustState, '')
and coalesce(CustZip, '') = coalesce( @CustZip, CustZip, '')
I use this a lot on SPs behind searching and reporting screens where you can supply various criteria. If you don't supply a parameter all matching data should be returned. (Note that coalesce is like isnull but returns the first non-null value.)
-
Nice mwelcome!
So now I have to go back and check execution plans etc to find which of these approaches is the most efficient....
/rockmoose
You must unlearn what You have learnt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply