Parameter.Add() problem

  • Thread starter Thread starter Timothy V
  • Start date Start date
T

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.
 
Hi Timothy,

as you wrote it it is normally only

SqlCommand.Parameter.Add("@SortBy", yourfield)

Would be enough, setting it befor the execute of the command of course.

I hope this helps?

Cor
 
Hey Timothy,
Cor's previous post tells you how to add a parameter to you command
object from the .NET end, you're trying to use the param in special way in
the stored proc. In your particular case you can't use the param to the
sproc in the 'order by' clause the way that you're doing it (that has
nothing to do with your param on the .NET end, that just the way transact
Sql works). This is how you accomplish what you're trying to do (or at least
an example of how to)

Create procedure sp_something
@sortBy varchar(50)
As

Declare
@sqlStatement varchar(100)

set @sqlStatement = 'Select * from Customers ORDER BY ' + @sortBy

exec (@sqlStatement)

So, replace the necessary fields to suit your needs, and that should do the
trick.
I should say though that this method of doing things defeats the pre
compiled aspect of the stored procedure.

Hope this helps.

Cordell Lawrence [[email protected]]
Teleios Systems Ltd. [www.teleios-systems.com]
 
Thank you! That's the answer I was looking for.

Thank you again.


Cordell Lawrence said:
Hey Timothy,
Cor's previous post tells you how to add a parameter to you command
object from the .NET end, you're trying to use the param in special way in
the stored proc. In your particular case you can't use the param to the
sproc in the 'order by' clause the way that you're doing it (that has
nothing to do with your param on the .NET end, that just the way transact
Sql works). This is how you accomplish what you're trying to do (or at
least
an example of how to)

Create procedure sp_something
@sortBy varchar(50)
As

Declare
@sqlStatement varchar(100)

set @sqlStatement = 'Select * from Customers ORDER BY ' + @sortBy

exec (@sqlStatement)

So, replace the necessary fields to suit your needs, and that should do
the
trick.
I should say though that this method of doing things defeats the pre
compiled aspect of the stored procedure.

Hope this helps.

Cordell Lawrence [[email protected]]
Teleios Systems Ltd. [www.teleios-systems.com]

Timothy V said:
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.
Click to expand...
Click to expand...
 
Back
Top