Solved

stored procedure -> confitional execution of SELECT's WHERE clause

Posted on 2008-06-14
11
576 Views
Last Modified: 2008-10-23
Hi,

I have a stored procedure that returns a list of widgets, however  I want to pass in a parameter to allow the user to filter the selection, but by default return all.

In the past i've achieved this by have the same SELECT statement multiple times, whilst changing the WHERE, i.e. add the where when there is a paramter and when there's not using a SELECt without a where.
See below code on how i'd acheieve this now:

The question is how can the below be re-written to just have one SELECT staement, but using a paramter is it's passed in?  Seems stupid have the same SELECT statement many times!!!

CURRENT SOLUTION - attached to the snippet area

CREATE PROCEDURE [dbo].[sp_DisplayWidgets]

	@WidgetCode int = null,

	@Cost float = null

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;
 

	IF (@WidgetCode <> '')	

	BEGIN

		SELECT *

		FROM Widgets

		WHERE WID=@WidgetCode 

	END

	

	IF (@Cost <> '')	

	BEGIN

		SELECT *

		FROM Widgets

		WHERE CID=1040

	END
 

	IF (@WidgetCode = '') AND (@Cost = '')	

	BEGIN

		SELECT *

		FROM Widgets

	END
 

END
 

--Example to execute

--Returns all

--sp_DisplayWidgets @WidgetCode='', @Cost=''
 

-- Returns just Widgets with the ID of 4

--sp_DisplayWidgets @WidgetCode='4', @Cost=''
 

--Returns just widgets with the Cost that match 1040

--sp_DisplayWidgets @WidgetCode='', @Cost='1040'

Open in new window

0
Comment
Question by:falsit
  • 3
  • 2
  • 2
  • +1
11 Comments
 
LVL 2

Expert Comment

by:osiara
ID: 21786414


CREATE PROCEDURE [dbo].[sp_DisplayWidgets]

        @WidgetCode int = null,

        @Cost float = null

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

 

        SELECT *

        FROM Widgets

        WHERE (@WidgetCode IS NULL OR WID=@WidgetCode) AND (@Cost IS NULL OR CID = @Cost)

 

END

Open in new window

0
 
LVL 2

Accepted Solution

by:
osiara earned 168 total points
ID: 21786422
or :
CREATE PROCEDURE [dbo].[sp_DisplayWidgets]

        @WidgetCode int = null,

        @Cost float = null

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

 

        SELECT *

        FROM Widgets

        WHERE (@WidgetCode IS NULL OR @WidgetCode = '' OR WID=@WidgetCode) AND (@Cost IS NULL OR @Cost = '' OR CID = @Cost)

 

END

Open in new window

0
 

Author Comment

by:falsit
ID: 21786436
Thanks, how about ORDER BY?

By default no ORDER BY, but what if the sp was passed in a paramter and I'd want to ORDER By Description or Cost or PurchaseDate Desc

but only id @Ordering char(20) = null      wasn't null?
0
 

Author Comment

by:falsit
ID: 21786465
osiara - I'm confused by the syntax logic in your example, can you breifly describe syntax is achieving per statement?

Example:
WHERE (@WidgetCode IS NULL OR @WidgetCode = '' OR WID=@WidgetCode)

the last element is asking for all WID that equal the @WidgetCode paramter, but how does that logic match up witht he two former elements?   What is it doing when asking for @WidgetCode to be NUll o =''?
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:falsit
ID: 21786480
Another angle on this is sometime I have the need to only SELECT certain fields based on paramters, how would this be achieved?

Say when @Cost is passed in it doesn't select all, but instead it asks for Select WID, Cost, Desc

Any ideas?

These kind of things have always bugged me!?! :)
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 166 total points
ID: 21788883
Can do this type of thing.... easy enough,

the isnull(@widgetcode,'') = ''  basically says if @widgetcode is null or it is '' then the "where" part of the statement will also return true, so it effectively does not have to match the widget code value of the row....

but not sure I understand your Order by ?

For Order by, you can do something like a case statement in the select statement and then order by that column, or, use Dynamic SQL to build up a statement... e.g.




ALTER PROCEDURE [dbo].[sp_DisplayWidgets]

	@WidgetCode int = null,

	@Cost float = null,

	@OrderBy int = null

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

 

	DECLARE @sql varchar(max)
 

	set @sql = '

	SELECT *

	FROM Widgets

	WHERE ( '+convert(varchar,isnull(@widgetcode,''))+' = '''' or widgetcode = '+convert(varchar,isnull(@widgetcode,''))+') 

	AND   ( '+convert(varchar,isnull(@cost,''))+' = '''' or cost = '+convert(varchar,isnull(@cost,''))+') 

	order by '+convert(varchar,isnull(@orderby,3))
 

	print @sql

	exec (@SQL)

 

END

 

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21788901
Oh, and for your conditional columns based on @cost example, then the dynamic SQL is probably the only real way of achieving it...

e.g.

      IF isnull(@cost,'') <> '' SET @sql = 'select widgetcode, cost,description '
      ELSE SET @sql = 'select * '

      SET @sql = @sql +'
      FROM Widgets
      WHERE ( '+convert(varchar,isnull(@widgetcode,''))+' = '''' or widgetcode = '+convert(varchar,isnull(@widgetcode,''))+')
      AND   ( '+convert(varchar,isnull(@cost,''))+' = '''' or cost = '+convert(varchar,isnull(@cost,''))+')
      order by '+convert(varchar,isnull(@orderby,3))
0
 
LVL 2

Assisted Solution

by:howyue
howyue earned 166 total points
ID: 21800877
since u wan it to be so 'dynamic', u should use dynamic sql as the name itself suggesting u to use it.
agreed with mark that u can condition the WHERE & ORDER statement but i can't find anyway to condition SELECT, hoping somebody would give me the answer, if any.

dynamic SQL is ur solution.
ALTER PROCEDURE [dbo].[sp_DisplayWidgets]

        @WidgetCode INT = null,

        @Cost FLOAT = null

AS

BEGIN

	SET NOCOUNT ON
 

	SELECT WidgetCode, Cost

	FROM Widgets

	WHERE WidgetCode = ISNULL(@WidgetCode, WidgetCode)

	AND Cost = ISNULL(@Cost, Cost)

	ORDER BY ISNULL(@WidgetCode, 1), ISNULL(@Cost, 2)
 

END

Open in new window

0
 
LVL 2

Expert Comment

by:howyue
ID: 21800884
just a reminder, u need to call the sp with NULL param like this:
sp_DisplayWidgets @WidgetCode=NULL, @Cost=NULL
 
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now