DECLARE @Where varchar(1000)
... -- Setting up @Where
EXEC('SELECT @ProjectCount = Count(*) FROM Basis WHERE ' + @Where)

SELECT @ProjectCount = Count(*) FROM Basis

quote:

---

Originally posted by alanlambert

I have a stored procedure which takes several parameters including one output parameter called @ProjectCount.

The stored procedure returns several recordsets and must also set the value of the @ProjectCount parameter.

All of the recordsets are created normally but the SQL needed to calculate the @ProjectCount parameter has to be generated dynamically.

My problem comes when I try to assign a value to this parameter using dynamic SQL:

DECLARE @Where varchar(1000)
... -- Setting up @Where
EXEC('SELECT @ProjectCount = Count(*) FROM Basis WHERE ' + @Where)

When this runs I get the message: Must declare the variable '@Count'.

If I change the dynamic SQL to something static e.g.:

SELECT @ProjectCount = Count(*) FROM Basis

it works fine but, obviously, doesn't give me the result I need.

---

declare @sqlString nvarchar(1000)
set @sqlString = N'SELECT @ProjectCount = Count(*) FROM Basis WHERE'
+ @where

exec sp_executesql @sqlString,
N'@ProjectCount int output',
@ProjectCount=@ProjectCount output