Stored Procedure problem...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
I have another problem with a stored procedure. What I am tryin to do is if the @discipline variable is not empty append my search variable, please see below:

Code: 
CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255),@discipline as varchar(50)
AS
declare @strQuery varchar(8000)
declare @strOrig varchar(3)
set @strOrig = Yes
set @strQuery = SELECT * FROM commentsTbl WHERE projName= + @project +  and original= + @strOrig +  and commtitle like  + @search +  or commDesc like  + @search +  or commAction like  + @search + 

if  @discipline != "
@strQuery .=  and commDisp= + @discipline + 
end if

exec (@strQuery)
GO

The problem that I have got is in the if statement, it returns the following error:

Server: Msg 105, Level 15, State 1, Procedure spSearchComm, Line 8
Unclosed quotation mark before the character string
@strQuery .= and commDisp= + @discipline +
end if

exec (@strQuery)
.
Server: Msg 170, Level 15, State 1, Procedure spSearchComm, Line 8
Line 8: Incorrect syntax near
@strQuery .= and commDisp= + @discipline +
end if

exec (@strQuery)
.

Is anyone able to point out where I have gone wrong?

Thanks inadvance

Simon
 
Last edited by a moderator:
Not tried it but would something like the following be suitable? Also if you can avoid concatenating strings to build SQL commands you will generally get slightly better performance but more importantly a more robust and secure system.
Code: 
CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255),@discipline as varchar(50)
AS
declare @strOrig varchar(3)
set @strOrig = Yes
if  @discipline = "
SELECT * FROM commentsTbl WHERE projName= @project and original= @strOrig  and commtitle like @search  or commDesc like @search or commAction like  @search 
ELSE
SELECT * FROM commentsTbl WHERE projName= @project and original= @strOrig  and commtitle like @search  or commDesc like @search or commAction like  @search and commDisp= @discipline
end if
GO
 
Thanks for the advise, however when I use the below code:

Code: 
CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255),@discipline as varchar(50)
AS
declare @strOrig varchar(3)
set @strOrig = Yes
if  @discipline = 
SELECT * FROM commentsTbl WHERE projName=@project and original=@strOrig  and commtitle like @search  or commDesc like @search or commAction like  @search 
ELSE
SELECT * FROM commentsTbl WHERE projName=@project and original=@strOrig  and commtitle like @search  or commDesc like @search or commAction like  @search and commDisp=@discipline
end
GO

It gives me the following error:

Server: Msg 156, Level 15, State 1, Procedure spSearchComm, Line 9
Incorrect syntax near the keyword end.

Any ideas? Thanks in advance

Simon
 
Im afraid that it is still giving me the following error, sorry to be a pain but have you any ideas???

Code that I am using:

Code: 
CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255),@discipline as varchar(50)
AS
declare @strOrig varchar(3)
set @strOrig = Yes
if  @discipline = 
SELECT * FROM commentsTbl WHERE projName=@project and original=@strOrig  and commtitle like @search  or commDesc like @search or commAction like  @search 
ELSE
SELECT * FROM commentsTbl WHERE projName=@project and original=@strOrig  and commtitle like @search  or commDesc like @search or commAction like  @search and commDisp=@discipline
end
GO

Error:

Server: Msg 156, Level 15, State 1, Procedure spSearchComm, Line 9
Incorrect syntax near the keyword end.

Any ideas? Thanks in advance

Simon
 
Thank you that does not give an error anymore. However I have another problem now. For some reason the sql above always returns all of the values even if there is some text within @discipline variable.

This is how I am doing the search. I have one textfield that allows the user to enter a string value (@search variable) and a combobox for the discipline (@discipline). If the search text field is empty then I pass to the @search variable % so that it lists all of the values. This works fine, however when I select a value from the combobox (@discipline variable) it still list all the values in my table.

Am I doing something wrong in the sql statement???

Thanks in advance

Simon
 
The second sql query uses both @search and @discipline in its criteria (I just cut and pasted your original code) - have you tried removing the references to @seach from the second query.
 
Back
Top