Stored Proc

I am  working on a vb.net app w/SQL Server 2K backend.  App uses microsoft's Ent. library Data Access Application block for data access.  The app is working fine.  I have stored procedures that will not return the desired results.  Below is a sample proc.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
Go

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RaidSiteCaseCount')
 BEGIN
  DROP  Procedure  RaidSiteCaseCount
 END

GO

------------------------------------------------------------------------------------------------------------------------
-- Date Created: Friday, November 11, 2005
-- Created By:   Clifton Robertson
------------------------------------------------------------------------------------------------------------------------

Create Procedure RaidSiteCaseCount
(
@SecurityToken as uniqueidentifier,
@CaseCount as int out
)
as

Begin

SELECT @CaseCount = Count(DISTINCT ac.casenumber)
FROM dbo.[Inventory] i
Inner Join dbo.[AgencyCase] ac On i.ProjectID = ac.ProjectID and i.caseid = ac.caseid
WHERE i.ProjectID IN(SELECT ProjectID FROM fnProjectsValid())
 
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
go

This proc always returns 0 to the calling application.  If I run the select in analyzer it works fine.  Also if I change the select of the procedure to just do select count(*) from [table], it works and returns the correct count to the calling application.  I even placed a Select @CaseCount right before the END keyword, that did not work.  I also tried SET instead of select, you know,  SET @CaseCount = (Select Count(DISTINCT ac.casenumber)………….., this did not work either.  What is going on with this   Can anyone help < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />



 

 



Answer this question

Stored Proc

  • LtCondor

    When you say it 'returns' 0, have you tried to return @CaseCount, or are you saying @CaseCount is always 0



  • Tim5827

    In the application when I perform GetParameterValue for @CaseCount its value is 0.  However, if I place say, SET @CaseCount = where is an arbitrary number as the last line in the proc, that number is returned when GetParameterValue for @CaseCount is called.
  • Brad Alexander

    No.  There is only one object with that name.  Keep in mind that I get the desired result returned to the app when

    1.  The last line of the proc is a SET stmnt ie.  SET @CaseCount = 43 or any other number.

    OR

    2,  The select in the proc is a simple stmnt like - Select Count(*) from

    Either of these 2 scenarios work.

    Its the SQL in the proc that is not working.

    Thnx

  • _x3m

    Is it possible that your database contains multiple objects with the same name, one is being used by the IDE when you run this yourself, and another is visible to the user that ASP.NET logs in as


  • _anna

    You need to post a simple repro that demonstrates the problem. I don't see anything wrong with the SP code itself. It could be a bug in your client code also. How are you retreiving the output parameter value in the client What API are you using

  • Stored Proc