Programming Forums - SQL Query Problem

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Other Scripting Languages (http://www.programmingforums.org/forum39.html)

- - SQL Query Problem (http://www.programmingforums.org/showthread.php?t=13273)

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:

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 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 :

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

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.

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:

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!

Database administration

Quote:

Originally Posted by King (Post 128705)

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

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