You can't dynamically build the SQL statements..<BR><BR><BR><BR>*unless*<BR><BR>You use the EXEC function:<BR><BR>CREATE PROCEDURE spSomeThing<BR>@OrderBy nvarchar(30)<BR><BR>AS<BR><BR>EXEC ('SELECT * FROM MyTable ORDER BY ' + @OrderBy)<BR><BR>You have to build a string and execute it with the EXEC function. <BR><BR>WARNING! you loose some performance since dynamic SQL statements are not pre-compiled and their execution plan is not stored.
Thanks for your response - <BR><BR>One follow up question: you said I can't dynamically build the sql statements. But, isn't that what I'm doing when I specify a where clause parameter, or a select parameter? What makes the order by parameter so different that it returns an error?
actually you gave the answer. When adding a field to the where clause:<BR><BR>SELECT * FROM Employees WHERE EmpID = @EmpID<BR><BR>you supply a *parameter* to the query! That is possible.<BR><BR>But when you add something to the actual SQL statement (such as an order by column) the compiler chokes because he doesn't recognize the SQL statement, that really is something different than adding a parameter.