Wayne Atherton Wayne Atherton - 7 months ago 9
SQL Question

stored procedure concatenate two parameters in where clause

I've got an annoying problem, where I pass two parameters into a stored procedure and join them together for use within the WHERE clause of my statement. The first parameter is used in an equals expression, whereas the second is to form an AND clause.

The below extract of the stored proc, shows what I am attempting to do.

Declare @CombinedWhereClause varchar(500), @Sender varchar(10), @AndClause char(200)

Set @Sender = 'Wayne';
Set @AndClause = ' AND Convert(varchar(8), MessageDate, 112) < DATEADD(day, -10, GETDATE())';
Set @CombinedWhereClause = @Sender + @AndClause;

SELECT Messages.Id, Messages.IdExternal, Messages.MessageReference,
FROM Messages
WHERE Messages.Sender =@CombinedWhereClause


If I replace the @CombinedWhereClause with @Sender only then I get back the expected records. If I change the where clause to be:

WHERE Message.MessageDate Convert(varchar(8), MessageDate,112) < DATEADD(day, -10, GETDATE())


then I get all records which are 10 days old. However, when combined I get no results.

Answer

In your statement are several flaws:

  • The comma behind the MessageReference
  • You try to have WHERE Messages.sender=Wayne but you need quotes ...='Wayne'
  • You think the db engine is somehow magic :-)
  • This is - for sure! - not the approach one should chose...

Try this dynamic approach (SQL Server syntax)

Declare @CombinedWhereClause varchar(500), @Sender varchar(10), @AndClause char(200)

Set @Sender = '''Wayne'''; --you need to wrap the word in quotes!
Set @AndClause = ' AND Convert(varchar(8), MessageDate, 112) < DATEADD(day, -10, GETDATE())';
Set @CombinedWhereClause = @Sender + @AndClause;

DECLARE @cmd VARCHAR(MAX)=
'SELECT Messages.Id, Messages.IdExternal, Messages.MessageReference 
 FROM Messages
 WHERE Messages.Sender =' + @CombinedWhereClause;

--check the command
SELECT @cmd;

--if the command is OK you might execute it
--EXEC(@cmd);
Comments