SQL Server 2005 - Building a WHERE clause
- by user336786
Hello,
I have a stored procedure that is dynamically building a query. The where clause associated with this query is based on filter values selected by a user. No matter what I do though, the where clause does not seem to get set.
-- Dynamically build the WHERE clause based on the filters
DECLARE @whereClause as nvarchar(1024)
IF (@hasSpouse > -1)
BEGIN
IF (@hasSpouse = 0)
SET @whereClause='p.[HasSpouse]=0'
ELSE
SET @whereClause='(p.[HasSpouse]=1 OR p.[HasSpouse] IS NULL)'
END
-- Dynamically add the next filter if necessary
IF (@isVegan > -1)
BEGIN
IF (LEN(@whereClause) > 0)
BEGIN
SET @whereClause = @whereClause + ' AND '
END
IF (@isVegan = 0)
SET @whereClause = @whereClause + 'c.[IsVegan]=0'
ELSE
SET @whereClause = @whereClause + '(c.[IsVegan]=1 OR c.[IsVegan] IS NULL)'
END
PRINT @whereClause
The @whereClause never prints anything. In turn, the LEN(@whereClause) is always NULL. The @isVegan and @hasSpouse values are passed into the stored procedure. The values are what I expected.
What am I doing wrong? Why is the @whereClause never being set?
Thank you for your help!
Thank you!
