> 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
>

> 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
>
> > 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
> >

> 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.Parameters.AddWithValue("@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.Parameters.AddWithValue("@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_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
>>>

> 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
>
> > 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.Parameters.AddWithValue("@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.Parameters.AddWithValue("@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_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
> >>>

> 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 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_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
>>
>>> 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.Parameters.AddWithValue("@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.Parameters.AddWithValue("@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_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
>>>>>