MSSQL - Stored Procedure Error - Incorrect syntax near the keyword 'CASE'.

Hi guys 'n gals,

I have a problem with my CASE statement in my stored procedure, could somebody help me please?

My error:

Incorrect syntax near the keyword 'CASE'.
Incorrect syntax near the keyword 'END'.

My stored procedure:

CREATE PROCEDURE [sp_Test]
@column varchar(32),
@value varchar(64)
AS
BEGIN
CASE @column
WHEN 'tmp'
THEN DELETE FROM tblTemp WHERE UID=@value
END
END


Any idea on what I need to change?


Cheers!
LVL 4
Cyber-DrugsAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
CREATE PROCEDURE [sp_Test]
@column varchar(32),
@value varchar(64)
AS
BEGIN
if  @column = 'tmp'
   DELETE FROM tblTemp WHERE UID=@value

END

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Cyber-Drugs,

CREATE PROCEDURE [sp_Test]
@column varchar(32),
@value varchar(64)
AS
BEGIN
if @column =  'tmp'
THEN DELETE FROM tblTemp WHERE UID=@value
END
END



Aneesh R
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to use 'IF' to do this.Please not that never start the user procedures with 'sp_', which will hurt the performance
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Cyber-DrugsAuthor Commented:
Cheers aneeshattingal, I didn't think of using an If statement. :o)
0
 
Cyber-DrugsAuthor Commented:
aneeshattingal,

I never knew starting with 'sp_' hurt performance? What should I use? I want to make use of some prefix, can I make one up, eg 'pr_' ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
u can use any meaningful names otherthan 'sp_', sp_ means System Procedures. if you use 'sp_' for ur procedures, any call to the proc, wil check first in the master db and if some proc is there with the same name, it will execute that sp, instead of this one.
0
All Courses

From novice to tech pro — start learning today.