Handling a SELECT in a stored procedure

[stormydaniels]

Hi, I have a stored proc which retrieves a value from my database. If I select the value I then want to do a case statement and return a string. This is my code but I can't get it to work:

create procedure procGetEventType
(@EventID int, @EventHeading varchar(12))
as
declare@desc varchar(50)
select @desc = 'Unknown event type'

begin
select [EventID], [EventHeading]
from tblEvents
where [EventID] = @EventID

if ([EventHeading] = 'Info') desc = 'Information'
if ([EventHeading] = 'Warn') desc = 'Warning'
if ([EventHeading] = 'Error') desc = 'Error'

return @desc
end

I can't even create the stored procedure because I get a syntax error. It complains about EventHeading being an invalid column heading and when I comment out the "if" lines it's OK. If I have done a select on a column can I not then refer to it later on? Any ideas? :confused:

[Wendelius]

Few issues: - define variables where you place the result from SELECT - don't use parenthesis on parameters if creating a procedure but do use them if creating a function - procedure cannot return a value, use output parameter or create a function. Most likely you want to use a function for the logic you described. In that case it would be something like:

create function GetEventType (@EventID int)
returns varchar(12)
AS
declare @desc varchar(50)
declare @eventHeading varchar(12)
begin
select @eventHeading = [EventHeading]
from tblEvents
where [EventID] = @EventID

set @desc = 'Unknown event type'
if (@eventHeading = 'Info') set @desc = 'Information'
if (@eventHeading = 'Warn') set @desc = 'Warning'
if (@eventHeading = 'Error') set @desc = 'Error'

return (@desc)
end

The need to optimize rises from a bad design. My articles[^]

[stormydaniels]

Thanks for such a carefully created answer - that's solved my problem perfectly and more importantly, helped me understand the issue I had. Thanks again :cool:

[Wendelius]

You're welcome :)

The need to optimize rises from a bad design. My articles[^]