Flexible Stored Procedure 
Author Message
 Flexible Stored Procedure
Hi all,
   I want to create a stored procedure that accepts a filtering parameter but if the parameter content (string) is blank, instead of returning filtered records, I want to return all records. How can I write such a stored procedure without having to put the IF...ELSE... statement (to check if the parameter is blank) and achieve the task only by one SQL statement. Could someone give me sample code ?

Thank you very much

Setya

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!



Sat, 15 Nov 2003 14:55:49 GMT
 Flexible Stored Procedure

Setya,

What exactly do you mean by filtering ?

You could try something like


set nocount on

go


--
I hope this helps

Stephen Robinson - SQL Server MVP

Please reply to the newsgroup only, not by email



Hi all,
   I want to create a stored procedure that accepts a filtering parameter
but if the parameter content (string) is blank, instead of returning
filtered records, I want to return all records. How can I write such a
stored procedure without having to put the IF...ELSE... statement (to check
if the parameter is blank) and achieve the task only by one SQL statement.
Could someone give me sample code ?

Thank you very much

Setya

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 15 Nov 2003 16:03:03 GMT
 Flexible Stored Procedure
Thanks Stephen,
    I can see that you already understand what I mean by filtering.
    Your code looks cool, but what if instead of using LIKE I want to use '=' ? and what if I have more than one parameter to check for blank ?

Setya

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 15 Nov 2003 20:41:21 GMT
 Flexible Stored Procedure
Setya,

I hope this example helps.  Just need to change the case statement around.

drop proc testfilter
go

set nocount on


go


or
exec testfilter '',''

--
I hope this helps

Stephen Robinson - SQL Server MVP

Please reply to the newsgroup only, not by email



Quote:
> Thanks Stephen,
>     I can see that you already understand what I mean by filtering.
>     Your code looks cool, but what if instead of using LIKE I want to use

'=' ? and what if I have more than one parameter to check for blank ?
Quote:

> Setya

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sat, 15 Nov 2003 21:40:17 GMT
 Flexible Stored Procedure
Thanks Stephen,

Now I have the following stored procedure script (I skip the variable and parameter declaration here) :



ELSE


Now how can I change above script into one single SELECT statement ?

I try to make the above example as simple as possible.
The reality is I have one table with about 100 fields in it and for some reason I have to mention them one by one and script similliar to the above example requires me to write the SELECT part repeatedly in on stored procedure which I consider inefficient thing to do.

Thanks

Setya

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 18 Nov 2003 19:02:13 GMT
 Flexible Stored Procedure
You can use CASE within your SQL Statement to specify the expression to be
compared:

SELECT EmployeeID
FROM EmployeeTable


    FirstName
ELSE
    LastName
END

--
Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------



Thanks Stephen,

Now I have the following stored procedure script (I skip the variable and
parameter declaration here) :



ELSE


content.
Now how can I change above script into one single SELECT statement ?

I try to make the above example as simple as possible.
The reality is I have one table with about 100 fields in it and for some
reason I have to mention them one by one and script similliar to the above
example requires me to write the SELECT part repeatedly in on stored
procedure which I consider inefficient thing to do.

Thanks

Setya

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 18 Nov 2003 21:38:46 GMT
 Flexible Stored Procedure
Thanks Dan , your example saves my life.

Setya

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 22 Nov 2003 10:08:16 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Help required: Flexible stored procedures instead of concatenated text

2. Calling a Java Stored Procedure from another Java Stored Stored Procedure

3. Stored Procedure calling Stored Procedure

4. Executing a Stored Procedure in a Stored Procedure and selecting on the result

5. Call a stored procedure from another stored procedure

6. Calling a stored procedure with parameters from another stored procedure

7. using SQL stored procedure results in stored procedure

8. Using Resultset in Stored Procedure in another stored procedure

9. Stored Procedure using another Stored Procedure

10. Calling a Stored Procedure from a Stored Procedure

11. How to call an Oracle Stored procedure from MSSQL Server Stored Procedure

12. Calling a stored procedure with parameters from another stored procedure


 
Powered by phpBB® Forum Software