Dynamic Order By w/ additional parameters

I have a stored procedure that uses a dynamic order by statement. This statement works okay until I try to select ticket's by userEmail which is passed in to my stored procedure as a parameter. Here is the code that works for my dynamic sort order:

CREATE PROCEDURE [dbo].[SelectAllTickets]

@.SortOrder varchar(250)

AS

SET NOCOUNT ON

Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID,

b.typeID, b.typeName,

c.userID, c.UserFirstName,

f.statusID, f.statusName

FROM Tickets a

LEFT OUTER JOIN Type b ON b.typeID = a.typeID

LEFT OUTER JOIN Users c ON c.userID = a.userID

LEFT OUTER JOIN Status f ON f.statusID = a.statusID

ORDER BY ' + @.SortOrder)

I modied this procedure to create one in which I select tickets based on the userEmail as a criteria as well.. this one fails due to Incorrect syntax near the keyword 'BY'

CREATE PROCEDURE [dbo].[SelectTicketByUser]
@.SortOrder varchar(250),
@.userEmail varchar(50)

AS
SET NOCOUNT ON
Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID,
b.typeID, b.typeName,
c.userID, c.UserFirstName, c.userEmail,
f.statusID, f.statusName
FROM Tickets a
LEFT OUTER JOIN Type b ON b.typeID = a.typeID
LEFT OUTER JOIN Users c ON c.userID = a.userID
LEFT OUTER JOIN Status f ON f.statusID = a.statusID
WHERE a.statusID <> 40 AND c.userEmail = ' + @.userEmail +
'ORDER BY ' + @.SortOrder)

Any ideas on what syntax I should be using? Thanks!

make sure there is a space before and after each parameter:

WHERE a.statusID <> 40 AND c.userEmail = ' + @.userEmail +
' ORDER BY ' + @.SortOrder)

Notice the spaces before and after ORDER BY above.|||

As mentioned above, you need a space before the word ORDER. The second issue is that while you are concatinating strings together to execute, you forgot that your string literals need to be enclosed in quotes.

CREATE PROCEDURE [dbo].[SelectTicketByUser]
@.SortOrder varchar(250),
@.userEmail varchar(50)

AS
SET NOCOUNT ON
Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID,
b.typeID, b.typeName,
c.userID, c.UserFirstName, c.userEmail,
f.statusID, f.statusName
FROM Tickets a
LEFT OUTER JOIN Type b ON b.typeID = a.typeID
LEFT OUTER JOIN Users c ON c.userID = a.userID
LEFT OUTER JOIN Status f ON f.statusID = a.statusID
WHERE a.statusID <> 40 AND c.userEmail = ' + '''' + @.userEmail + '''' +
' ORDER BY ' + @.SortOrder)

|||Thanks for all the help.. worked like a charm!

zoop