Stored Proc Output Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc Output Parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-14 : 07:28:34

Cliff writes "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 = 'GetCaseCount')
BEGIN
DROP Procedure GetCaseCount
END

GO

Create Procedure GetCaseCount
(
@EmpID as uniqueidentifier,
@CaseCount as int out
)
as

Begin

SELECT @CaseCount = Count(DISTINCT ac.casenumber)
FROM dbo.[AppInventory] i
Inner Join dbo.[AppCase] 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. If I code @CaseCount = ?? where ?? is an aribtrary number that nummber is returned to the calling application. 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?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources