Parameter.Add() problem

[Timothy V]

Hi,
I was looking at this method within the SqlCommand class. It is the
SqlCommand.Parameter.Add(string, object) method.

My question is, how do I use this parameter within a stored procedure?

What i am really trying to do is dynamically change the ORDER BY method
within the SPROC. For example (i know this won't work):
CREATE PROCEDURE dbo.ClassFindAll
(
@sortBy VARCHAR(50)
)
AS
SELECT *
FROM Classes
ORDER BY @sortBy

Can anyone help?

Thank you,

Tim.

[Nicholas Paldino [.NET/C# MVP]]

Timothy,

SQL won't allow you to do this. If you need it to be truly
configurable, then you would have to call sp_exec, creating the SQL string,
and then making the call. However if you choose to do that, then all of the
benefits of stored procedures fly out the window. You would be better off
just creating a SQL command and executing that dynamically.

However, if you know that you will only sort on certain combinations of
fields, then what I would do is create an enumeration representing the
different orders you want to perform, and then have a conditional statement
in the stored procedure that would execute based on the value you pass in.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Timothy V" <tr****@msn.com> wrote in message
news:uW**************@tk2msftngp13.phx.gbl...

Hi,
I was looking at this method within the SqlCommand class. It is the
SqlCommand.Parameter.Add(string, object) method.

My question is, how do I use this parameter within a stored procedure?

What i am really trying to do is dynamically change the ORDER BY method
within the SPROC. For example (i know this won't work):
CREATE PROCEDURE dbo.ClassFindAll
(
@sortBy VARCHAR(50)
)
AS
SELECT *
FROM Classes
ORDER BY @sortBy

Can anyone help?

Thank you,

Tim.