Just in Case - SQL Server Forums

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)

Just in Case

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-17 : 04:16:35

I hate SQL Case statments, I find the syntax clumsy and horrible, is it just me?

I'm writing a sproc that will execute a line of code that is dependent on one of the input parameters. What I have is

Create Procedure dbo.GetNextID

@IDType varchar(30), -- holds the type of ID required
@NextID int OUTPUT -- holds the new ID or NULL if timeout

-- This is the generic get an ID function 
as

DECLARE @SQLid varchar(255) -- holds the text for the SQL command
SET @SQLid = 
 case @IDType         
   WHEN  'clinic'   THEN 'SELECT TOP 1 SMS_CLIN_CONTACT_LOCK from PARAMETER ORDER BY SMS_CLIN_CONTACT_LOCK DESC'
   WHEN  'patient'  THEN 'SELECT TOP 1 SMS_PTNO_LOCK from PARAMETER ORDER BY SMS_PTNO_LOCK DESC'
   WHEN  'pedigree' THEN 'SELECT TOP 1 SMS_PEDIGREENO_LOCK from PARAMETER ORDER BY SMS_PEDIGREENO_LOCK DESC'
   WHEN  'dna'      then 'SELECT TOP 1 SMS_DNA_LABNO_LOCK from PARAMETER ORDER BY SMS_DNA_LABNO_LOCK DESC'
   ELSE   'SELECT NULL'

  END
...

What I am trying to do is set SQLid to be a string that can then be executed but when I try to do this I get an error that the stored procedure 'SELECT TOP 1 ...' Does not exist

I tried to rewrite it but just got myself confused. Any suggestions would be gratefully received

thanks

steve

Steve no function beer well without

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-17 : 04:30:03

Just athought Steve, but how are you executing the command? I've had that error before when I tried something like EXEC @SQLid instead of EXEC (@SQLid).

Raymond

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-17 : 04:32:44

The sql case command is not that bad.
I also get it confused with the vb one.

Try This
[CODE]
SET @SQLid =
case
WHEN @IDType = 'clinic' THEN 'SELECT TOP 1 SMS_CLIN_CONTACT_LOCK from PARAMETER ORDER BY SMS_CLIN_CONTACT_LOCK DESC'
WHEN @IDType = 'patient' THEN 'SELECT TOP 1 SMS_PTNO_LOCK from PARAMETER ORDER BY SMS_PTNO_LOCK DESC'
WHEN @IDType = 'pedigree' THEN 'SELECT TOP 1 SMS_PEDIGREENO_LOCK from PARAMETER ORDER BY SMS_PEDIGREENO_LOCK DESC'
WHEN @IDType = 'dna' then 'SELECT TOP 1 SMS_DNA_LABNO_LOCK from PARAMETER ORDER BY SMS_DNA_LABNO_LOCK DESC'
ELSE 'SELECT NULL'

END
[/CODE]

Duane.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-09-17 : 04:35:03

If this is a method of getting a new unique id to be used as a primary key it is not good. Use identity instead.

Back to your query

There is no need for dynamic sql


set @nextID = 
 case @IDType         
   WHEN  'clinic'   THEN (SELECT TOP 1 SMS_CLIN_CONTACT_LOCK from PARAMETER ORDER BY SMS_CLIN_CONTACT_LOCK DESC)
   WHEN  'patient'  THEN (SELECT TOP 1 SMS_PTNO_LOCK from PARAMETER ORDER BY SMS_PTNO_LOCK DESC)
   WHEN  'pedigree' THEN (SELECT TOP 1 SMS_PEDIGREENO_LOCK from PARAMETER ORDER BY SMS_PEDIGREENO_LOCK DESC)
   WHEN  'dna'      then (SELECT TOP 1 SMS_DNA_LABNO_LOCK from PARAMETER ORDER BY SMS_DNA_LABNO_LOCK DESC)
 end

The cause of yuor error is probably that you do


exec @SQLid

instead of


exec (@SQLid)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 04:36:47

Do You really need the dynamic sql stuff,
could you not do it in an easier way ?

Create Procedure dbo.GetNextID

@IDType varchar(30), -- holds the type of ID required
@NextID int OUTPUT -- holds the new ID or NULL if timeout

-- This is the generic get an ID function 
AS

IF @IDType = 'clinic'
BEGIN
	SET @NextID = -- ( SELECT MAX(SMS_CLIN_CONTACT_LOCK) + 1 from PARAMETER ) or whatever
END

ELSE IF @IDType = 'patient'
BEGIN
	SET @NextID = ... do whatever to get this next id
END

....

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-17 : 05:30:18

Thanks for the comments guys they have really helped but I haven't quite cracked it yet.

Duane, I'm trying what you suggested as that makes a little more sense to me in terms of the syntax.

Lars you are absolutely right about indentity but unfortunately the guy who wrote this system doesn't understand the concept. In fact he has had to 'invent' field locking in order to handle muliple users which is why this sproc is so long (I left a lot out to prevent any death through sheer boredom)

Raymond and Lars you were right about my Exec statements so I have fixed them now but I think they are still the problem. Further on in the sproc I have the line

EXEC @TMP = @SQLID

which is trying to put the value returned from the SQL above (note it is a single value only) into a variable @tmp. Do I need brackets somewhere? What if I was passing parameters e.g.

EXEC @Variable = dbo.SomeProc @param1, Param2

According to BOL my syntax looks right (I think!!!)

In case it makes any difference my target server is SQL Server 7

Steve no function beer well without

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 05:45:05

quote:
EXEC @TMP = @SQLID

Sorry for not being constructive, but I think you are in a mess

check out this classic on dynamic sql... if you haven't already.
[url]http://www.sommarskog.se/dynamic_sql.html[/url]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-17 : 06:20:15

OK I'll take a look at that, but as you can see from [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39409[/url]
the syntax that you are questioning works in some cases

steve

Steve no function beer well without

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 06:27:28

Not on my Sql Server it doesn't

i can't do that kind of dynamic sql

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-17 : 06:30:34

Ah, now I'm even more confused. Anyone got any clues? Shall we just wait til Monday when all may be miraculously clear?

looks like the beers are on you :)

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 07:44:04

ok lets get case jiggy:


set @NextID = 
(select TOP 1 
	case 
	   WHEN  @IDType = 'clinic'   THEN convert(varchar(8000), SMS_CLIN_CONTACT_LOCK)
	   WHEN  @IDType = 'patient'  THEN convert(varchar(8000), SMS_PTNO_LOCK)
	   WHEN  @IDType = 'pedigree' THEN convert(varchar(8000), SMS_PEDIGREENO_LOCK)
	   WHEN  @IDType = 'dna'      then convert(varchar(8000), SMS_DNA_LABNO_LOCK)
	   ELSE   null
	END
from 	PARAMETER
order by 1 desc 
)

you don't need Convert(varchar(8000),...) if all the columns are of same type.

hope it helps

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-17 : 09:09:47

I think a complete re-write is in order :(

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 09:22:46

so this works for you???

hey what's going on with your saga??
i laughed my ass off...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources