CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012dynamic-sql
Johan Hjalmarsson
Johan Hjalmarsson

Reputation: 3493

incorrect syntax error and object/column name missing

I'm making a storet procedure that basicly just does 1 dynamic query with the result from another dynamic query as alias for 1 column. However, I get the following two errors (id doesn't say where):

  1. Incorrect syntax near '='.
  2. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

This is my code:

    ALTER PROCEDURE myProcedure
        @param1 NVARCHAR(50),
        @param2 NVARCHAR(50),
        @param3 int
    AS
    BEGIN
        DECLARE @query Nvarchar(2000)
        DECLARE @columnName Nvarchar(250)
        DECLARE @columnNameOUT Nvarchar(250)
        set @columnName = ''
        SELECT @query = 'SELECT (@columnNameOUT = CONVERT(time(0), TimeStamps."timeStamp", 108)) AS alias FROM TimeStamps WHERE [param3] = ''' + CAST(@param3 AS VARCHAR) + ''' AND param2 = ''' + @param2 + ''' AND [param1] = ''' + @param1 +''''
        EXECUTE sp_executesql @query, N'@columnNameOUT NVARCHAR(30) OUTPUT', @columnNameOUT = @columnName OUTPUT
        set @query = ''
        IF @param1 IS NULL
        BEGIN
            SELECT @query = 'SELECT column1 AS '''+@columnName+''', column2, column3 ETA FROM myTable WHERE [param3] = '''+CAST(@param3 AS VARCHAR) +''' AND param2 = '''+@param2+''' AND [param1] IS NULL order by column1'
        END
        ELSE
        BEGIN
            SELECT @query = 'SELECT column1 AS '''+@columnName+''', column2, column3 ETA FROM myTable WHERE [param3] = '''+CAST(@param3 AS VARCHAR) +''' AND param2 = '''+@param2+''' AND [param1] ) '''+@param1+''' order by column1'
        END
        EXEC(@query);
    END

Upvotes: 0

Views: 9731

Answers (2)

Johan Hjalmarsson
Johan Hjalmarsson

Reputation: 3493

Finally solved it myself!

Turns out that this little query:

SELECT @query = 'SELECT (@columnNameOUT = CONVERT(time(0), TimeStamps."timeStamp", 108)) AS alias FROM TimeStamps WHERE [param3] = ''' + CAST(@param3 AS VARCHAR) + ''' AND param2 = ''' + @param2 + ''' AND [param1] = ''' + @param1 +''''

Had a chance of returning empty. So after adding a IF @columnName = '' everything worked out.

And changing this line:

... 'SELECT (@columnNameOUT = CONVERT(time(0), TimeStamps."timeStamp", 108)) AS alias FROM ...

To this:

...'SELECT @columnNameOUT = CONVERT(time(0), TimeStamps."timeStamp", 108) FROM...

Made the other error disappear too :)

Upvotes: 0

Maxim Zhukov
Maxim Zhukov

Reputation: 10140

First of all, there is a problem with bracket: 

' AND [param1] ) '

Your second problem was in ELSE statement (there is a solution):

    BEGIN
        SELECT @query = 'SELECT column1, column2, column3 ETA 
        FROM myTable 
        WHERE [param3] = '''+CAST(@param3 AS VARCHAR) +''' 
        AND [param2] = '''+@param2+''' 
        AND [param1] = '''+@param1+''' order by column1';
    END

But the first problem looks like here, but don't know how to fix it:

SELECT @query = 'SELECT (@columnNameOUT = CONVERT(time(0), TimeStamps."timeStamp", 108)) AS alias

Hope it could help you

Upvotes: 1

Related Questions