Parsing a string In Stored Procedure 4

[jsnulf]

I need to pass a where clause to a stored proc using ADO. The where clause may contain any number of items.  The stored proc returns a single recordset. The clause is of the nature: "WHERE (AUTHOR='JONES' OR AUTHOR='SMITH') AND PUBLISHER="VIKING".  Must this be done by passing a string variable to the stored proc and then letting the stored proc parse out the string?  What is the best method for addressing this?  Thank you.

 

[Jerrycurl]

I assume that your where clause is not always made up of two authors and one publisher; the user is allowed any number (within reason) of combinations.&nbsp;&nbsp;In that case, your application is responsible for constructing the where clause.&nbsp;&nbsp;Your sproc will look something like this:<br><br>CREATE PROCEDURE sp_find_books @where_clause varchar(255) AS<br>&nbsp;&nbsp;exec(&quot; select * from books &quot; + @where_clause)<br><br>Your application would call this sproc and pass it the where_clause parameter.&nbsp;&nbsp;Note that if you are using a pre-7.0 version of SQL Server that your where_clause is limited to 255 characters.&nbsp;&nbsp;To save space, use &quot;WHERE AUTHOR IN ('JONES', 'SMITH')&quot; instead of &quot;WHERE (AUTHOR='JONES' OR AUTHOR='SMITH')&quot;. Also, make sure that @where_clause does not contain any double quotes.<br>You could also impose a limit of a certain number of authors and a certain number of publishers that the user is allowed to search, and have your sproc take each one as a seperate parameter.

 

[txkitten]

This was very helpful to me...since I have a similar problem. However, can this solution be used for only a part of a where clause? I have a large stored proc in which, depending on the selection made by the user the where clause looks for a different field. Plus I have to look at either a number or varchar, again depending on selection. Can this method be used in this instance?

Txkitten

 

[Jerrycurl]

I'm not quite sure I understand the question. Your where clause can be anything. Your application will include the logic to construct it based on what your user selects (e.g. surrounding text strings with quotes, but not numbers). You just have to make sure that the where clause is valid SQL.

 

[txkitten]

Here's what I mean...
If @SelectBy = 'Borrower'
BEGIN
SELECT punit,prenew, papply,
FROM property,
WHERE pstatus NOT IN ('X', 'T') AND
pbnumber in ( @param1, @param2, @param3)
End
If @SelectBy = 'Officer'
BEGIN
SELECT punit,prenew, papply,
FROM property,
WHERE pstatus NOT IN ('X', 'T') AND
bofficer in ( @param1, @param2, @param3)
END

~~~there are 2 more choices for @Selectby in my Stored Proc...but my question was is there a way to change just that one line of the Where clause for the different parameters used?
I'm not very good at explaining things in writing...but I hope I made it understandable enough.

TxKitten

 

[Jerrycurl]

If I understand correctly, your sproc would look like this:

BEGIN
exec(&quot;SELECT punit,prenew, papply,
FROM property,
WHERE pstatus NOT IN ('X', 'T') AND &quot; + @where_clause)
End

where your application generates a where clause like this:
&quot;pbnumber in ( 2, 44, 152)&quot; (or whatever your numbers are)

or

&quot;bofficer in ( 'whatever', 'something', 'ack')&quot; (or whatever your strings are)

You don't need the IF THEN because the only difference between the cases is in the where clause.

I don't think SQL Server allows you to pass paramater values directly into a IN statement, which is why you have to construct the where string and use EXEC.

 

[txkitten]

Well, I will give this a try....thank you for the input.
On a side note...the parameters in the IN clause DO work just fine. ...it's just annoyig to have to do 4 separate parts of this sproc, when the only real change is to the where clause. Again, thank you for your help...and I'll try and let you know how it comes out.
Have a great day!

TxKitten

 

[Jerrycurl]

This is what I meant by not being able to pass a parameter in a IN statement:

declare @string varchar(255)
set @string = &quot;12,53,22,55&quot;

select * from table1 where column1 in (@string)

 

[txkitten]

oh...sorry...I misunderstood. I got it now. I'm very new to this whole Forum thing. Sorry if I've bugged you too much. But thanks again for your help. I'm really hoping this is the solution I've been hunting for.

Take it easy.
TxKitten

 

[Frano]

If you are using SQL 2000, you can use a udf to process the parameter string.

Bradley Morris has submitted some code to do this on 'Planet Source Code', the URL is

http://www.planet-source-code.com/xq/ASP/txtCodeId.306/lngWid.5/qx/vb/scripts/ShowCode.htm

So using his code and your example :


declare @string varchar(255)
set @string = '12,53,22,55'

select * from table1 where column1 in
(SELECT Array_data FROM [dbo].[udf_ArrayToTable] (@string, ','))