If Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If Statement

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 11:10:18

I am trying to create a stored procedure that takes in a parameter but the user does not have to enter a parameter they are allowed to just leave it blank. If they leave it blank I just want it to select all records, but if they select the parameter I want it to only select the records equal to the paramter. I think I am setting up my if statement wrong but I can't find an example that will help. How does this look for syntax/formatting?

create procedure GetITTaskRpt (@TaskLookup5 varchar(30)) as

if @TaskLookup5 is null then
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks

if @TaskLookup5 is not null then
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where tasklookup5 = @TaskLookup5

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 11:12:55

Make NULL as default value of parameter and use like below

create procedure GetITTaskRpt 
(@TaskLookup5 varchar(30)=NULL) as


select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 OR @TaskLookup5 IS NULL)
GO

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 11:16:46

Thanks. I also just realized that in an if statement you don't need to put the "then"....my procedure worked when I pulled it out.

quote:
Originally posted by visakh16

Make NULL as default value of parameter and use like below
create procedure GetITTaskRpt
(@TaskLookup5 varchar(30)=NULL) as

select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 OR @TaskLookup5 IS NULL)
GO

Thanks in Advance!
Sherri

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-23 : 11:34:37

CREATE PROC GetITTaskRpt
(
@TaskLookup5 VARCHAR(30) = NULL
)
AS
BEGIN
SELECT
wo_num,
task,
opendate,
clsddate,
respons,
tasklookup5,
tasklookup6
FROM
tasks
WHERE
tasklookup5 = COALESCE(@TaskLookup5,tasklookup5)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources