Using ORDER BY in a stored procedure

[Rick Snagglehimer]

I keep getting an error from the following SP snippet

"Line 11: Incorrect syntax near 'BY'."

It runs fine without the ORDER BY clause.
[' + @user + ']
WHERE
[' + @cal + '].clientID = [' + @user1 + '].userID
AND [' + @cal + '].newID = ' + @userID + ' ORDER BY [' + @cal +
'].myAppointment'
EXEC (@sql)
GO



Any suggestions?

 

[Bob Barrows]

I keep getting an error from the following SP snippet

"Line 11: Incorrect syntax near 'BY'."

It runs fine without the ORDER BY clause.
[' + @user + ']
WHERE
[' + @cal + '].clientID = [' + @user1 + '].userID
AND [' + @cal + '].newID = ' + @userID + ' ORDER BY [' + @cal +
'].myAppointment'
EXEC (@sql)
GO



Any suggestions?

Put a "Print @sql" statement in there to verify that the statement contained
in @sql is correct.

Bob Barrows

 

[Rick Snagglehimer]

I keep getting an error from the following SP snippet

"Line 11: Incorrect syntax near 'BY'."

It runs fine without the ORDER BY clause.
[' + @user + ']
WHERE
[' + @cal + '].clientID = [' + @user1 + '].userID
AND [' + @cal + '].newID = ' + @userID + ' ORDER BY [' + @cal +
'].myAppointment'
EXEC (@sql)
GO



Any suggestions?

Put a "Print @sql" statement in there to verify that the statement contained
in @sql is correct.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Looks OK to me. Not sure what I'm missing here-

SELECT [wcdemo].ID,
[wcdemousers].one,
[wcdemousers].two,
[wcdemo].appointmentStart,
[wcdemo].appointmentEnd,
[wcdemo].ownerID
FROM [wcdemo],
[wcdemousers]
WHERE
[wcdemo].clientID = [wcdemousers].userID
AND [wcdemo].ownerID = 1 ORDER BY
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'BY'.
Stored Procedure: intra-fusedb.dbo.sp_CalendarNewAppointments
Return Code = 0




The actual statement-


CREATE PROCEDURE sp_CalendarNewAppointments

@userID varChar(255),
@users varChar(255),
@calendar varChar(255)

AS
DECLARE @sql varChar(255)

SELECT @sql = 'SELECT [' + @calendar +'].ID,
[' + @users + '].one,
[' + @users + '].two,
[' + @calendar + '].appointmentStart,
[' + @calendar + '].appointmentEnd,
[' + @calendar + '].ownerID
FROM [' + @calendar + '],
[' + @users + ']
WHERE
[' + @calendar + '].clientID = [' + @users + '].userID
AND [' + @calendar + '].ownerID = ' + @userID +
' ORDER BY [' + @calendar + '].appointmentStart'
Print @sql
EXEC (@sql)
GO

 

[Bob Barrows]

Looks OK to me. Not sure what I'm missing here-

SELECT [wcdemo].ID,
[wcdemousers].one,
[wcdemousers].two,
[wcdemo].appointmentStart,
[wcdemo].appointmentEnd,
[wcdemo].ownerID
FROM [wcdemo],
[wcdemousers]
WHERE
[wcdemo].clientID = [wcdemousers].userID
AND [wcdemo].ownerID = 1 ORDER BY
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'BY'.
Stored Procedure: intra-fusedb.dbo.sp_CalendarNewAppointments
Return Code = 0

I don't understand what you are saying. Since when is this:

SELECT ... ORDER BY

a valid T-SQL statement? You can't just tack on an "ORDER BY" without
including some columns in the ORDER BY clause ...

Obviously, something is causing the compiler to think the string is ended
after the word "BY" in your concatenation statement. Look closer and see if
you can see what it is ... (hint: look at the length you set for the @sql
variable <grin>)

The idea when creating a dynamic sql statement is to create a statement that
can be executed as-is, i.e., without modification. The best way to debug
what you've done is to use PRINT to print the contents of the string
variable, and then make sure you can copy and paste the result of the PRINT
statement to the Execute pane in QA and execute it without modification.

HTH,
Bob Barrows

PS. You should not be using the "sp_" prefix on your stored procedures.
"sp_" should be reserved for system stored procedures. There are performance
penalties for using the "sp_" prefix on your user-defined stored procedures.

 

[Rick Snagglehimer]

Looks OK to me. Not sure what I'm missing here-

SELECT [wcdemo].ID,
[wcdemousers].one,
[wcdemousers].two,
[wcdemo].appointmentStart,
[wcdemo].appointmentEnd,
[wcdemo].ownerID
FROM [wcdemo],
[wcdemousers]
WHERE
[wcdemo].clientID = [wcdemousers].userID
AND [wcdemo].ownerID = 1 ORDER BY
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'BY'.
Stored Procedure: intra-fusedb.dbo.sp_CalendarNewAppointments
Return Code = 0

I don't understand what you are saying. Since when is this:

SELECT ... ORDER BY

a valid T-SQL statement? You can't just tack on an "ORDER BY" without
including some columns in the ORDER BY clause ...

Obviously, something is causing the compiler to think the string is ended
after the word "BY" in your concatenation statement. Look closer and see if
you can see what it is ... (hint: look at the length you set for the @sql
variable <grin>)

The idea when creating a dynamic sql statement is to create a statement that
can be executed as-is, i.e., without modification. The best way to debug
what you've done is to use PRINT to print the contents of the string
variable, and then make sure you can copy and paste the result of the PRINT
statement to the Execute pane in QA and execute it without modification.

HTH,
Bob Barrows

PS. You should not be using the "sp_" prefix on your stored procedures.
"sp_" should be reserved for system stored procedures. There are performance
penalties for using the "sp_" prefix on your user-defined stored procedures.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks Bob!
<grin>