Easy(?) SQL Stored Procedure Question

[Chad A. Beckner]

Hey all,

I currently have a stored procedure that accepts 1 parameter, and passes
the rowcount out. Using ASP.NET/SQL, how can I create a function/stored
procedure to do the following:

1. form my stored procedure, how can I pass in "dynamic" parameters
without having to declare them in the actual stored procedure?
example: ID, Status_ID, Active - or - just Status_ID, Active
2. How can I setup (using a generic function that I have to create) add
these parameters to the command object?
example: I pass in "ID,Status_ID,Active" - or - just
"Status_ID,Active"
3. Should I just use ADHOC queries for this? I have always seen
articles and documentation stating that stored procedures are faster...

Thanks everyone!

Chad

 

[William \(Bill\) Vaughn]

Stored procedures are faster--but not always. Their query plan is compiled
based on the parameters passed to it.
Stored procedures have a fixed set of parameters but you can provide default
values for any or all of them. One approach you might consider is creating
several stored procedures that accept the parameters you need to pass. Each
of these SPs sets up a call to one or more other stored procedures.
Sure, ad hoc queries can be easier, but be sure to use Command objects to
manage the parameters. This helps prevent sql injection attacks and manages
many of the parameter formatting issues you'll encounter.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________