Hi,
I'm trying to create a stored procedure that checks to see whether the parameters are NULL. If they are NOT NULL, then the parameter should be used in the WHERE clause of the SELECT statement otherwise all records should be returned.
sample code:
SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300), @.ProjectManagerIDint, @.DeptCodevarchar(20), @.ProjIDvarchar(50), @.DateRequesteddatetime, @.DueDatedatetime, @.ProjectStatusIDint)ASBEGINSET NOCOUNT ONIF @.ProjectTitleISNOT NULL AND @.ProjectManagerIDISNULL AND @.DeptCodeISNULL AND @.ProjIDISNULL AND @.DateRequestedISNULL AND @.DueDateISNULL AND @.ProjectStatusIDISNULLSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE ProjectTitle = @.ProjectTitle;ELSE IF @.ProjectTitleISNOT NULL AND @.ProjectManagerIDISNOT NULL AND @.DeptCodeISNULL AND @.ProjIDISNULL AND @.DateRequestedISNULL AND @.DueDateISNULL AND @.ProjectStatusIDISNULLSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE ProjectTitle = @.ProjectTitleAND ProjectManagerID = @.ProjectManagerID;ELSESELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_Project;
I could do this using IF-ELSE but that would require a ridiculous amount of conditional statements (basically 1 for each combination of NULLs and NOT NULLs). Is there a way to do this without all the IF-ELSEs?
Thanks.
it will be easier to dynamically build your sql string, while you'll still have to use IF-ELSE you won't have to check for each and every condition combination, just check for each parameter. for example...
CREATE PROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitlevarchar(300), @.ProjectManagerIDint, @.DeptCodevarchar(20), @.ProjIDvarchar(50),
@.DateRequesteddatetime, @.DueDatedatetime, @.ProjectStatusIDint)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.strSQL varchar(4000)
SET @.strSQL = "SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID "
SET @.strSQL = @.strSQL + "FROM dbo.tbl_Project "
SET @.strSQL = @.strSQL + "WHERE "
IF @.ProjectTitle IS NOT NULL
BEGIN
SET @.strSQL = @.strSQL + "ProjectTitle = " + @.ProjectTitle
END
IF @.ProjectManagerId IS NOT NULL
BEGIN
SET @.strSQL = @.strSQL + " AND ProjectMangerId = " + @.ProjectMangerId
END
<conditions for each param>
EXEC(@.strSQL)
GO
|||
There is a better way than using lots of IF and ELSE statements. Rewrite your stored proecedure so that it doesn't matter what parameters are passed it will always work. eg.
CREATEPROCEDURE [dbo].[GetProjectInfo]
(
@.ProjectTitlevarchar(300)=NULL, @.ProjectManagerIDint=NULL, @.DeptCodevarchar(20)=NULL,
@.ProjID
varchar(50)=NULL, @.DateRequesteddatetime=NULL, @.DueDatedatetime=NULL, @.ProjectStatusIDint=NULL)
AS
BEGIN
SET
NOCOUNTONSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE(ProjectTitle= @.ProjectTitleOR @.ProjectTitleISNULL)AND(ProjectManagerID= @.ProjectManagerIDOR @.ProjectManagerIDISNULL)AND(DeptCode= @.DeptCodeOR @.DeptCodeISNULL)AND(ProjID= @.ProjIDOR @.ProjIDISNULL)AND(.... [rest of parameters])
END
|||SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitle varchar(300), @.ProjectManagerID int, @.DeptCode varchar(20), @.ProjID varchar(50),
@.DateRequested datetime, @.DueDate datetime, @.ProjectStatusID int)
AS
SET NOCOUNT ON
IF DATALENGTH(RTRIM(@.ProjectTitle)) = 0 SET @.ProjectTitle = NULL
IF DATALENGTH(RTRIM(@.ProjectManagerID)) = 0 SET @.ProjectManagerID = NULL
IF DATALENGTH(RTRIM(@.ProjectStatusID)) = 0 SET @.ProjectStatusID = NULL
IF DATALENGTH(RTRIM(@.DeptCode)) = 0 SET @.DeptCode = NULL
IF @.DueDate >= CONVERT(DATETIME, '9999-12-31 00:00:00') SET @.DueDate = NULL
IF @.DateRequested >= CONVERT(DATETIME, '9999-12-31 00:00:00') SET @.DateRequested = NULL
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project
WHERE ProjectTitle = COALESCE(@.ProjectTitle, ProjectTitle)
AND ProjectManagerID = COALESCE(@.ProjectManagerID, ProjectManagerID)
AND ProjectStatusID = COALESCE(@.ProjectStatusID, ProjectStatusID)
AND DeptCode = COALESCE(@.DeptCode, DeptCode)
AND DateRequested = COALESCE(@.DateRequested, DateRequested)
AND DueDate = COALESCE(@.DueDate, DueDate)|||
The beauty of the S.P. I just posted is that it remains very simple no matter what combination of search is used. For unused string crriteria, use ''. For Integer use use 0. For dates pass a date of 31/Dec/9999.
The idea was suggested some years ago to me by Joe Celko.
|||
Woah.
Thanks for all the quick replies.
I'm away from my development machine right now, but I'll try the solutions in a few hours and let you know how it works out.
Thanks again.
|||I would recommend user "Connect"'s solution. Too many IF loops can screw up the query plan.|||
Connect:
There is a better way than using lots of IF and ELSE statements. Rewrite your stored proecedure so that it doesn't matter what parameters are passed it will always work. eg.
CREATEPROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitlevarchar(300)=NULL, @.ProjectManagerIDint=NULL, @.DeptCodevarchar(20)=NULL,
@.ProjIDvarchar(50)=NULL, @.DateRequesteddatetime=NULL, @.DueDatedatetime=NULL, @.ProjectStatusIDint=NULL)
AS
BEGIN
SETNOCOUNTON
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project
WHERE(ProjectTitle= @.ProjectTitleOR @.ProjectTitleISNULL)
AND(ProjectManagerID= @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND(DeptCode= @.DeptCodeOR @.DeptCodeISNULL)
AND(ProjID= @.ProjIDOR @.ProjIDISNULL)
AND(.... [rest of parameters])
END
I was going over the code you posted, and it occurred to me that I made a *slight* error.
The ProjID is actually of the form DeptCode-Number (e.g. ACCT-1) and I don't have the DeptCode field in my tbl_Project.
Basically, I want to do something like:
CREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL,@.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)ASBEGINSETNOCOUNT ONSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE (ProjectTitle = @.ProjectTitleOR @.ProjectTitleISNULL)AND (ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)--AND (DeptCode = @.DeptCode OR @.DeptCode IS NULL)AND ((IF @.DeptCodeISNOT NULL ProjID = @.DeptCode +'-' +'%')ELSE ProjID = @.ProjIDOR @.ProjIDISNULL)-- OR @.DeptCode IS NULL))AND (DateRequested = @.DateRequestedOR @.DateRequestedISNULL)AND (DueDate = @.DueDateOR @.DueDateISNULL)AND (ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)END
which is to say,
if @.DeptCode is not null, ProjID = @.DeptCode + '-' + (any number)
else ProjID = @.ProjID
I tried the code I posted, but understandably I get errors of Incorrect syntax near keyword IF an near ProjID.
Thanks again.
|||
Hi,
I was wondering if someone could help me out with the problem of implementing
IF @.DeptCode IS NOT NULL
ProjID = @.DeptCode + '-' + (any number)
ELSE ProjID = @.ProjID
in the code given by Connect.
Thanks.
|||
Try something like this:
CREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL,@.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)ASBEGINSET NOCOUNT ONDeclare @.nintIF @.DeptCodeISNOT NULLSET @.Deptcode = @.Deptcode +'-' +convert(varchar,@.n)SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE (ProjectTitle = @.ProjectTitleOR @.ProjectTitleISNULL)AND (ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)--AND (DeptCode = @.DeptCode OR @.DeptCode IS NULL)AND (ProjID =CaseWHEN @.DeptcodeISNOT NULLTHEN @.DeptCodeELSE @.ProjIDEND)--AND ((IF @.DeptCode IS NOT NULL ProjID = @.DeptCode + '-' + '%') ELSE ProjID = @.ProjID OR @.ProjID IS NULL)-- OR @.DeptCode IS NULL))AND (DateRequested = @.DateRequestedOR @.DateRequestedISNULL)AND (DueDate = @.DueDateOR @.DueDateISNULL)AND (ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)END
|||
Hi,
Thanks for the reply.
The code you posted doesn't seem to work. Now I get 0 rows returned when using any of the parameters.
|||
I did not set any value for @.n. You just mentioned "number" but didnt say what number? does it come from a lookup table/user? For example:
SET @.n = 4
right before the check for NULL on @.Deptcode.
|||
oh. What I meant by number was, any number using a wildcard. The only wildcard I know for sql is '%', that's why if you see my example code, I did the following
ProjID = @.DeptCode + '-' + '%'
I don't know if that would work, but it shows the general idea.
|||I dont understand..can you provide some sample parameters and how you expect the ProjID to turn out?|||
sure. Let's say I run the code given below:
DECLARE@.return_valueintEXEC@.return_value = [dbo].[GetProjectInfo]@.ProjectTitle =NULL,@.ProjectManagerID =NULL,@.DeptCode ='BAT',@.ProjID =NULL,@.DateRequested =NULL,@.DueDate =NULL,@.ProjectStatusID =NULLSELECT'Return Value' = @.return_valueGO
I would want that to return all records where the ProjID = BAT-*
That is, all records that have the word BAT as the first part of the ProjID.
C.P.Hardcastle wrote: