Post reply

Using a declared variable in SQL statement

  • August 4, 2008 at 2:26 am

    #93608

    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.

  • August 4, 2008 at 2:50 am

    #852548

    Above you are assigning the whole SQL Statement to the variable, you should do something along the lines of:

    declare @Cnt int

    SELECT @Cnt = count(CName) FROM [TableName] where CName= 850891144

    SELECT sum(Volume)/@Cnt AS TVolume,

    from TableName

    where (--Any Condition)

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 5, 2008 at 8:42 am

    #853222

    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.

  • August 5, 2008 at 11:30 pm

    #853625

    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:

  • August 6, 2008 at 5:06 am

    #853718

    Division by zero is not handled here 🙂

  • August 6, 2008 at 6:18 am

    #853746

    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

  • October 6, 2010 at 11:26 pm

    #1233258

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply