Update Records in SQL Server where Logical Condition is inside the Parameter Supplied by the User

Question

Is there a way that I can Update records from the database where the logical condition for example is supplied by the user in the parameter?

I want to update the salesperson where invoice date for example is greater than "11/13/17". But in this case the user will be the one supplying the logical operator on the parameter. I create a stored procedure with the following code:

UPDATE inv_hdr_mst
    SET slsman = @SalesPerson
    WHERE cust_num = @Customer AND **inv_date = @Invoice**  

Here is the copy of my stored procedure:

ALTER PROCEDURE TMA_UpdateSalesPersonSp

    -- Add the parameters for the stored procedure here

    @Invoice InvNumType,
    @Salesperson SlsmanType,
    @Customer   CustNumType

AS

BEGIN

    DECLARE @upd_sql nvarchar(1000)

    BEGIN

        set @upd_sql = 
            '
                 UPDATE inv_hdr_mst_all
                  SET slsman = ''' + @salesperson + '''
                  WHERE cust_num = ''' + @customer + ''' AND inv_date ' + @invoice + '
             '

            print @upd_sql



    END

exec (@upd_sql);



END

Answer 1

Dynamic SQL will allow you to have the user specify the logical operator at run-time. The @invoice variable will do the trick.

declare @invoice nvarchar(1000)
    , @salesperson nvarchar(100)
    , @customer nvarchar(100)
    , @upd_sql nvarchar(1000)

set @invoice = '> ''11/3/2017'''
set @customer = 'STARTR'
set @salesperson = 'MAY'

set @upd_sql = 
    '
        UPDATE inv_hdr_mst
        SET slsman = ''' + @salesperson + '''
        WHERE cust_num = ''' + @customer + ''' AND inv_date ' + @invoice + ' 
    '

print @upd_sql

exec (@upd_sql);