procedure with if statement ???? help

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 2000 Forums

Transact-SQL (2000)

procedure with if statement ???? help

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2005-12-14 : 17:30:22

I want to put an if statement into my procedure. But I am not doing it right
Create proc dbo.str_random_sample
@year varchar(7),
@period varchar(8)
as
CREATE TABLE #mySample
(
[lname] [varchar] (20),
[fname] [varchar] (15),
[empid] [varchar] (6),
[item] [varchar] (4),
[pay_location] [varchar] (4) ,
[sub] [varchar] (1) ,
[bureau] [varchar] (4) ,
[division] [varchar] (4) ,
[eval_type] [varchar] (25) ,
[begindate] [datetime] ,
[enddate] [datetime] ,
[bname] [char] (60) ,
[dname] [char] (60))

INSERT into #mySample (lname,fname,empid,item,pay_location,sub,bureau,
division,eval_type,begindate,enddate,bname,dname)
select top 200 e.lname,e.fname,e.empid,e.item,e.pay_location,e.sub,e.bureau,e.division,d.eval_type,
convert(varchar,d.fromdate,101) as begindate, convert(varchar,d.todate,101) as enddate,
o.org_name as bname, n.org_name as dname
from emp_information_test e,due_evals d,new_organization o,new_organization n
WHERE e.item Not in (0845,1004,1060,2574,8008,8013,8014,8015,8016,8017,8019,8023,8645,9421)
and e.deptno <> '999'
and e.empid = d.empid
and d.eval_type <> 'Probation'
and e.bureau is not null
and e.bureau = o.org_id
and o.type = 'BUREAU'
and e.division = n.org_id
and n.type = 'DIVISION'

-- this statement is not correct can I not do something like this
if @Year = "" then
and datepart(yyyy,d.datetobefiled ) = @year
and e.empid LIKE @period
else
and datetobefiled between '07/15/2004' and '08/15/2005'
end if
order by NEWID()
select * from #mysample order by dname,bname

cfederl
Starting Member

26 Posts

Posted - 2005-12-14 : 20:00:30

you are defining @year as varchar(7), but it needs to be a 4 digit numeric or integer for this to work correctly

AND ( ( @Year > 0 and datepart(yyyy,d.datetobefiled ) = @year and e.empid LIKE @period) )
OR ( ( @Year <= 0 or @Year is null ) and datetobefiled between
cast('2004-07-15' as datetime) and cast('2005-08-15' as datetime ))

Carl Federl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 20:27:48

you can't use IF .. THEN .. ELSE .. END IF within a select statement.
change to something like this

and (
    (    @Year = "" 
    and  datepart(yyyy,d.datetobefiled ) = @year
    and  e.empid LIKE @period
    )
   or
   (    @Year <> "" 
   and  datetobefiled between '07/15/2004' and '08/15/2005'
    )
)

-----------------
[KH]
Learn something new everyday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-15 : 02:23:11

or use it as seperate statements

if @Year = "" then
---Insert statement with some condition
else
---Insert statement with some other condition

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources