Incorrect syntax near the keyword 'DECLARE' in SQL Server

Question

I'm using this SQL code:

DECLARE
@vals AS varchar(MAX),
@q  AS varchar(MAX)

select @vals = STUFF((SELECT distinct ',' + QUOTENAME(chgnum) 
        from pppview_nl
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(MAX)'),1,1,'')

set @q = 'SELECT lotdetid,' + @vals + ' FROM 
                        (
                SELECT TOP 50 lotdetid, LineValue as val,chgnum FROM pppview_nl ORDER BY lotdetid DESC
                        ) a
                        pivot 
                        (
                            max(val)
                            for chgnum in (' + @vals + ')
                        ) b'
execute(@q)

Which works fine, and changes:

https://i.stack.imgur.com/yPd9a.png

INTO:

https://i.stack.imgur.com/vDOgI.png

It runs fine on Navicat and SQL Fiddle, but when I try and save it, I get this issue: [SQL Server]Incorrect syntax near the keyword 'DECLARE'

I've done quite a bit of reading and far from redoing the entire thing, how can this issue be fixed? I read something somewhere about needing to but all declares inside a ~~whatever it was~~ but then other people are seeming saying it doesn't make a difference.

Hope someone can assist me in this, as it's a bit of a head-scratcher how it works fine but doesn't save.

Regards, Oscar.

Edit:

ALTER PROCEDURE [dbo].[o_getvals]
    @lotdetit varchar(6)
AS
BEGIN
    DECLARE
    @vals AS varchar(MAX),
    @q  AS varchar(MAX)
    select @vals = STUFF((SELECT distinct ',' + QUOTENAME(chgnum) 
            from pppview_nl
            ORDER BY chgnum desc
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(MAX)'),1,1,'')

    set @q = 'SELECT lotdetid,' + @vals + ' FROM 
                            (
                                    SELECT TOP 50 lotdetid, LineValue as val,chgnum FROM pppview_nl WHERE lotdetid = ' + @lotdetit + ' ORDER BY lotdetid DESC
                            ) a
                            pivot 
                            (
                                max(val)
                                for chgnum in (' + @vals + ')
                            ) b'
    execute(@q)
END

Stored proceedure did the trick, thanks jyparask and everyone else. :)

what do you mean by "when I try and save it"? Saving it as a stored procedure, view? — Aziz Shaikh, Nov 20 '13 at 13:08
I'm totally not sure. But shouldn't your declare and with a delimiter (eg `;`) just before you start your select ? — nl-x, Nov 20 '13 at 13:09
You need to create a stored procedure. I don't think a VIEW can handle this. — Giannis Paraskevopoulos, Nov 20 '13 at 13:11
Local variables are not allowed in a VIEW. You may read [SQL Views - no variables?](http://stackoverflow.com/questions/6114826/sql-views-no-variables) and [How do I “Declare the scalar variable” in a VIEW in Sql Server (2005)](http://stackoverflow.com/questions/3475712/how-do-i-declare-the-scalar-variable-in-a-view-in-sql-server-2005). — Aziz Shaikh, Nov 20 '13 at 13:20
Views have to have a "shape" that is fixed - the number and type of columns, together with their names. Your query produces a variable number of columns, so it will not work. — Damien_The_Unbeliever, Nov 20 '13 at 13:33

Giannis Paraskevopoulos · Accepted Answer · 2013-11-20T13:35:15.707

1

For this piece of work you should create a stored procedure.

edited Nov 20 '13 at 13:35

answered Nov 20 '13 at 13:16

Giannis Paraskevopoulos

18,261
1
49
69

1

table-valued can't use `exec` either. – Damien_The_Unbeliever Nov 20 '13 at 13:32

score 0 · Answer 2 · answered Nov 19 '14 at 17:47

I cam across this issue 'invalid syntax near'. After a brief investigation I discovered that it's due to conflict between by Function declaration parameters and the use of the parameters in my query i.e. declaring int variables as varchar in my Function signature.

Let me know if this solves the problem.......

Incorrect syntax near the keyword 'DECLARE' in SQL Server

2 Answers2