[thelist] mssql stored procedure-using a variable for 'AND' and'OR'

Jay Turley jayturley at gmail.com
Mon Aug 25 17:29:06 CDT 2008


Here's an example of a short one I used. The search phrase isn't
parsed, and yes, if you are passing in a comma-separated list of
values, you will have to do some string parsing unless someone smarter
than me knows a better way.

Basically you are going to loop over your list of values and then each
time you come to a comma, add the last value to the current sql as an
AND or OR clause, which will be something simple like

IF @comparison = 'and'
     @sql = @sql + ' AND [column_name] = '' + @phrase + '''
ELSE IF @comparison = 'or'
     @sql = @sql + ' OR [column_name] = '' + @phrase + '''

--- sample procedure below ---

CREATE PROCEDURE NotesSearch

	@phrase VARCHAR(100)
	, at search_contacts BIT = 1
	, at search_properties BIT = 1
	, at search_deals BIT = 1
	, at search_exchanges BIT = 1
	, at user_id INT

AS

BEGIN

    DECLARE @sql NVARCHAR(4000)
			, at params NVARCHAR(100)

    SELECT @sql = N'SELECT n1.note_id,LEFT(n1.note,100) AS
note,u1.user_id,u1.fname + '' '' + u1.lname as
created_by_user,n1.creation_date,n1.changed_date,n1.property_id,n1.contact_id,n1.deal_id,n1.exchange_id'
    SELECT @sql = @sql + N' FROM enotes n1 LEFT JOIN eusers u1'
    SELECT @sql = @sql + N' ON n1.created_by_user_id = u1.user_id'
    SELECT @sql = @sql + N' WHERE n1.note LIKE ''%'' + @phrase + ''%'''
    IF @search_contacts = 0
    	SELECT @sql = @sql + N' AND n1.contact_id IS NULL'
    IF @search_properties = 0
    	SELECT @sql = @sql + N' AND n1.property_id IS NULL'
    IF @search_deals = 0
    	SELECT @sql = @sql + N' AND n1.deal_id IS NULL'
    IF @search_exchanges = 0
    	SELECT @sql = @sql + N' AND n1.exchange_id IS NULL'
    SELECT @sql = @sql + N' AND
(dbo.IsShared(@user_id,n1.team_sharing) = 1 OR n1.created_by_user_id =
@user_id)'

    SELECT @params = ''
    SELECT @params = @params + N'@user_id INT'
    SELECT @params = @params + N', at phrase VARCHAR(100)'

    EXEC sp_executesql @sql, @params, @user_id, @phrase

END

GO


On Mon, Aug 25, 2008 at 3:12 PM, Joel D Canfield <joel at bizba6.com> wrote:
> I'm back . . .
>
>> comma-separated-values in as a varchar, and then parsed it in the SQL
>> stored procedure.
>
> parsed it, how? my searches led me to some fairly convoluted SQL, which
> I hope isn't the answer ;)
>
>> In addition, I passed in some flags to say how the CSVs
>> should be handled.
>
> I don't see in your example where we're setting whether the select is OR
> or AND-is that what you mean here?
>
> much thankage, honest
>
> joel
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list