Case error - 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

SQL Server Development (2000)

Case error

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-03-21 : 06:27:05

I have the following sp:


create procedure proc_tmp
@tip varchar(50),@id int
as
set nocount on

select 
case @tip
	when 'k' then print 'kkk'
	when 'm' then print 'mmmm'
end

When I try to run it I'm having this error:

Incorrect syntax near the keyword 'print'.

What am I doing wrong?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-03-21 : 06:55:58

remove the Print statement's - you don't need them...

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-03-21 : 07:08:41

U can not SELECT PRINT '..'

------------------
If I am to do it,
I will, and NOW ..

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-21 : 08:07:02

might be worthwhile to re-do your procedure to include something like.....

if @tip = 'k' print 'kkk'
if @tip = 'm' print 'mmmm'

rather than a "case" statement inside a "select" statement.

Jay99

468 Posts

Posted - 2002-03-21 : 09:05:17

In case you haven't picked up on it by now, we can't determine if you are tying to assign a new value to @tip or print something based on the provided value of @tip . . .

for the former: Wanderer and Da_Retina
for the later: AndrewMurphy has got your back . .

Jay
<O>

kensai
Posting Yak Master

172 Posts

Posted - 2002-03-21 : 16:33:04

Wanderer the print statements are there just for trying. I'm gonna use sql code if I can get this work.

ok my bad sorry, I mistyped it.

ok, then what's wrong this one:


create procedure proc_tmp
@tip varchar(50),@id int
as
set nocount on

case @tip
	when 'k' then print 'kkk'	
	when 'm' then print 'mmmm'
end

The error:

Incorrect syntax near the keyword 'case'.

Edited by - kensai on 03/21/2002 16:36:11

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-21 : 17:00:15

In T-SQL, CASE returns an expression or value, it does not control the flow of execution (as SELECT...CASE does in VB). If you want to run 2 different pieces of code, you need to use IF...THEN instead:

create procedure proc_tmp @tip varchar(50), @id int as
set nocount on
IF @tip='k'
BEGIN
print 'kkk'
END
IF @tip='m'
BEGIN
print 'mmmm'
end

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 07:51:21

or...

create procedure proc_tmp
@tip varchar(50),
@id int
as
set nocount on
select case @tip
	when 'k' then 'kkk'
	when 'm' then 'mmmm' end
print @tip
go

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

leeholden
Starting Member

34 Posts

Posted - 2002-03-22 : 10:51:26

or

print case @tip
when 'k' then 'kkk'
when 'm' then 'mmm'
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources