Using ORDER BY in a stored procedure

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
Sun Nov 02 09:33:44 CST 2003

Rick Snagglehimer wrote:
> 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"

Rick
Sun Nov 02 18:53:28 CST 2003


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eGo77bVoDHA.1656@tk2msftngp13.phx.gbl...
> Rick Snagglehimer wrote:
> > 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
Sun Nov 02 19:15:57 CST 2003

Rick Snagglehimer wrote:

> 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"

Rick
Sun Nov 02 19:34:10 CST 2003



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eROTRhaoDHA.360@TK2MSFTNGP12.phx.gbl...
> Rick Snagglehimer wrote:
>
> > 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>