• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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

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
falsit
Asked:
falsit
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
osiaraCommented:


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
 
osiaraCommented:
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
 
falsitAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
falsitAuthor Commented:
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
 
falsitAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
howyueCommented:
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
 
howyueCommented:
just a reminder, u need to call the sp with NULL param like this:
sp_DisplayWidgets @WidgetCode=NULL, @Cost=NULL
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now