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" />

Stored Proc
LtCondor
Tim5827
Brad Alexander
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
_anna