How would I write this stored procedure...

[nc297]

create procedure GETNames
@rpt char(1)
as

if rpt = '1', '3', '4', '5'

then

SELECT FirstName, LastName, JobTitle, Department
FROM HumanResources
WHERE rpt=@rpt

else if rpt='2'

SELECT FirstName, LastName, JobTitle, Department
EmployeeDepartment
WHERE rpt='2'

end if

I want rpt='2' to have it's own select statement as different data needs to appear.

 

[gmmastros]

I would probably write it like this:

create procedure GETNames
@rpt char(1)
as

if rpt [!]In([/!]'1', '3', '4', '5'[!])[/!]

  SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources
    WHERE rpt=@rpt

else if rpt='2'

  SELECT FirstName, LastName, JobTitle, Department
    EmployeeDepartment
    WHERE rpt='2'

Although, it's probably better this way:

create procedure GETNames
@rpt char(1)
as

if rpt = '2'
  SELECT FirstName, LastName, JobTitle, Department
    EmployeeDepartment
    WHERE rpt='2'
Else

  SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources
    WHERE rpt=@rpt

In the first example, if you add another report type, you would have to modify the code. In the second example, you wouldn't (unless you also wanted special handling for it).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thanks I will use the second procedure as rpt='2' is the only report that will have special handling

I will try it in the morning thanks!

 

[SimonSellick]

Don't forget the missing '@' on line 5. Gets me every time.

 

[nc297]

Thanks this worked but I'm stuck on another problem so I opened a new thread.