Stored Procedure problem...

[lidds]

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:

 

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

Edited December 5, 2006 by PlausiblyDamp

[PlausiblyDamp]

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.

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

[lidds]

Thanks for the advise, however when I use the below 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

[PlausiblyDamp]

Whoops - my bad. just remove it and try again.

[lidds]

I'm 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:

 

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

[PlausiblyDamp]

Did you try deleting 'end' from the statement?

[lidds]

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

[PlausiblyDamp]

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