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.
| 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.CustomerGROUP BY customer_id, customerUNION ALLSELECT NULL, NULL, COUNT(*) AS ExprFROM dbo.CustomerAny 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 |
 |
|
|
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 ExprFROM dbo.CustomerUNION ALLSELECT NULL, NULL, COUNT(*)FROM dbo.CustomerDoes this work the same way?Owais Make it idiot proof and someone will make a better idiot |
 |
|
|
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!" |
 |
|
|
|
|
|
|
|