Using the CASE syntax with GOTO

  • 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

  • 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"

  • 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

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

    Exec @SQL

    rather than

    Exec (@SQL)

    Are you doing it with brackets?...

  • Solved it as follows:

    EXEC sp_executesql @SQL

    This will execute the user defined function.

    Thanks for your input DavidT.

    Hawkeye67

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

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