Using a declared variable in SQL statement
-
Dear All,
How can I use a declared variable which has a value from an sql query, I wanna use the returned result in another SQL query.
Note: the sql query returns number values.
and the following is my case:
declare @Cnt varchar(100)
set @Cnt = 'SELECT count(CName) AS Countt FROM [TableName] where CName= 850891144 '
exec (@Cnt)
--The previous returns 3
--Now, I need to use the previous returned value in the next SQL query
SELECT sum(Volume)/@Cnt AS TVolume,
from TableName
where (--Any Condition)
The problem is: Arithmetic overflow error converting varchar to data type numeric.
So, How can I solve this?
Note: I tried to cast the @Cnt, but the error still the same.
-
Above you are assigning the whole SQL Statement to the variable, you should do something along the lines of:
declare @Cnt intSELECT @Cnt = count(CName) FROM [TableName] where CName= 850891144SELECT sum(Volume)/@Cnt AS TVolume,from TableNamewhere (--Any Condition)Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
You declared the variable as type varchar, but the select statement returns a numeric value (because you use the count function).
Declaring the variable as type int should solve your problem.
-
Thanks a lot Mr.Andras Belokosztolszki,
you solved my problem...I was thinking I can not retrieve the SQL query result in an Int variable :doze::crying:
-
Division by zero is not handled here
-
You should write a conditional select statement then:
declare @Cnt int
SELECT @Cnt = count(CName) FROM [TableName] where CName= 850891144
select TVolume=
case @cnt
when 0 then
/* assuming you want zero returned for TVolume in this case */
0
else
sum(Volume)/@Cnt
end
from actual
Egon Rijk
-
I have a doubt.What is the best practice in following the DECLARE statement?
It is DECLARE @variable1 datatype,@variable2 datatype
or
DECLARE @variable1 datatype
DECLARE @variable2 datatype
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply