asked on

TSQL Syntax Error... with dynamic SQL

I get "ADO error: Line 40: Incorrect syntax near '@SQL1'. Incorrect syntax near the keyword ELSE.
It's at the If exists (@SQL1) line. I don't know how to do that with dynamically generated SQL. Any ideas?

ALTER PROCEDURE getlocalizedValue
@table varchar(255),
@joinKey varchar(255),
@col varchar(255),
@rowID int,
@localeID int,
@fallBackLocaleID int,
@showStaging int
AS

DECLARE @statusClause VARCHAR(255), @SQL1 VARCHAR(255), @SQL2 VARCHAR(255)

IF @showStaging = 1
      BEGIN
            SELECT @StatusClause = '.StatusID in (1,2)'
      END
ELSE
      BEGIN
            SELECT @StatusClause = '.StatusID = 1'
      END

SELECT @SQL1 = 'SELECT ' + @col
            + ' FROM ' + @table + ', ' + @table + 'Text'
            + ' WHERE ' + @table + '.' + @joinKey + ' = ' + @table + 'Text' + '.' + @joinKey
            + ' AND ' + @table + 'Text.' + @joinKey + ' = ' + CAST (@rowID AS VARCHAR(255))
            + ' AND ' + @table + 'Text.LocaleID' + ' = ' + CAST (@localeID AS VARCHAR(255))
            + ' AND ' + @table + @statusClause +
            + ' AND ' + @table + 'Text' + @statusClause

SELECT @SQL2 = 'SELECT ' + @col
            + ' FROM ' + @table + ', ' + @table + 'Text'
            + ' WHERE ' + @table + '.' + @joinKey + ' = ' + @table + 'Text' + '.' + @joinKey
            + ' AND ' + @table + 'Text.' + @joinKey + ' = ' + CAST (@rowID AS VARCHAR(255))
            + ' AND ' + @table + 'Text.LocaleID' + ' = ' + CAST (@fallBackLocaleID AS VARCHAR(255))
            + ' AND ' + @table + @statusClause +
            + ' AND ' + @table + 'Text' + @statusClause

IF EXISTS (@SQL1)
      EXEC(@SQL1)
ELSE
      EXEC(@SQL2)

pique_tech

I don't think you can use EXISTS that way.

What conditional are you testing to choose which SQL to execute? Whether EXEC(@SQL1) returns any records?

jbrahy

ASKER

yes, if @SQL1 doesn't return any records, I'd like to return the results from @SQL2

pique_tech

I think I'd try something like this. It seems to do what you want (with VERY limited testing).

DECLARE @C integer

EXEC(@SQL1)
SET @C = @@RowCount
IF @C = 0 THEN
EXEC(@SQL2)

jbrahy

ASKER

That kinda works, but it returns two result sets and I only want one.

jbrahy

ASKER

BTW,

EXEC(@SQL1)
IF @@RowCount = 0
EXEC(@SQL2)

is just as effective

pique_tech

How are you using the results? I note in your original posting, you got an ADO error. This suggests that you're using this somewhere in an application.

If that's the case, there is no real benefit to using a stored proc since you generate the SQL dynamically. It might be better to generate the SQL string right in your application code, use ADO to execute it, examine the results, and if there aren't any, execlute the other code via ADO.

jbrahy

ASKER

it has to be done in stored procedures, it's a requirement from a client.

ASKER CERTIFIED SOLUTION

pique_tech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial