CREATE PROC GetCheapestCompanies
  @LocID    int = 0,
  @CatID    int = 0,
  @BrandID  int = 0,
  @FeeLimit int = 0,
  @RecCount int = 0 OUTPUT
WITH ENCRYPTION
AS

  SET NOCOUNT ON

  IF ((@iLocationID+@iCategoryID+@iBrandID) IS NULL
  OR (@iLocationID+@iCategoryID+@iBrandID) = 0)
  BEGIN
    RETURN 0
  END

--  BEGIN TRANSACTION

    DECLARE @sSQL nvarchar(500)

    SET @sSQL = '
    SELECT CheapestCompanies.CompID AS [CompID]
    FROM
    (
      SELECT p.CompID, c.LocID, 
        MIN(p.ProductFee) AS [ProductFee]
      FROM tbProducts p, tbCompanies c
      WHERE
        c.CompID = p.CompID '

        IF (@LocID <> 0) 
          SET @sSQL = @sSQL + 'AND c.LocID = '+ CAST(@LocID AS nvarchar(10))

        IF (@CatID <> 0) 
          SET @sSQL = @sSQL + 'AND p.iProductCategoryID = '+ CAST(@CatID AS nvarchar(10))

        IF (@BrandID <> 0) 
          SET @sSQL = @sSQL + 'AND p.BrandID = '+ CAST(@BrandID AS nvarchar(10))

      SET @sSQL = @sSQL +'GROUP BY p.CompID, c.LocID '

      IF (@FeeLimit <> 0) 
        SET @sSQL = @sSQL + 'HAVING MIN(p.ProductFee) > '+ CAST(@FeeLimit AS nvarchar(10))

    SET @sSQL = @sSQL +'
    ) AS CheapestCompanies'

    -- ## execute the query
    EXEC sp_executesql @sSQL

    IF ((@@ROWCOUNT <= 0) OR (@@ERROR <> 0))
      SET @RecCount = 0
    ELSE
      SET @RecCount = @@ROWCOUNT

--  COMMIT TRANSACTION
GO

quote:

---

.. sql server 7 returns recordset successfully but it doesnt return RecCount OUTPUT variable, why? i dont know, thanx all..

---

quote:

---

The output designation is required when calling the stored procedure ( Even though it is declared on the receiving end). Assuming you are calling it correctly, you should be getting at least some value returned from the SProc.

---

DECLARE @RecCount INT

EXEC spGetCheapestCompanies
  @LocID = 7,
  @CatID = 5,
  @BrandID  = 4,
  @RecCount = @RecCount OUTPUT

SELECT @RecCount
--PRINT CAST(@RecCount AS varchar(10)) +" recs are returned."

quote:

---

@RecCount = @RecCount OUTPUT

---

set nocount on

declare @RecCount  int
select @RecCount = -1

create table #tmp (value int not null)
insert #tmp values (1)
insert #tmp values (2)
insert #tmp values (3)
insert #tmp values (4)

	
select * from #tmp

IF ((@@ROWCOUNT <= 0) OR (@@ERROR <> 0))
	SET @RecCount = 0
ELSE
	SET @RecCount = @@ROWCOUNT

select @RecCount as RecCount


declare @RecCount2  int , @err int
select @RecCount2 = -1

select * from #tmp
	
select @RecCount2 = @@ROWCOUNT, @err = @@ERROR


IF ((@RecCount2 <= 0) OR (@err <> 0))
	SET @RecCount2 = 0
--ELSE
	--SET @RecCount = @@ROWCOUNT


select @RecCount2 as RecCount2

	
drop table #tmp

-- here is the output
value       
----------- 
1
2
3
4

RecCount    
----------- 
0

value       
----------- 
1
2
3
4

RecCount2   
----------- 
4

quote:

---

and c.locID = (case when @locID<>0 then CAST(@LocID AS nvarchar(10) else c.LocID)

---