Help with T-SQL
-
Please help! I want to add the number of records of a table into a variable such as below but I can't make it works.
declare @total int
set @total = 100
select count (*) from Northwind..Orders -- 830 rows
set @total = @total + result from (select count (*) from Northwind..Orders)
print @total -- should be 930.
Thanks a lot.
-
declare @total int
set @total = 100
select @total = @total + count (*) from Northwind..Orders -- 830 rows
select @total
* Noel -
declare @total int
set @total = 100
select count (*) from Northwind..Orders -- 830 rows
set @total = @total + (select count (*) from Northwind..Orders)
print @total -- should be 930
This works fine for me. Why do you say it is not working? What results are you getting?
-
don't know why it didn't work at first time for me. Must be Friday. Thanks guys.
-
I still got stucked here. If I replace the select ... with a stored procedure. How do I make it works:
declare @total int
set @total = 100
create proc sps_test
as
select count (*) from Northwind..Orders -- 830 rows
go
set @total = @total + exec sps_test
print @total -- should be 930
Thx.
-
First, let me make a fairly imprecise (but helpful) definition. An expression is something that can be turned into a value. For example, in the statement SELECT productid, productname FROM product the terms productid and productname are the only expressions because they are the only things that represent values. By contrast, SELECT and FROM are simply syntax keywords, and product is a table name - which is an object, but not something reduceable to a value.
I wanted to say that to make it easier to say this. When you use the + operator, it expects two operands, one before it and one after. And both operands have to be ... you guessed it ... expressions. Stored procedure invocations are not expressions because they do not resolve to values. They do have return values (which you can catch with the exec @rc = myproc syntax), they can have output parameters, and they can set error codes, but none of those things are the same as resolving into a value. Function calls, however, are expressions.
So here are some choices for you. First, you could make your stored procedure have an output parameter and then capture the value of that output parameter in the calling code, after which you could use it for whatever purposes you want. Second, you could write a stored function instead of a stored procedure. Third, you could do something more convoluted like using INSERT ... EXEC to put your results into a temp table and then SELECTing the value out of the temp table into your local variable.
If you don't know how to do any of these things, just say so. I have to leave the office immediately so I can't post sample code right now, but I will probably be back online later tonight.
Having said that, I should also point out that these are all basic T-SQL ideas and so there are probably hundreds of people here who can help you out even before I log back on tonight. Good luck,
Chris
-
I was able to do this with a temp table, just wanted to see if there is other way. Thx.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply