SELECT statement returning integer value 1

[glyn6]

I'm trying to return the value of a COUNT query as an integer can someone tell me what I'm doing wrong now?

CREATE PROCEDURE [dbo].[Countit]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT COUNT(CompanyDetailID) as CountOfID
	FROM mainCompanyDetail
	WHERE Deleted = 0

return CountOfID
END

Thanks

 

[bborissov]

You can't return FIELD from a query, you can return variable:

CREATE PROCEDURE [dbo].[Countit]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CountOfID int

    SELECT @CountOfID = COUNT(CompanyDetailID)
    FROM mainCompanyDetail
    WHERE Deleted = 0

return @CountOfID
END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[glyn6]

That executes nicely But how do I see the values?
"Execute Countit" in query analyser brings back "Command(s) completed successfully."

In the Vb code I'm trying to do this

Dim oRecordset As IDataReader = db.ExecuteReader(dbCommand)
oRecordset.Read
Dim x As String = Convert.ToString(oRecordset("CountOfID"))

 

[bborissov]

You use datareader, it expects RecordSet to be returned.
The SP I wrote didn't return ANY recordset, it just return a count.
So if you want to have RecordSet AND return value you should change it to:

CREATE PROCEDURE [dbo].[Countit]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CountOfID int

    SELECT @CountOfID = COUNT(CompanyDetailID)
    FROM mainCompanyDetail
    WHERE Deleted = 0
    SELECT @CountOfID AS CountOfID

return @CountOfID
END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[glyn6]

Perfect thanks?
What code should I use if I just want to return the count and not a recordset?

 

[bborissov]

The first one
But it depends HOW you get the value from your frontend.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[glyn6]

Sorry, I should have said "what code should I use in VB.Net to return the count? " DOH

 

[bborissov]

That's a question for VB.NET forum
I'm not a .NET person.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

In VB.NET you can return your code as Output Parameter.

Check this thread

http://forums.asp.net/p/1481883/3460628.aspx#3460628

(it deals with different problem, but has examples of getting the output parameter).

 

[JoeAtWork]

I think I would just have kept using the DataReader and made this minor modification to your stored procedure:

CREATE PROCEDURE [dbo].[Countit]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(CompanyDetailID) as CountOfID
    FROM mainCompanyDetail
    WHERE Deleted = 0
END

I don't see the point of storing the query result in a variable first. It's also less code in both the stored procedure and in .NET. Also, it's a convention that the return value should always indicate what error may have occurred (where zero indicates no error).

 

[markros]

I disagree. In our case we're returning a single int value, so the simplest way to get it back would be to use either ExecuteNonQuery and get it through output parameter or ExecuteScalar and get it through return value.

 

[glyn6]

Now that I know what I'm looking for I found this page

http://www.4guysfromrolla.com/articles/062905-1.aspx

which gives a few different methods from both the SQL and VB/C# side

 

[markros]

Good article!