Using the CASE syntax with GOTO

Question

Post reply

Using the CASE syntax with GOTO

PaladinTech

Mr or Mrs. 500

Points: 577
More actions
August 7, 2003 at 8:50 am

#176304

Here is the problem: The following case statement throws the error - Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'GOTO'.
SELECT CASE WHEN @name = 'John' THEN GOTO John ELSE SELECT 'OOPS' END
John is a label to which I want to direct the program if the variable @name (a varchar(6)) contains 'John' (this is only an example) but the Analyzer objects. Any reason why GOTO cannot be used in the THEN clause of a CASE WHEN ... THEN ... statement?
Hawkeye67

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DavidT SSCommitted Points: 1727 More actions · Answer 1

As far as I know, you cannot use a goto in a select statement.

As you are not selecting data from a table, try just using an if statement:

If @name = "John"

goto John

else

select "oops"

PaladinTech Mr or Mrs. 500 Points: 577 More actions · Answer 2

Okay. So, now, I have created a separate scalar function returning a varchar(300) and in the calling procedure created a dynamic sql statement that is supposed to execute the scalar function (@SQL = N'SELECT dbo.myscalarfunction'), but when I use EXEC @sql it returns the error message "Cannot find stored procedure "myscalarfunction". I thought you could use EXEC to execute a dynamic sql statement that accessed a user defined function in SQL Server 2000?

BTW, the value of the dynamic sql statement comes from a CASE WHEN ... THEN ... arrangment and all the syntax appears to be correct.

Why doesn't the command EXEC work?

Hawkeye67

DavidT SSCommitted Points: 1727 More actions · Answer 3

That sounds just like an error you get if you call:

Exec @sql

rather than

Exec (@SQL)

Are you doing it with brackets?...

PaladinTech Mr or Mrs. 500 Points: 577 More actions · Answer 4

Solved it as follows:

EXEC sp_executesql @sql

This will execute the user defined function.

Thanks for your input DavidT.

Hawkeye67