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
3 1732
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]
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jakob |
last post by:
Hey...
Why do I sometimes experience low performance when I use a parameter instead
of an exact value?
For example the following performs very bad:
|
by: Gregory A Greenman |
last post by:
I'm trying to write a program in vb.net to automate filling out a
series of forms on a website. There are three forms I need to
fill out in sequence. The first one is urlencoded. My program is...
|
by: Todd |
last post by:
Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in...
|
by: Nicolae Fieraru |
last post by:
Hi All,
I want to find if there is a different way than the way I am working now.
Lets say I have a table, tblCustomers containing address details.
I want a report with all the customers from...
|
by: Yodel99 |
last post by:
Greetings,
I have a form (Form1) based on a parameter query (Query1). I want the
value that the user enters in the parameter window to populate a field
in the form. I've been able to use the...
|
by: StBond |
last post by:
Hi everyone,
I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.
I am having a little problem with the database that I am...
|
by: vncntj |
last post by:
I have this syntax working perfectly, but I'm trying to apply a stored
procedure instead using
the CommandText (Sql Statement).
// Open connection to the database
string ConnectionString =...
|
by: rbg |
last post by:
I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table,...
|
by: karanj |
last post by:
Hi all,
I'm facing an issue with Access that I haven't been able to find a solution for, despite hours of googling, so I hope someone here can help!
I've got a report - rptClientsByCountry -...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |