Stored procedure parameter as descending

[Corinne]

Hi, Can anyone help me with the end if this stored procedure? I am trying to pass a parameter to the procedure to sort the results either ascending or descending:

CREATE PROCEDURE cusSP_Grid

@Sort as nvarchar(20)

AS

SELECT ID, BaseModel, StorisModel, CushionSet, Fabric, Type, Condition, Location, Reserved, OriginalScanBy, OriginalScanDate, EditScanBy, EditScanDate
FROM CusCushionInv
ORDER BY ID @Sort

GO

I'm interested in if this can be written like this? @Sort is a value I pass (ascending or descending) depending on the users selection. When I check my syntax in the stored procedure it tells me that I have a error: Incorrect syntax near '@Sort'

I know that I can have the users sort by clicking on the column heading but I would like to know how to pass the sort order as a parameter. Can anyone help?

Thanks,
Corinne

[techgnome]

Uhh... no. You can't do that.... BUT, what you CAN do is build a dynamic SQL statement that the SP executes....

CREATE PROCEDURE cusSP_Grid

@Sort as nvarchar(20)

AS
DECLARE @SQL varchar(8000)

SET @SQL = 'SELECT ID, BaseModel, StorisModel, CushionSet, Fabric, Type, Condition, Location, Reserved, OriginalScanBy, OriginalScanDate, EditScanBy, EditScanDate
FROM CusCushionInv
ORDER BY ID ' + @Sort

EXEC (@SQL) -- note the parens () are necessary....


GO