falsit
asked on
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
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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 =''?
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 =''?
ASKER
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!?! :)
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!?! :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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) )
e.g.
IF isnull(@cost,'') <> '' SET @sql = 'select widgetcode, cost,description '
ELSE SET @sql = 'select * '
SET @sql = @sql +'
FROM Widgets
WHERE ( '+convert(varchar,isnull(@
AND ( '+convert(varchar,isnull(@
order by '+convert(varchar,isnull(@
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just a reminder, u need to call the sp with NULL param like this:
sp_DisplayWidgets @WidgetCode=NULL, @Cost=NULL
sp_DisplayWidgets @WidgetCode=NULL, @Cost=NULL
Open in new window