August 7, 2003 at 8:50 am
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
August 7, 2003 at 8:58 am
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"
August 7, 2003 at 10:20 am
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
August 7, 2003 at 10:29 am
That sounds just like an error you get if you call:
Exec @SQL
rather than
Exec (@SQL)
Are you doing it with brackets?...
August 7, 2003 at 11:12 am
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