You don't say what the text is of the error msg you are getting. But when I execute the code as written, the msg quite clearly tells me that your use of double quotes in if @Value1 <>"" is wrong. More than that, if you simply replace the double quotes with singe quotes, that syntax error will go away, but you will still have a runtime bug. The line should be if @Value1 is not null and @Value <>'' or, more succinctly, if IsNull( @Value1, '' ) <>''.
Then there is the matter of the dynamic SQL string you are building. Imagine someone executes the stored procedure and passes in the string 'George'. Manually follow the building of the string and see what you will be passing to sp_executesql. Here is what it will look like:
SELECT * FROM Table1 and Column1 LIKE George or Column2 like George or Column3 like George
The first thing that leaps out is that there is no WHERE clause. The first posted answer hinted at that. Fine, change "and" to "where" and that fixes that. But now you have three places where the word "George" appears. Because that word does not have "@" in front of it and is not delimited by single quotes, the compiler has to assume it refers to a column in the table. I would hazard a guess that your table does not have a column "George".
So you want to put the single quotes in the string building statement. There are three ways:
- set @SQL = @SQL + ' where Column 1 like ''' + @Value1 + ''' or Column2 like ''' ...
- set @SQL = @SQL + ' where Column 1 like ' + QuoteName( @Value1, '''' ) + ' or Column2 like ' + ...
- Leave the existing line as is but insert before it the line set @Value1 = QuoteName( @Value1, '''' );
Personally, I prefer using the QuoteName function as it provides a safeguard against SQL injection. I also do it like this:
create proc Procname
@uqValue1 varchar(50) -- Input string in original form
as begin
declare @SQL nvarchar(500),
@qValue1 varchar(50);
set @SQL = 'select * from Table1';
if IsNull( @uqValue1, '' ) <> '' begin
set @qValue1 = QuoteName( @Value1, '''' );
-- Now you have Value1 in quoted (@qValue1) and unquoted (@uqValue1) form.
set @SQL = ...
...
because you never know when you will need to use the input string in either quoted or unquoted form. By prefixing the parameters with "q" and "uq" for "quoted" and "unquoted", you have to stop and think which one you need whenever you use it in the code.