Hi,
I am currently creating an ASP page that returns a recordset of search
result based on multiple keywords. The where string is dynamically
built on the server page and that part work quite well. However I want
to also return the number of records in the recordset and I can not
manage to get any output parameter working. Even if I assign SELECT
@mycount=100 (or SET @mycount=100) in the SP the only value set in
mycount is always NULL. I tested various theories (e.g. early exit,
order & naming of parameters etc. but I can not make the SP set the
output parameters - has it anything to do with the execute?). @mycount
also returns NULL if I test it in SQL QA.
What's wrong with this SP (as regards mycount):
CREATE PROCEDURE dbo.spFindProducts
@mycount integer OUTPUT,
@whereString varchar (1000)
AS
--SET NOCOUNT ON
--Set a Default value for the Wherestring which will return all records
if the Wherestring is blank
IF @whereString is Null
SELECT @whereString = 'AND TblProduct.ProductID is not null'
--Declare a variable to hold the concatenated SQL string
DECLARE @SQL varchar(2500)
-- AND (((UPPER([TblProduct].[ProductName] + [ProductGroupCode] +
[AttributeValue1] +
-- [SearchWords] + [tblSupplier].[SupplierCode] + [SupplierDesc]))
Like '%screw%'))
SELECT @SQL = 'SELECT TblProduct.ProductID, TblProduct.ProductName,
TblProduct.ChapterCode, tblProduct.ProductGroupCode' +
' FROM (TblProduct LEFT JOIN TblStockItem ON TblProduct.ProductID =
TblStockItem.ProductID) ' +
' LEFT JOIN tblSupplier ON TblProduct.SupplierCode =
tblSupplier.SupplierCode' +
' WHERE 1=1 ' + @whereString +
' GROUP BY TblProduct.ProductID, TblProduct.ProductName,
TblProduct.ChapterCode, TblProduct.ProductGroupCode'
SELECT @mycount = 200; -- test
execute (@SQL);
-- next line seems to be ignored ?
SELECT @mycount = @@rowcount;
GO
tia
Axel
