Using a case statement to set a variable?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using a case statement to set a variable??

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-10-07 : 06:13:10

Hi Reader!

I currently have the below case statement:


		SELECT 
     		  CASE Score1
         		WHEN '1' THEN 'Integer'
			WHEN '2' THEN 'Currency'
			WHEN '3' THEN 'Date'
         		ELSE 'Not Used'
      	END
  	 	FROM tblScoreDescription WHERE ScoreDescriptionID = @ScoreDescriptionID

What I want to do is set a variable depending on the value of score1. Something like the below:


		SELECT 
     		  CASE Score1
         		WHEN '1' THEN SET @SQL = @SQL + ' (dbo.tblKPIData.KPINumber01) as Result'
			WHEN '2' THEN SET @SQL = @SQL + ' (dbo.tblKPIData.KPICurrency01) as Result'
			WHEN '3' THEN SET @SQL = @SQL + ' (dbo.tblKPIData.KPIDate01) as Result'
         		ELSE 'Not Used'
      	END
  	 	FROM tblScoreDescription WHERE ScoreDescriptionID = @ScoreDescriptionID

Is there any way I can do this?

Thanks.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-07 : 07:05:53

[code]
SELECT
@SQL = CASE Score1
WHEN '1' THEN @SQL + ' (dbo.tblKPIData.KPINumber01) as Result'
WHEN '2' THEN @SQL + ' (dbo.tblKPIData.KPICurrency01) as Result'
WHEN '3' THEN @SQL + ' (dbo.tblKPIData.KPIDate01) as Result'
ELSE 'Not Used'
END
FROM tblScoreDescription WHERE ScoreDescriptionID = @ScoreDescriptionID

[/code]

Jay White
{0}

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-10-07 : 07:41:13

Thanks! Worked first time. Your a star :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-07 : 08:04:09

Is it really easier to log onto the internet, find this website, log in, go to the forums, post a question, wait for the result, check them out, copy and paste, adapt it for your code, and then see if it works .... rather than just trying it and finding out yourself ?

just wondering ... people seem to do this all the time.

"What happens if I type SELECT * FROM MyTable ? Can anyone help me ? "

- Jeff

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-10-07 : 08:07:46

I tried a couple of things but had no luck. Got to finish it by today, so needed the answer pretty sharpish.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-07 : 08:58:34

actually, i thought he just re-posted your code but now I see the difference ... sorry 'bout that.

- Jeff

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-10-07 : 09:01:48

You what?

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 11:29:07

better hope that @ScoreDescriptionID is a PK or unique index...

otherwise you're getting 1 out of potentially many values...

Jeff...come on...

How do I make this work?

DECALRE @x varchar(8000)
SELECT @x = 'a,b,c'
SELECT * FROM myTable WHERE col1 = @x

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources