asked on

Int and VarChar types in where clause

hi,

I have the below Stored proc which passes in the field name and values to filter on. It works great. However, some of the columns are of type int and others are varchar. The varchar columns need the values in quotes, but the int columns do not. Therefore the below only works for varchars.

Is there any easy fix? Thanks

CREATE PROC test
@columName  varchar(30),
@value  varchar(30)
AS
BEGIN
    EXECUTE(
    'SELECT     D.id_desk,
                D.id_region           
    FROM        DESK D,
                INSTRUMENT I
    WHERE '      + @columName + ' LIKE ''' + @value +'''
    AND         I.id_desk = D.id_desk' )
END
go

Open in new window

ASKER CERTIFIED SOLUTION

IncisiveOne

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IncisiveOne

Er, that's for discussion/progress, obviously the code will not work without the execute.

SOLUTION

alpmoon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alpmoon

Have you tried it? Is it useful for your end?

alpmoon

I think my solution is more flexible and efficient. If table structure is changed, the procedure doesn't need to be modified.

I suggest split between me and IncisiveOne

alpmoon

I think my solution is more flexible and efficient. If table structure is changed, the procedure doesn't need to be modified.

I suggest split between me and IncisiveOne