Null Parameters
-
Hello All:
I'm working on a Stored Procedure that I would like to add several parameters to. The problem I'm having is how to handle null parameters. The way I would like to code it would be when a parameter is null, skip that criteria. What I have so far follows:
CREATE PROCEDURE spTestPlanProdCycles
(
@TestPlan varchar(50),
@Product varchar(50),
@CycleBeg varChar(50)=null,
@CycleEnd varChar(50)=null
)
AS
DECLARE @sqlStmt AS NVARCHAR(4000)
IF ( @CycleBeg IS NULL AND @CycleEnd IS NULL)
SET @sqlStmt = N'SELECT tblConditions.*, tbl' + @product + 'PolicyNumbers.lngPolicyNumber
FROM tbl' + @product + 'PolicyNumbers INNER JOIN
tblConditions ON tbl'+ @product + 'PolicyNumbers.lngModelOfficeNum =
tblConditions.lngModelOfficeNum
WHERE tblConditions.strTestPlan = '''+ @TestPlan + ''' AND
tblConditions.strPlan= ''' + @product +''''
ELSE
SET @sqlStmt = N'SELECT tblConditions.*, tbl' + @product + 'PolicyNumbers.lngPolicyNumber
FROM tbl' + @product + 'PolicyNumbers INNER JOIN
tblConditions ON tbl'+ @product + 'PolicyNumbers.lngModelOfficeNum =
tblConditions.lngModelOfficeNum
WHERE tblConditions.strTestPlan = '''+ @TestPlan + ''' AND
tblConditions.strPlan= ''' + @product + ''' AND
numCycle BETWEEN ''' + @CycleBeg + ''' AND ''' + @CycleEnd + ''''
--PRINT @sqlStmt
EXEC sp_executesql @sqlStmt
GO
I plan on adding a few more variables/parameters. Is this the right approach? I know it can get a little hairy checking for nulls in each additional parameter. Does anyone have a simpler approach?
Thanks,
Cleech
-
Take a look at the "SP Wildcard" thread in this topic
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=129509
Steve
-
If it is only the two items then you have four scenarios. For optimal performance consider maybe using multiple procdures for all 4 cases and have a central procedure determine which to kick off.
This is an example of one I use for Update,Delete,Insert logic on input from a web page I use.
CREATE PROC ip_UpdateSP;1
@IDX varchar(10),
@PID int,
@TT int
AS
SET NOCOUNT ON
DECLARE @LID int
SET @LID = (SELECT [Index] FROM dbo.LD WHERE [IDX#] = @IDX)
If @PID = 0
EXEC ip_UpdateSP;2 @LID, @tt
ELSE
BEGIN
IF Exists(SELECT * FROM dbo.tbl_PP WHERE FK_LDID = @LID AND FK_TTID = @tt)
EXEC ip_UpdateSP;3 @LID, @PID, @tt
ELSE
EXEC ip_UpdateSP;4 @LID, @PID, @tt
END
GO
-- Delete support personnel
CREATE PROC ip_UpdateSP;2
@LID int,
@TT int
AS
SET NOCOUNT ON
DELETE dbo.tbl_PP WHERE FK_LDID = @LID AND FK_TTID = @tt
GO
-- Update personnel item.
CREATE PROC ip_UpdateSP;3
@LID int,
@PID int,
@TT int
AS
SET NOCOUNT ON
UPDATE
dbo.tbl_PP
SET
FK_PID = @PID
WHERE
FK_LDID = @LID AND
FK_PID != @PID AND
FK_TTID = @tt
GO
-- Insert new support personnel item.
CREATE PROC ip_UpdateSP;4
@LID int,
@PID int,
@TT int
AS
SET NOCOUNT ON
INSERT dbo.tbl_PP
(
FK_LDID,
FK_PID,
FK_TTID
 
VALUES
(
@LID,
@PID,
@TT
 
GO
-
How about this...
I have removed the single qutoations for clarity, you need to add it as necessary
Modify your where clause for the numCycle as below...
( (numCycle BETWEEN @CycleBeg AND @CycleEnd) OR (@CycleBeg is null AND @CycleEnd is null) )
What will happen is when ever (@CycleBeg is null AND @CycleEnd is null) is true the "BETWEEN" filter will be ignored.
-
Since you're using dynamic SQL, why not declare variables for each of the phrases that can be null and create them separately, like this example using @cond1 for the CycleBeg/CycleEnd BETWEEN test. You can repeat this process for as many parameters as you like and still write a single query.
CREATE PROCEDURE spTestPlanProdCycles
(
@TestPlan varchar(50),
@Product varchar(50),
@CycleBeg varChar(50)=null,
@CycleEnd varChar(50)=null
)
AS
DECLARE @sqlStmt AS NVARCHAR(4000)
DECLARE @cond1 NVARCHAR(100)
IF @CycleBeg IS NULL OR @CycleEnd IS NULL
SET @cond1 = ''
ELSE
SET @cond1 = 'AND numCycle BETWEEN ''' + @CycleBeg + ''' AND ''' + @CycleEnd + ''''
SET @sqlStmt = N'SELECT tblConditions.*, tbl' + @product + 'PolicyNumbers.lngPolicyNumber
FROM tbl' + @product + 'PolicyNumbers INNER JOIN
tblConditions ON tbl'+ @product + 'PolicyNumbers.lngModelOfficeNum =
tblConditions.lngModelOfficeNum
WHERE tblConditions.strTestPlan = '''+ @TestPlan + ''' AND
tblConditions.strPlan= ''' + @product +'''' + @cond1
--PRINT @sqlStmt
EXEC sp_executesql @sqlStmt
GO
-
I would replace the part above - @cond1 - in the construction of your dynamic SQL with the following:
CASE WHEN @CycleBeg + @CycleEnd is null then '' else 'AND numcycle BETWEEN ''' + @CycleBeg + ''' AND ''' + @CycleEnd + '''' END
Since adding a NULL to any other value will render the result NULL this successfully checks if either of the variables you have passed is null then don't add the additional where clause.
Dave Hilditch.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply