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)
 Grabbing a RecordCount from a Stored Procedure

Author  Topic 

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-09-17 : 19:38:11
Hi everyone,
I have a stored procedure created which pulls data to post as a lookup and creates an Expression to give me a recordcount.

This works ...

sELECT Customer_id, customer, COUNT(*)
FROM dbo.Customer
GROUP BY customer_id, customer
UNION ALL
SELECT NULL, NULL, COUNT(*) AS Expr
FROM dbo.Customer

Any ideas how I can grab the expr and place it into a variable or label in .Net?

Appreciate your help as always ... this is a great site for SQL :)
edb

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-17 : 22:33:03
Am not sure exactly what you're doing, but am assuming you are using ADO to get your data to your front end and you want this data in a recordset. Your query should have an ORDER BY Customer or Customer_Id to guarantee the NULL row appearing first, then you can feed that value whereever you want. Also, the first COUNT() is the one that must be aliased, not the one appearing after the UNION.

Sarah Berger MCSD
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-18 : 00:58:18
Just wondering, why are you doing a GROUP BY and COUNT(*) on the first statement? Doesn't it return all the rows in the Customer Table (assuming Customer_Id is the primary key)?

SELECT Customer_id, customer, 0 AS Expr
FROM dbo.Customer
UNION ALL
SELECT NULL, NULL, COUNT(*)
FROM dbo.Customer

Does this work the same way?

Owais


Make it idiot proof and someone will make a better idiot
Go to Top of Page

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-09-18 : 02:20:55
quote:

Any ideas how I can grab the expr and place it into a variable or label in .Net?



Return Expr as an output variable. Then the following code in C# should work....


using(SqlConnection myConn = new SqlConnection(**your connection string here***))
{
SqlCommand myCommand = new SqlCommand("sprocName", myConn);
myCommand.CommandType = CommandType.StoredProcedure;

// Variable for the corresponding parameter in your stored procedure
SqlParameter Expr = myCommand.Parameters.Add("@Expr", SqlDbType.Int, 4);
Expr.Direction = ParameterDirection.Output;

// Open the connection to the database
myConn.Open();
// If you are not returning a resultset and just want a single value
// use ExecuteNonQuery.
myCommand.ExecuteNonQuery();
myCommand.Dispose();

// make the value of the output parameter the text of your label
myLabel.Text = myCommand.Parameter["@Expr"].ToString();
}


hth,
Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"
Go to Top of Page
   

- Advertisement -