Variable Declaration Question
-
Hello, I was loking at building a search on my site to return records in my SQL DB. So naturally, i went to sql2005 books online and found the following example
USE scx_3215;
GO
DECLARE @SearchWord as varchar(30)
SET @SearchWord = 'network'
SELECT ittdescription FROM tbl3215
WHERE CONTAINS(ittDescription, @SearchWord);
GO
This works great, except I have to ask how to enhance my query slightly:
1. I want to not hardcode the word network, so how one i declare the variable @SearchTerm, how do i get it to prompt me for the serach string i want to feed it (what the user inputs in the box on my site). I have tried the floowing with no success
USE scx_3215;
GO
DECLARE @SearchWord as varchar(30)
--SET @SearchWord = 'network' <--this does not work, null or empty full-text predicate ( i think i understand this, but is there an article where someone can explain this a little more?)
SELECT ittdescription FROM tbl3215
WHERE CONTAINS(ittDescription, @SearchWord);
GO
and have also tried
USE scx_3215;
GO
DECLARE @SearchWord as varchar(30)
SET @SearchWord = @SearchWord <--null or empty full-text predicate
SELECT ittdescription FROM tbl3215
WHERE CONTAINS(ittDescription, @SearchWord);
GO
2. the second thing i want to do is search against more than one column in the DB. Can you help me with the syntax? For example, if I want to
USE scx_3215;
GO
DECLARE @SearchWord as varchar(30)
SET @SearchWord = 'network'
SELECT ittdescription, itttitle, ittdescription, ittnumber FROM tbl3215
WHERE CONTAINS(ittDescription,itttitle,iddescription,ittnumber @SearchWord);
GO
do i have to declare one variable for each column? Thank You
Joe
-
First, do you have FULL Text Indexing set up on your SQL Server?
The CONTAINS keyword only works with that. Same as FREETEXT, CONTAINSTABLE and FREETEXTTABLE.
Secondly, you have to code your website to prompt you for the search term, not T-SQL. This would probably be a COM object, javascript, an ASP.NET script or something that runs inside the webpage. Use that script code to pass the search term to a stored procedure which will execute against your database and find the appropriate value(s) you are looking for.
VERY IMPORTANT: Do not run the T-SQL code with the SearchString directly from the webpage to the database as you can open yourself up to SQL injection attacks. Make the webpage call a database stored procedure so that no hackers can change the code on your webpage or use the Search Term in ways you don't want it to be used.
In the stored procedure, you can use a WHERE XTerm = @MySearchTerm kind of thing or use dynamic SQL to build the SQL String you want to execute.
-
Hi brandie
thanks for the prompt response. Yes, it has full-text capabilities and i selected the table and columns that i want searchable with the runtime late at night every night.
Ahh, i didnt know that, so in the code behind page should it be something like
dim mysearchterm as string
sql.data.storedprocedure = ("search", mysearchterm)
then my sp should be something like this?
CREATE PROCEDURE [dbo.search]
use scx_3215
GO
{
@ittnumber varchar
@ittdescription varchar
@itttitle varchar
@techsolution varchar
}
Declare @search as varchar
select * from tbl3215 where (@ittnumber OR @ittdescription OR itttitle OR @techsolution = (@search)) RETURN @search
Thanks for the tip about SP's, i am slowly migrating all my queries within the code to SP's. Still a newb at all this, but having fun even though i have developed a bump on my head from banging it off the desk too many times
-
Yes, you have the right idea, but your SP WHERE clause is wrong.
You can't use the WHERE clause to match the variables you are passing into the SP to each other. You have to match the variables to literal values in the table you are selecting from.
Since you have FULL Text Indexing available, you should be able to use CONTAINS, but you might have to build your SQL Statement dynamically.
But, as an example of using SQL without the CONTAINS...
Select * from tbl3215 where tbl3215.ittnumber = @search or tbl3215.ittdescription = @search or tbl3215.itttitle = @search or tbl3215.technsolution = @search
Do you see what I'm doing? Pulling the literal column names from the table and seeing if they're equal to @Search. And if you're using the AND / OR keywords, you have to restate the column name each time.
-
hi again, thank you for the explanation. I understand what you are doing and it makes sense, but somethihng is not adding up. when i run this as a SP like so
USE [SCX_3215]
GO
/****** Object: StoredProcedure [dbo].[search] Script Date: 09/18/2007 15:42:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[search]
(
@ittNumber nvarchar(max),
@ittdescription nvarchar(max),
@itttitle nvarchar(max),
@techsolution nvarchar(max),
@search nvarchar(max)
)
AS
SET NOCOUNT OFF;
Select * from tbl3215 where tbl3215.ittnumber = @search or tbl3215.ittdescription = @search or tbl3215.itttitle = @search or tbl3215.techsolution = @search
return @search
the following errors are returned.
USE [SCX_3215]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[search]
@ittdescription = N'network'
SELECT 'Return Value' = @return_value
GO
and: Msg 201, Level 16, State 4, Procedure search, Line 0
Procedure or function 'search' expects parameter '@ittNumber', which was not supplied.
(1 row(s) affected)
ok obviously it wont return an ittnumber because i did not feed it one, but i thought since the OR clause was used it was only looking at whichever value i fed it?
Thanks, sorry if these seem to be dumb questions....
-
You have to default any variables that you might potentially not receive from outside the stored procedure. However, you can't actually use the DEFAULT keyword with variables that are passed in and out of the procedure. Only with sp local variables.
So, in your proc declaration, you'd do something like:
ALTER PROCEDURE [dbo].[search]
(
@ittNumber nvarchar(max),
@ittdescription nvarchar(max),
@itttitle nvarchar(max),
@techsolution nvarchar(max),
@search nvarchar(max)
)
AS
If (@ittnumber IS NULL) Set @ittnumber = '000'....
Use a "Set" for each variable that might not have a value and an IF...ELSE statement to check for a value before it sets anything. You don't want to overwrite any values passed in, after all.
-
Still kind of stuck, it has to be something simple i am not doing correctly.
USE [SCX_3215]
GO
/****** Object: StoredProcedure [dbo].[search] Script Date: 09/18/2007 15:42:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[search]
(
@ittNumber nvarchar(max),
@ittdescription nvarchar(max),
@itttitle nvarchar(max),
@techsolution nvarchar(max),
@search nvarchar(max)
)
AS
SET NOCOUNT OFF;
Select * from tbl3215 where tbl3215.ittnumber = @search or tbl3215.ittdescription = @search or tbl3215.itttitle = @search or tbl3215.techsolution = @search
begin
if (@ittnumber is NULL) set @ittnumber = 'XYZ'
else
if (@ittdescription is null) set @ittdescription = 'zyx'
else
if (@itttitle is NULL) set @itttitle = 'XXX'
else
if (@techsolution is null) set @techsolution = 'ZZZ'
end
return @search
here is the error returned
USE [SCX_3215]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[search]
@ittNumber = N'20060001'
SELECT 'Return Value' = @return_value
GO and: Msg 201, Level 16, State 4, Procedure search, Line 0
Procedure or function 'search' expects parameter '@ittdescription', which was not supplied.
(1 row(s) affected)
-
Joe,
You need to have your variable defaulting done BEFORE you do your SELECT statement. Otherwise, it won't work.
ALTER PROCEDURE [dbo].[search]
(
@ittNumber nvarchar(max),
@ittdescription nvarchar(max),
@itttitle nvarchar(max),
@techsolution nvarchar(max),
@search nvarchar(max)
)
AS
SET NOCOUNT OFF;
if (@ittnumber is NULL) set @ittnumber = 'XYZ'
else
if (@ittdescription is null) set @ittdescription = 'zyx'
else
if (@itttitle is NULL) set @itttitle = 'XXX'
else
if (@techsolution is null) set @techsolution = 'ZZZ'
Select * from tbl3215 where tbl3215.ittnumber = @search or tbl3215.ittdescription = @search or tbl3215.itttitle = @search or tbl3215.techsolution = @search
...<rest of SP>...
-
im sorry, it still is not working and i dont know what to do...
can you take another look or point me in the right direction? I would like to figure this out but i am rather lost...I appreciate your help
USE [SCX_3215]
GO
/****** Object: StoredProcedure [dbo].[search] Script Date: 09/19/2007 13:12:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[search]
(
@ittNumber nvarchar(max),
@ittdescription nvarchar(max),
@itttitle nvarchar(max),
@techsolution nvarchar(max),
@search nvarchar(max)
)
AS
SET NOCOUNT OFF;
if (@ittnumber is NULL) set @ittnumber = 'XYZ'
else
if (@ittdescription is null) set @ittdescription = 'zyx'
else
if (@itttitle is NULL) set @itttitle = 'XXX'
else
if (@techsolution is null) set @techsolution = 'ZZZ'
Select * from tbl3215 where tbl3215.ittnumber = @search or tbl3215.ittdescription = @search or tbl3215.itttitle = @search or tbl3215.techsolution = @search
return @search
-
What is the structure of tbl3215? What columns do you have set up for Full Text Indexing?
-
above is the index, below is the structure
would you like DDL as well? Would you prefer insert format?
-
Unfortunately, I can't see your pictures. My browser is just showing two little white boxes with red X's in them.
Can you paste in text?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply