Programming Forums > Script Programming > Other Scripting Languages

King · Jun 3rd, 2007, 2:07 AM

I am trying to write a SQL stored procedure that will search a certain field in a table. This is what I have so far:

CREATE PROCEDURE pSearchRecords
(
 @Category varchar(50),
 @Search varchar(50)
)
AS
SELECT * FROM tAddressBook
WHERE @Category = @Search

@Category is the field I want to search, and @search is the text I want to search for in that field. The problem is when I go "@Category = @Search", it checks the actual value of the variable @Category instead of using that value as the field I want to search. Does anyone know how I can do this properly? Thanks.

xavier · Jun 3rd, 2007, 2:53 AM

I don't think you can do that. But lets say you have Category1, Category2, Category3.
Now, you don't know what category the user will select , or if he will select all of them. So you do like this :

 sql Syntax (Toggle Plain Text) 
CREATE PROCEDURE pSearchRecords
(
 @SearchCategory1 varchar(50),
 @SearchCategory2 varchar(50),
 @SearchCategory3 varchar(50)
)
AS
SELECT * FROM tAddressBook
WHERE (Category1 IS NULL OR Category1 = @SearchCategory1)
AND (Category2 IS NULL OR Category2 = @SearchCategory2) -- if Category2 will be null, it will be ignored.
AND (Category3 IS NULL OR Category3 = @SearchCategory3)
CREATE PROCEDURE pSearchRecords
(
 @SearchCategory1 varchar(50),
 @SearchCategory2 varchar(50),
 @SearchCategory3 varchar(50)
)
AS
SELECT * FROM tAddressBook
WHERE (Category1 is null or Category1 = @SearchCategory1)
and (Category2 is null or Category2 = @SearchCategory2) -- if Category2 will be null, it will be ignored.
and (Category3 is null or Category3 = @SearchCategory3)

That's one way of doing it ... maybe there are other

King · Jun 3rd, 2007, 11:06 AM

I was going to do something like that, but there are 30 fields in this table and don't want to type it all out hahah. I might have to though, thanks.

mackenga · Jun 9th, 2007, 9:13 AM

Hmm, to me this sounds like a database design problem. You should never end up with 30 fields in a table each of which represents a category that you might want to search by in the same sort of way. If you designed the table structure, I recommend reading some introductory stuff about database design - the way SQL works makes so much more sense if you do the right thing as far as relational databases are concerned.

At a quick glance, this article looks like it wouldn't be a bad starting point:

http://www.edm2.com/0612/msql7.html

It might be a bit simple for you in places since it's starting from the very basics, and it looks like a bit of a dry read, too, but it looks really useful to me.

Hope this helps!

Oh, and just to confirm: you can't specify which field to use in a WHERE clause via a variable. This code:

(Toggle Plain Text)

CREATE PROCEDURE pSearchRecords
(
 @Category varchar(50),
 @Search varchar(50)
)
AS
SELECT * FROM tAddressBook
WHERE @Category = @Search

will return nothing (empty recordset) if the values of the two variables differ, and if they are the same, it will return every row in the table. It has no way of knowing that the value in @Category is a table name; it just treats it as a value, the same way it treats @Search, so in the end your WHERE clause ends up just comparing two scalar values from the arguments, not looking at the data coming back from the table at all.

P.S.: Why use T-SQL if you're addicted to Linux?

Switch to MySQL or Postgres! I wish I could, but they'll only pay me to code for MS SQL 2000. Bah!

rubia126 · Aug 8th, 2007, 6:31 AM

Quote:

Originally Posted by King

I am trying to write a SQL stored procedure that will search a certain field in a table. This is what I have so far:

(Toggle Plain Text)

CREATE PROCEDURE pSearchRecords
(
 @Category varchar(50),
 @Search varchar(50)
)
AS
SELECT * FROM tAddressBook
WHERE @Category = @Search

@Category is the field I want to search, and @search is the text I want to search for in that field. The problem is when I go "@Category = @Search", it checks the actual value of the variable @Category instead of using that value as the field I want to search. Does anyone know how I can do this properly? Thanks.

Hey thr

well i dont know much about your problem but i have a hunch that your having some sort of database issues... am a CS student and was facing same sort of issues few days back.. my frend use to visit www.oracleplace.com for soving his programming and database issues on his advice i try it on too and guess waht it helps me out... hope this will help you too.

Good Luck
BIA

