[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1629
  • Last Modified:

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
0
dhenderson12
Asked:
dhenderson12
  • 5
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
ErnariashCommented:
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now