Stored Procedure If Else Problem

  • Although this may sound really stupid but iam not being able to figure out where is the problem.

    i am getting this error message

    Server: Msg 170, Level 15, State 1, Procedure Pd_GivePoints, Line 61

    Line 61: Incorrect syntax near '@EchVpoin'.

    ------------------

    below is the code

    --exec pd_givepoints

    --drop procedure pd_givepoints

    CREATE PROCEDURE Pd_GivePoints AS

    declare @noofVisits as int

    declare @noofTele as int

    declare @cntV as int

    declare @cntT as int

    declare @telp as Varchar(4)

    declare @bal as varchar(20)

    declare @totlTelp as varchar(10)

    declare @EchVPoin as varchar(10)

    declare @SQlViUp as nvarchar(4000)

    declare @totlMd as VArchar(5)

    declare @totlVisits as varchar(5)

    declare @totlTel as varchar(5)

    --first count total tel+vis , and tel, and visits on a particular date

    declare curTotlMd cursor for select count(*) as totlmd from RMCPL_DAILY_ACTIVITY where

    RMCPL_USER_CD=4 and RMCPL_ENTRYDATE='2004-01-14'

    open curTotlMd

    fetch next from curTotlMd into @totlMd

    select @totlMd

    close curTotlMd

    deallocate curTotlMd

    declare CurTotlVisit Cursor for select count(*) as cntV from RMCPL_DAILY_ACTIVITY

    where RMCPL_USER_CD=4 and RMCPL_MODE='Visit'

    and RMCPL_ENTRYDATE='2004-01-14'

    open CurTotlVisit

    fetch next from CurTotlVisit into @totlVisits

    select @totlvisits

    close curtotlVisit

    deallocate CurtotlVisit

    declare CurTotlTel cursor for select count(*) as cntT from RMCPL_DAILY_ACTIVITY where

    RMCPL_USER_CD=4 and RMCPL_MODE='Tel' and RMCPL_ENTRYDATE='2004-01-14'

    open CurTotlTel

    fetch next from CurTotlTel into @totlTel

    close CurtotlTel

    deallocate CurTotlTel

    --select @totlTel

    if @totlMd >= 1

    @EchVpoin = 1 / @totlMd

    select @EchVpoin

    end

    --if @totlTel > =1

    --begin

    --@totlTelp = @cntT * 0.1

    ---@bal = @totlTelp-1

    --@EchVPoin=@bal/@cntV

    --else

    --@EchVPoin=1/@totlMd

    ---end

    --else

    --end

    --select @echVPoin

    --declare @SQLTPoints as nvarchar(4000)

    --@SQLTPoints=" Update set rmcpl_md_points="+@EchVPoin+ "RMCPL_DAILY_ACTIVITY where

    -- RMCPL_USER_CD=4 and RMCPL_MODE='Tel' and RMCPL_ENTRYDATE='2004-01-14'"

    --@SQLViup=" Update set rmcpl_md_points="+@EchVPoin+ "RMCPL_DAILY_ACTIVITY where

    -- RMCPL_USER_CD=4 and RMCPL_MODE='Visit' and RMCPL_ENTRYDATE='2004-01-14'

    --end if

    --end if

    --sp_executesql @SQLTPoints

    ---sp_executesql @SQLViup

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • That should be

    select @EchVpoin = 1 / @totlMd

    instead of

    @EchVpoin = 1 / @totlMd

    srv

  • Thanks A Lot,

    you solved my time consuming problem, no where in the sql server books there is any note of this thing.

    and i got the answer within 3-5 minutes of posting in this forum, thats what is great about this forum, so many experts log in..

    Thanks a Lot

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • Sure it is mentioned in BOL. Take a look at local variables, for example.

    Also, this is maybe interesting for that matter http://vyaskn.tripod.com/differences_between_set_and_select.htm

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • -- Looks to me that two syntax errors existed

    if @totlMd >= 1

    @EchVpoin = 1 / @totlMd

    select @EchVpoin

    end

    -- should it be like this???

    IF (@totlMd >= 1)

    BEGIN

    SELECT @EchVpoin = 1 / @totlMd

    -- SELECT @EchVpoin

    END

    /* CODE BETTER, FUNCTION BETTER */

    -- Your code should read like it runs


    Regards,

    Coach James

Viewing 5 posts - 1 through 4 (of 4 total)

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