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_SelectfromJ obNumbers (@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.Parame ters.AddWithVal ue("@field1",
value1) )

Enjoy,
-Boo

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_SelectfromJ obNumbers (@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

[ric_deez]

Hi there Boo,

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

>>Dont forget that ad-hoc queries can still take advantage of the SqlParameter
SELECT field1 FROM table1 WHERE (field1 = @field1) -- tCommand.Parame ters.AddWithVal ue("@field1",
value1) )

Thanks!

Ric

GhostInAK wrote:

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.Parame ters.AddWithVal ue("@field1",
value1) )

Enjoy,
-Boo

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_SelectfromJ obNumbers (@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,

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(c onnection_strin g_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.Tex t
.ComandText = "SELECT field1 FROM table1 WHERE (field1 = @value1)"
' Note: Here you would pass in your constructed ad-hoc query
.Parameters.Add WithValue("@val ue1", value1) ' See, the parameter
name corresponds to the parameter name in the query.. spiffy huh..
End With

tConnection.Ope n
tAdapter.Fill(t Table)
tConnection.Clo se
' And now you got a shiney new DataTable to show your friends

tAdapter = Nothing
tCommand = Nothing
tConnection = Nothing

-Boo

Hi there Boo,

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

>>Dont forget that ad-hoc queries can still take advantage of the
SqlParamete r
SELECT field1 FROM table1 WHERE (field1 = @field1) --
tCommand.Para meters.AddWithV alue("@field1",
value1) )

Thanks!

Ric

GhostInAK wrote:

>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.Param eters.AddWithVa lue("@field1",
value1) )
Enjoy,
-Boo

>>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_SelectfromJ obNumbers (@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
unmaintainabl e 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

[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.Tex t, which you have now confirmed.

This is pretty nifty, thanks!!!

Ric
GhostInAK wrote:

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(c onnection_strin g_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.Tex t
.ComandText = "SELECT field1 FROM table1 WHERE (field1 = @value1)"
' Note: Here you would pass in your constructed ad-hoc query
.Parameters.Add WithValue("@val ue1", value1) ' See, the parameter
name corresponds to the parameter name in the query.. spiffy huh..
End With

tConnection.Ope n
tAdapter.Fill(t Table)
tConnection.Clo se
' And now you got a shiney new DataTable to show your friends

tAdapter = Nothing
tCommand = Nothing
tConnection = Nothing

-Boo

Hi there Boo,

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

>Dont forget that ad-hoc queries can still take advantage of the
SqlParameter
SELECT field1 FROM table1 WHERE (field1 = @field1) --
tCommand.Param eters.AddWithVa lue("@field1",
value1) )

Thanks!

Ric

GhostInAK wrote:

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.Parame ters.AddWithVal ue("@field1",
value1) )
Enjoy,
-Boo
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_SelectfromJ obNumbers (@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
unmaintainab le 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,

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

-Boo

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.Tex t, which you have now confirmed.

This is pretty nifty, thanks!!!

Ric

GhostInAK wrote:

>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_stri ng_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.Tex t
.ComandText = "SELECT field1 FROM table1 WHERE (field1 = @value1)"
' Note: Here you would pass in your constructed ad-hoc query
.Parameters.Ad dWithValue("@va lue1", value1) ' See, the
parameter
name corresponds to the parameter name in the query.. spiffy huh..
End With
tConnection.Op en
tAdapter.Fill( tTable)
tConnection.Cl ose
' And now you got a shiney new DataTable to show your friends
tAdapter = Nothing
tCommand = Nothing
tConnection = Nothing
-Boo

>>Hi there Boo,

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

Dont forget that ad-hoc queries can still take advantage of the
SqlParamete r
SELECT field1 FROM table1 WHERE (field1 = @field1) --
tCommand.Pa rameters.AddWit hValue("@field1 ",
value1) )
Thanks!

Ric

GhostInAK wrote:

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
maintainab le
solution.
Put a layer of abstraction between the UI and the query. This
abstractio n 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
SqlParamet er
object (eg. SELECT field1 FROM table1 WHERE (field1 = @field1)
--
tCommand.Par ameters.AddWith Value("@field1" ,
value1) )
Enjoy,
-Boo
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
Procedure s with a data reader to add various parameters.
>
However, if I have a stored procedure such as
>
CREATE usp_SelectfromJ obNumbers (@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
unmaintaina ble 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
parameter s and do the grunt of the work. The problem is that I
haven't got a lot of experience with these advanced stored
procedure s using CASE statements and the like...
Any ideas would be greatly appreciated.
>
Regards,
>
Ric
>