Syntax error in Stored Procedure while trying to execute

Question

I have written this stored procedure for one my operations in the database, but it is prompting with two errors. I am fairly new top this, so it would be great if somebody could correct the mistake.

CREATE PROCEDURE findVersions 
  @seg nvarchar(255),
  @str nvarchar(255)
AS

  DECLARE @UnsegQuery AS nvarchar(255)

  SET @UnsegQuery = SELECT DISTINCT UnsegmQuery 
                      FROM tbData 
                     WHERE SegQuery = @seg 

  SELECT TOP 1 Strategy, Versions, CGNum 
    FROM tbData 
   WHERE Strategy = @str 
     AND SegQuery = @seg 
ORDER BY CGnum DESC
  UNION
  SELECT TOP 1 Strategy, Versions 
    FROM tbData 
   WHERE Strategy = 'BF' 
     AND UnsegmQuery = @UnsegQuery
  UNION
  SELECT Strategy, Versions 
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY nDCG DESC) AS rownumber 
            FROM tbData) AS foo 
   WHERE rownumber > 1

Errors:

Msg 156, Level 15, State 1, Procedure findVersions, Line 10
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure findVersions, Line 13
Incorrect syntax near the keyword 'UNION'

Any suggestions?

Update Eg: of what I have to do with the queries. I have to display the first result from the first query, first result from the second query and then the remaining three from the first query results.

1st class: (has 4 student)

Tom (Has highest score)
Rex (Hss second highest score)
Rambo (HAs 3rd highest score)
Betty (Has least score)

2nd class: (has 1 student)

Spooky (Has the highest score)

Required result order in DataControl:

Tom
Spooky
Rex
Rambo
Betty

This doesn't look complete - `@UnsegQuery` is declared & set, but never used. The third query in the UNION is incorrect as a stand-alone. Break the `UNION` up, and gradually add to determine what your issues are. — OMG Ponies, Mar 01 '12 at 05:08
Try putting parenthese around the subquery (after `SET @UnsegQuery =`). — John Pick, Mar 01 '12 at 05:09
@JohnPick: See [TSQL: SET vs SELECT](http://stackoverflow.com/questions/3945361/t-sql-set-verus-select-when-assigning-variables) — OMG Ponies, Mar 01 '12 at 05:09

rsbarro · Accepted Answer · 2012-03-01T05:43:29.900

2

To address the "Incorrect syntax near the keyword 'SELECT'" error, this statement needs to be written using parenthesis around the SELECT:

SET @UnsegQuery = (SELECT DISTINCT UnsegmQuery FROM tbData WHERE SegQuery = @seg)

If you use SET to set the value of @UnsegQuery, you also need to be certain that the SELECT will only return one value. Alternatively, you can use:

SELECT DISTINCT @UnsegQuery = UnsegmQuery FROM tbData WHERE SegQuery = @seg

to set the value of @UnsegQuery. In this case, if more than one record is returned @UnsegQuery will be set to the value of the last record.

The "Incorrect syntax near the keyword 'UNION'" error is occurring because you can't use ORDER BY before a UNION. You can only use an ORDER BY after the last UNION statement (see the MSDN documentation for more info).

UPDATE
To answer your question from your last comment, the correct syntax for the last part of the query should look something like this:

SELECT foo.Strategy, foo.Versions 
FROM (
    SELECT Strategy, Versions, ROW_NUMBER() OVER (ORDER BY nDCG DESC) AS [rownumber] 
    FROM tbData) foo 
WHERE foo.rownumber > 1

That statement will select all records from tbData ordered by nDCG descending except the first record. I'm not sure that helps you solve the problem, but the syntax is correct.

UPDATE 2

OK, I think I understand the problem. You want to select all of the rows from the table, but you want one specific record to be first, a different specific record to be second, and then all the rest. One approach to doing this would be to use a CASE WHEN statement to assign values to the desired first row, desired second row, and then sort by that value. For example:

DECLARE @myTable TABLE([ID] INT, [Student] VARCHAR(10))
INSERT INTO @myTable VALUES(1, 'Tom')
INSERT INTO @myTable VALUES(2, 'Spooky')
INSERT INTO @myTable VALUES(3, 'Rex')
INSERT INTO @myTable VALUES(4, 'Rambo')
INSERT INTO @myTable VALUES(5, 'Betty')
DECLARE @firstID INT, @secondID INT
SET @firstID = 2
SET @secondID = 4

SELECT * 
FROM @myTable
ORDER BY
    CASE 
        WHEN [ID] = @firstID THEN 1 
        WHEN [ID] = @secondID THEN 2
        ELSE 3 
    END,
    [ID]

edited Mar 01 '12 at 05:43

answered Mar 01 '12 at 05:09

rsbarro

27,021
9
71
75

The `SET` works now, but can you recommed a way for a way around to the second error? What should I do to retrieve the top most result in the first query then? – user1240679 Mar 01 '12 at 05:16
If I remove the `ORDER BY` from the first query, strangely it starts showing: Errors for the last query: `Invalid column name 'Strategy'. Invalid column name 'QuotedVersions'.` – user1240679 Mar 01 '12 at 05:19
Updated the questions and added an example for what I am trying to do – user1240679 Mar 01 '12 at 05:22
Update the code with `@UnsegQuery`. This is being used in the second query being `Union`ed – user1240679 Mar 01 '12 at 05:24
Yes, CGNum is a column in the table, as in the update question – user1240679 Mar 01 '12 at 05:35
Please see the update to my answer (now that I think I understand the problem). Does that help? – rsbarro Mar 01 '12 at 05:44
`Update` solved one of the problems. `Update 2` is not actually applicable in my case, because I cannot currently create a new column of insertion for all the rows that I have (500k rows in DB). But, yes, the you got the problem correct that I want to have a certain record first, a certain record second, and all others afterwards. If somehow `Order by` could be used in my first `select` query to get the first record, then the problem looks solved. – user1240679 Mar 01 '12 at 05:57
I certainly could be wrong, but I don't think it's going to work with UNION. You can only order once the UNION is complete. Furthermore, if you don't order the UNION, I'm pretty sure there's no guarantee that the records will be returned in the same order that the UNION takes place. Update 2 doesn't require you to create a new column, it just requires you to sort on a calculated value. All you need to do is figure out what the criteria is to pick the first and second records. I just used a temp table to illustrate the pattern. Perhaps someone else out there has a better approach. – rsbarro Mar 01 '12 at 06:01

Syntax error in Stored Procedure while trying to execute

1 Answers1