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
dhenderson12Asked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
You are going to get an error with the group by ...I am missing the max

select key,
 MAX(CASE WHEN  position='1' THEN  text else null END)  + " " + 
 MAX(CASE WHEN  position='2' THEN  text else null END)  + " " + 
 MAX(CASE WHEN  position='3' THEN  text else null END) as result 
from test_table
group by key  

Open in new window

0
 
ErnariashCommented:
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...:)
0
 
ErnariashCommented:
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

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
dhenderson12Author Commented:
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.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
ErnariashCommented:
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  
0
 
dhenderson12Author Commented:
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.
0
 
ErnariashCommented:

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,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.