Dynamic order by and JOIN

Hi,
Here is my code:
ALTER PROCEDURE dbo.sp_GetPeopleDetails_1
@.OrderByClause varchar(100)
AS
DECLARE @.SQLStatement varchar(255)
SELECT @.SQLStatement = 'SELECT People.PeopleID, People.FirstLastName, People.Title,
Departments.AcademicArea, Shifts.ShiftName, People.TShirt,
People.ParkingFROM Departments INNER JOIN
People ONDepartments.DepartmentID = People.DepartmentID
INNER JOINShifts ON People.ShiftID = Shifts.ShiftID
order By ' +@.OrderByClause
EXEC(@.SQLStatement)
/* SET NOCOUNT ON */
RETURN
When I run it, the error is: "Incorrect syntax near the keyword 'IN'."
Can anyone point my mistake?
Thanks.

Could the 'IN' be in the @.OrderByClause text?

Brian

|||Does this happen with any orderbyclause or just a particular one?|||

run the query in the Query analyzer passing the order by clause and do a PRINT(@.SQLStatement) to see how its building up.

On another note you should not be prefixing your stored proc with "SP_". It causes performance degradation as the sql server will first look under master database assuming its a system proc and then not finding there will look under the database you ware working with.

|||

Finally, problem solved, when I run it in Query analyzer itshowed a truncated query, very strange as I have had it set to varchar(255). Ijust increased it to 500 even though the query does not have more 255characters.
Thanks for the tip with the sp_

Dynamic name of result column in s-proc?

How can I make this stored procedure concept work:

---------------
Create Procedure MyProc @.MyValue varchar(5)
As
Declare @.ColumnName as varchar(11)
Set @.ColumnName = 'Price_' + @.MyValue
Select Sum(Price) As @.ColumnName --??
From MyTable where Item = @.MyValue
Return
---------------

Using @.MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?

BjornUSE Northwind
GO

DECLARE @.ColumnName sysname, @.sql varchar(8000), @.myValue varchar(5)
SELECT @.myValue = '23'
SET @.ColumnName = 'Price_' + @.MyValue
SELECT @.sql = 'SELECT Sum(UnitPrice) As ' + @.ColumnName
+ ' FROM [Order Details] '
+ ' WHERE ProductID = ' + @.MyValue

SELECT @.sql

EXEC(@.sql)|||Thank you thank you!

Bjornsql