Query parameters - Tricky Stored Procedure Question

[ric_deez]

Hi there, I would like to create a simple search form to allow users to
search for a job number based on a number of parameters. I think I
understand how to use parameteres associated with Stored Procedures
with a data reader to add various parameters.

However, if I have a stored procedure such as

CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype, @par3
datatype)

AS

SELECT FROM Jobs WHERE
PAR1 = @par1 AND
PAR2 = @par2 AND
PAR3 = @par3

I cannot just pass the one parameter to the procedure. The question
is then: how is it possible to create a stored procedure which can
accept any number of parameters an then smartly modify the query in the
store procedure with CASE statements or similar?

I don't really want to do this as in-line code as it easily becomes
unmaintainable and it is hard to add additional conditions. I was
thinking of passing a string array into the Stored Procedure and having
the stored procedure parse through the array, determine the parameters
and do the grunt of the work. The problem is that I haven't got a lot
of experience with these advanced stored procedures using CASE
statements and the like...


Any ideas would be greatly appreciated.


Regards,

Ric

 

[GhostInAK]

Hello ric_deez,

This is one case in which an inline ad-hoc query would be preferable I do
believe. Done right it is extremely maintainable. TSQL, while up to the
task, is not designed for such a query and would indeed be the less maintainable
solution.

Put a layer of abstraction between the UI and the query. This abstraction
layer would take as inputs the name of the field to search as well as the
value to search for. It would then give you a hashtable or some similar
construct which you could then loop over to construct your query.

Dont forget that ad-hoc queries can still take advantage of the SqlParameter
object (eg. SELECT field1 FROM table1 WHERE (field1 = @field1) -- tCommand.Parameters.AddWithValue("@field1",
value1) )

Enjoy,
-Boo

 

[ric_deez]

Hi there Boo,

Can you please explain how to use the sqlParameter with ad-hoc queries?

Thanks!

Ric

 

[GhostInAK]

Hello ric_deez,

I thought I had. However, if by explain you mean write you the entire code..
*sigh* fine.. but only because I don't hate you, yet. Thias is off the
top of my head...

Dim tConnection As SqlConnection = New SqlConnection(connection_string_here)
Dim tCommand As SqlCommand = New SqlCommand
Dim tAdapter As SqlDataAdapter = New SqlDataAdapter(tCommand)
Dim tTable as DataTable = New DataTable

With tCommand
.Connection = tConnection
.CommandType = CommandType.Text
.ComandText = "SELECT field1 FROM table1 WHERE (field1 = @value1)"
' Note: Here you would pass in your constructed ad-hoc query
.Parameters.AddWithValue("@value1", value1) ' See, the parameter
name corresponds to the parameter name in the query.. spiffy huh..
End With

tConnection.Open
tAdapter.Fill(tTable)
tConnection.Close
' And now you got a shiney new DataTable to show your friends

tAdapter = Nothing
tCommand = Nothing
tConnection = Nothing

-Boo

 

[ric_deez]

Hi Boo,

Thanks for your help on this. I didn't actually mean for you to write
the entire code but I do appreciate you taking the time to do so as it
has answered my question. I was wondering if you had to specify the
CommandType as CommandType.Text, which you have now confirmed.

This is pretty nifty, thanks!!!

Ric

 

[GhostInAK]

Hello ric_deez,

The default value for SqlCommand.CommandType is indeed CommandType.Text,
however, I prefer to set this property as I think it makes the code more
readable.

-Boo