[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