Using parameter on Query condition not working
-
Hi Guru's I created a stored procedure which on a straight query it will appear like this select BookTitle, Author, Status from LibBooks where Author like '%Buck%' On my SP I created two @parameters to have flexibility on my query, @SearchCategory - column name (Author) @SearchField - text to be searched (Buck) Problem: I noticed that my query does'nt return result, its it possible to use @parameter as substitute to column name on a condition? Something like this: select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%' SP that I created Create procedure [dbo].[sp_searchFromCat] ( @SearchCategory varchar(100) = null, @SearchField varchar (100) = null ) as begin select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%' end
-
Hi Guru's I created a stored procedure which on a straight query it will appear like this select BookTitle, Author, Status from LibBooks where Author like '%Buck%' On my SP I created two @parameters to have flexibility on my query, @SearchCategory - column name (Author) @SearchField - text to be searched (Buck) Problem: I noticed that my query does'nt return result, its it possible to use @parameter as substitute to column name on a condition? Something like this: select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%' SP that I created Create procedure [dbo].[sp_searchFromCat] ( @SearchCategory varchar(100) = null, @SearchField varchar (100) = null ) as begin select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%' end
The easiest way to make the column in the where clause dynamic is to build the query in the procedure then execute the sql code. example from article: Article on dynamic sql [^] use Northwind go declare @RECCNT int declare @ORDID varchar(10) declare @CMD Nvarchar(100) set @ORDID = 10436 SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + ' where OrderId < @ORDERID' print @CMD exec sp_executesql @CMD, N'@RECORDCNT int out, @ORDERID int', @RECCNT out, @ORDID print 'The number of records that have an OrderId' + ' greater than ' + @ORDID + ' is ' + cast(@RECCNT as char(5)) Another idea is if you only have a couple possible columns you could do something like this Select * From table where column1 = isnull(@Column1Value, column1) and column2 = isnull(@Column2Value, column2) This might not perform well on a large table
-
Hi Guru's I created a stored procedure which on a straight query it will appear like this select BookTitle, Author, Status from LibBooks where Author like '%Buck%' On my SP I created two @parameters to have flexibility on my query, @SearchCategory - column name (Author) @SearchField - text to be searched (Buck) Problem: I noticed that my query does'nt return result, its it possible to use @parameter as substitute to column name on a condition? Something like this: select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%' SP that I created Create procedure [dbo].[sp_searchFromCat] ( @SearchCategory varchar(100) = null, @SearchField varchar (100) = null ) as begin select BookTitle, Author, Status from LibBooks where @SearchCategory like '%' + @SearchField + '%' end
-
The easiest way to make the column in the where clause dynamic is to build the query in the procedure then execute the sql code. example from article: Article on dynamic sql [^] use Northwind go declare @RECCNT int declare @ORDID varchar(10) declare @CMD Nvarchar(100) set @ORDID = 10436 SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + ' where OrderId < @ORDERID' print @CMD exec sp_executesql @CMD, N'@RECORDCNT int out, @ORDERID int', @RECCNT out, @ORDID print 'The number of records that have an OrderId' + ' greater than ' + @ORDID + ' is ' + cast(@RECCNT as char(5)) Another idea is if you only have a couple possible columns you could do something like this Select * From table where column1 = isnull(@Column1Value, column1) and column2 = isnull(@Column2Value, column2) This might not perform well on a large table
Hi, I guess I'll go to the second suggestion you gave, my brain got tangled after reading the first one. checking on this Thanks
-
Hi Gautham, Just got the query working, thanks! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[sp_searchFromCat] ( @SearchCategory varchar(100) = null, @SearchField varchar (100) = null ) as begin declare @SQL varchar(4000) set @SQL = 'select BookTitle, Author, Status from LibBooks ' + case when @SearchCategory is null then '' else 'where ' + @SearchCategory + ' like ' + '''%' + isnull(@SearchField,'') + '%''' end exec(@SQL) end Dom