CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
user1240679
user1240679

Reputation: 6979

Syntax error in Stored Procedure while trying to execute

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

Upvotes: 1

Views: 2642

Answers (1)

rsbarro
rsbarro

Reputation: 27339

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]

Upvotes: 2

Related Questions