asked on

use a case statement to compare variables in sql server 2000 stored procedure

I am creating a stored procedure that does the following:
1. create a cursor to hold a result set
2. loop through the recordset and set a variable value based on a value in the table using a case statement.

I'm have a problem with the case statement. I have posted my code below and any help really appreciated.

declare @fname varchar(30)
declare @lname varchar(50)
declare @title varchar(10)

declare @FLD_FNAME varchar(30)
declare @FLD_LNAME varchar(50)
declare @FLD_TITLE varchar(10)

declare @pos varchar(2)
declare @txt varchar(50)

set @FLD_FNAME = "1"
set @FLD_LNAME = "2"
set @FLD_TITLE = "3"

declare pdc cursor for
select position, text from test_table
open pdc
fetch next from pdc INTO @pos, @txt
while @@fetch_status = 0
begin
select case @pos
when (@FLD_FNAME) then set @fname = @txt
when (@FLD_LNAME) then set @lname = @txt
when (@FLD_TITLE) then set @title = @txt

fetch next from pdc INTO @pos, @txt_value
end

Ernariash

Hello, you have some issues with your case statement, case is missing the END and you are trying to set variables, and in you case you need to use IF....
if (@pos=@FLD_FNAME) set @fname = @txt
if (@pos=@FLD_LNAME) set @lname = @txt
if (@pos=@FLD_TITLE) set @title = @txt
Please try to avoid cursors if possible...:)

Ernariash

please see the case statement in the select if you really need to loop with cursors you can declare pdc cursor for
select key,
CASE WHEN position='1' THEN text ELSE NULL END Fname,
CASE WHEN position='2' THEN text ELSE NULL END Lname,
CASE WHEN position='3' THEN text ELSE NULL END Title
from test_table
group by key

Select 
	CASE WHEN  position='1' THEN  text ELSE NULL END Fname,
	CASE WHEN  position='2' THEN  text ELSE NULL END Lname, 
	CASE WHEN  position='3' THEN  text ELSE NULL END Title 
from test_table

Open in new window

james henderson

ASKER

thanks for the reply. I'm not sure I understand the first response. Also, I'm using a cursor because I'm returning multiple records and I need to set the value of a specific variable based on the current record.

The end result of looping through the recordset and setting these variables is to build a single string for storage in another table:
set @result = @fname + " " + @lname + " " + @title.
the @result variable will be stored in another table. The table I'm looping through holds one of the values per record, so record 1 has first name, record 2 has last name, etc.

Guy Hengel [angelIII / a3]

this "one-liner" will replace all the cursor code:

 select @fname = max(case position when 1 then text end)
      , @lname = max(case position when 2 then text end)
      , @title = max(case position when 3 then text end)
   from test_table

Open in new window

Ernariash

My first post the IF are the equivalent to yor intentions with the case @pos
when (@FLD_FNAME) then set @fname = @txt
when (@FLD_LNAME) then set @lname = @txt
when (@FLD_TITLE) then set @title = @txt
if (@pos=@FLD_FNAME) set @fname = @txt
if (@pos=@FLD_LNAME) set @lname = @txt
if (@pos=@FLD_TITLE) set @title = @txt
How many records you have in your table, do you have a key to get to the set @result = @fname + " " + @lname + " " + @title.
IF yes... then this should work :)
select key,
CASE WHEN position='1' THEN text ELSE ' ' END + " " +
CASE WHEN position='2' THEN text ELSE ' ' END + " " +
CASE WHEN position='3' THEN text ELSE ' ' END result
from test_table
group by key

ASKER CERTIFIED SOLUTION

Ernariash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

james henderson

ASKER

ok, a couple of things ....
first, I need to learn to type a little better ... save re-writing a lot!

second, using your case statements I was getting a warning about nulls in the aggregate function, until I added the else clause to each statement, after which it all worked like a champ. Thanks!!

I'm trying to figure a way to apply this to the rest of the stored procedure ... I have two outer loops prior to getting to the one you helped me with, and I'm not sure how to do it without cursors ... both do a simple "get next key" sort of thing which, in turn, is used to get records from another table, etc.

Ernariash

dhenderson12;
I am confident you could avoid the cursors, and please try to avoid using it. I have been doing sql dev and analysis for 10+ year pretty close to 100% without using cursors.. You can post your other queries with cursors on EE. It will help you in the future as samples.Thanks,