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
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (@WidgetCode <> '')
IF (@Cost <> '')
IF (@WidgetCode = '') AND (@Cost = '')
--Example to execute
--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'