why not try explicitly extracting the row count and return it in an output parameter, something like
Code:
create proc someTopSecretProcedure(
@material varchar(500),
@RecordCount int output,
)
as
CREATE TABLE #TMPTBL_MATERIAL
(
xNumber VARCHAR(50)
)
declare @Build nvarchar(3000)
select @Build = 'INSERT INTO #TMPTBL_MATERIAL(xNumber)
SELECT DISTINCT i.poNumber FROM dbo.po_items i WHERE i.itemNumber IN ('+ @material + ') AND i.dateCreated = (select Max(dateCreated) from dbo.po_items where itemNumber = i.itemNumber);
select @RecordCount = @@RowCount;'
--EXEC(@BUILD)
exec sp_executesql @Build, '@RecordCount output', @RecordCount output
go
if you cant use output parameters then do a
Code:
select @RecordCount as RowsAffected
as the last line of the procedure... and declare @RecordCount inside the stored proc
additionally, if you are not using the data in the temp able then use a table variable
Code:
declare @tblMaterial table (xNumber VARCHAR(50) primary key)