CODEWITHSUNDEEP
sql-serverstored-procedures
g_shockTan
g_shockTan

Reputation: 355

Pass multiple parameters in a stored procedure to a QueryString Parameter

How can pass two SQL parameters in a stored procedure to a query string in the WHERE clause? The issue I'm having is in the @iField. If I remove that and replace with InfoID LIKE '%' + @iSearch + '%', it works, but I need to add the @iField.

WHERE CustomerID = @CustomerID AND @iField LIKE '%' + @iSearch + '%'

Upvotes: 1

Views: 2974

Answers (3)

bummi
bummi

Reputation: 27385

As far as you are allready using a procedure

Declare @Query nvarchar(max)
Select @Query=
'
Select * from dbo.Invoice
WHERE CustomerID = '+Cast(@CustomerID as Varchar(20)) 
+' AND ['+ @iField +'] LIKE ''%' + @iSearch + '%''
'

--Print @Query
Exec(@Query)

Upvotes: 1

nathan_jr
nathan_jr

Reputation: 9292

If I understand correctly, you are simply trying to limit the like operator to a specific column?

declare @yourTable table (i int, FirstName varchar(10), LastName varchar(10))
insert into @yourTable
    select 1, 'john', 'doe' union all
    select 2, 'jane', 'doe';


declare @iField varchar(10),
        @iSearch varchar(10);


select  @iField = 'FirstName',
        @iSearch = 'j'


select  *
from    @yourTable
where   (@iField = 'FirstName' and FirstName like '%' + @iSearch + '%') or
        (@iField = 'LastName' and LastName like '%' + @iSearch + '%')

Upvotes: 0

Young Bob
Young Bob

Reputation: 743

T-SQL doesn't allow you to substiute variables for object names (it would be handy though, wouldnt' it). Two options really:

1) Use dynamic SQL e.g. sp_executesql (http://www.techrepublic.com/blog/datacenter/generate-dynamic-sql-statements-in-sql-server/306)

or

2) Use IF...ELSE e.g.

IF @iField = 'InfoId'
     SELECT ...
     WHERE CustomerID = @CustomerID AND InfoId LIKE '%' + @iSearch + '%'
ELSE IF @iField = '<some other field>'
     SELECT ...
     WHERE CustomerID = @CustomerID AND <some other field> LIKE '%' + @iSearch + '%'
etc.

Upvotes: 0

Related Questions