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 + '%'
Add a comment
|
3 Answers
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)
2 Comments
g_shockTan
Thanks for your help. Using your suggestion I'm getting an
Invalid column name ''+ @iField +'' error.
bummi
@g_shockTan just copying my post I get results print will show e.g. Select * from dbo.Invoice WHERE CustomerID = 1 AND [BillNr] LIKE '%aus%' Try with print instead of Exec, might be missing quotation ...
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.
3 Comments
g_shockTan
Basically I would like to know if it's possible to write the WHERE clause as:
WHERE @iField LIKE '%' + @iSearch + '%'.
Young Bob
No you can't substitue column names, table names, procedure names etc. with variable names unless you build a sql string and execute it dynamically as in (1) above.
g_shockTan
Very interesting article. Thank you for the link.
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 + '%')
