Using parameter on Query condition not working

[itsmenique]

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

Thanks
Dom

[iburyak]

Try this:

[PHP]Create procedure [dbo].[sp_searchFromCat]
(
@SearchCategory varchar(100) = null,
@SearchField varchar (100) = null
)
as
begin
Declare @SQL varchar(4000)

Select @SQL = '
select
BookTitle,
Author,
Status
from
LibBooks' +
Case when @SearchCategory is null then ''
else ' where ' @SearchCategory + ' like ''%' + isnull(@SearchField,'') + '%'''
end

Exec (@SQL)
end[/PHP]

[itsmenique]

Thanks iburyak!

You pin it down, I guess my second q is from the script you used, what's the use of this??

isnull(@SearchField,'')

Thanks again

Dom

[reon]

please check that coded area.............

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=    @SearchCategory like '%' + @SearchField + '%' 
 

-- you want to specify that which table data you are going to access.. i guessing that column name as searchcategory....
end

Thanks
Dom