Jun 3rd, 2007, 2:07 AM	#1
King Professional Programmer Join Date: Jan 2006 Location: Ontario, Canada Posts: 376 Rep Power: 0	SQL Query Problem I am trying to write a SQL stored procedure that will search a certain field in a table. This is what I have so far: (Toggle Plain Text) CREATE PROCEDURE pSearchRecords ( @Category varchar(50), @Search varchar(50) ) AS SELECT * FROM tAddressBook WHERE @Category = @Search CREATE PROCEDURE pSearchRecords ( @Category varchar(50), @Search varchar(50) ) AS SELECT * FROM tAddressBook WHERE @Category = @Search @Category is the field I want to search, and @search is the text I want to search for in that field. The problem is when I go "@Category = @Search", it checks the actual value of the variable @Category instead of using that value as the field I want to search. Does anyone know how I can do this properly? Thanks. __________________ I am Addicted to Linux!

Jun 3rd, 2007, 2:53 AM	#2
xavier Professional Programmer Join Date: Oct 2004 Location: .ro Posts: 383 Rep Power: 4	I don't think you can do that. But lets say you have Category1, Category2, Category3. Now, you don't know what category the user will select , or if he will select all of them. So you do like this : sql Syntax (Toggle Plain Text) CREATE PROCEDURE pSearchRecords ( @SearchCategory1 varchar(50), @SearchCategory2 varchar(50), @SearchCategory3 varchar(50) ) AS SELECT * FROM tAddressBook WHERE (Category1 IS NULL OR Category1 = @SearchCategory1) AND (Category2 IS NULL OR Category2 = @SearchCategory2) -- if Category2 will be null, it will be ignored. AND (Category3 IS NULL OR Category3 = @SearchCategory3) CREATE PROCEDURE pSearchRecords ( @SearchCategory1 varchar(50), @SearchCategory2 varchar(50), @SearchCategory3 varchar(50) ) AS SELECT * FROM tAddressBook WHERE (Category1 is null or Category1 = @SearchCategory1) and (Category2 is null or Category2 = @SearchCategory2) -- if Category2 will be null, it will be ignored. and (Category3 is null or Category3 = @SearchCategory3) That's one way of doing it ... maybe there are other __________________ Don't take life too seriously, it's not permanent ! Last edited by xavier; Jun 3rd, 2007 at 2:54 AM. Reason: misplaced comma

Jun 3rd, 2007, 11:06 AM	#3
King Professional Programmer Join Date: Jan 2006 Location: Ontario, Canada Posts: 376 Rep Power: 0	I was going to do something like that, but there are 30 fields in this table and don't want to type it all out hahah. I might have to though, thanks. __________________ I am Addicted to Linux!

Jun 9th, 2007, 9:13 AM	#4
mackenga Professional Programmer Join Date: Mar 2005 Location: Glasgow, Scotland Posts: 317 Rep Power: 4	Hmm, to me this sounds like a database design problem. You should never end up with 30 fields in a table each of which represents a category that you might want to search by in the same sort of way. If you designed the table structure, I recommend reading some introductory stuff about database design - the way SQL works makes so much more sense if you do the right thing as far as relational databases are concerned. At a quick glance, this article looks like it wouldn't be a bad starting point: http://www.edm2.com/0612/msql7.html It might be a bit simple for you in places since it's starting from the very basics, and it looks like a bit of a dry read, too, but it looks really useful to me. Hope this helps! Oh, and just to confirm: you can't specify which field to use in a WHERE clause via a variable. This code: (Toggle Plain Text) CREATE PROCEDURE pSearchRecords ( @Category varchar(50), @Search varchar(50) ) AS SELECT * FROM tAddressBook WHERE @Category = @Search CREATE PROCEDURE pSearchRecords ( @Category varchar(50), @Search varchar(50) ) AS SELECT * FROM tAddressBook WHERE @Category = @Search will return nothing (empty recordset) if the values of the two variables differ, and if they are the same, it will return every row in the table. It has no way of knowing that the value in @Category is a table name; it just treats it as a value, the same way it treats @Search, so in the end your WHERE clause ends up just comparing two scalar values from the arguments, not looking at the data coming back from the table at all. P.S.: Why use T-SQL if you're addicted to Linux? Switch to MySQL or Postgres! I wish I could, but they'll only pay me to code for MS SQL 2000. Bah! __________________ "I'm not a genius. Why do I have to suffer?"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
<td> height problem	grimpirate	HTML / XHTML / CSS	19	May 4th, 2007 6:01 AM
cgi/perl script + IE problem	joyceshee	Perl	2	Jan 24th, 2006 11:10 AM
What means 'Operation must use updateable query' ?	jonyzz	ASP	6	Sep 22nd, 2005 6:11 PM
Variable array problem	Hintshigen	C	6	Apr 10th, 2005 2:35 PM
help with recursion problem	the_new_guy_in_town	Java	3	Apr 7th, 2005 3:04 AM

Programming Forums > Script Programming > Other Scripting Languages

SQL Query Problem