Connditional executing of script

  • Hi Gurus,

    I have 2 parts of a script

    PART ONE ---

    DELETE FROM [ReorderS]

    GO

    INSERT INTO [ReorderS]

    SELECT ITEM,NULL FROM [INV_ITEM_MAS]

    GO

    UPDATE [ReorderS] SET Quantity = [PO_Qty]

    FROM [PO ITEM INTERFACE] INNER JOIN [INV_ITEM_MAS]

    ON [PO ITEM INTERFACE].[PO_Item]= [ReorderS].ITEM

    Part TWO --

    DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50),@val3 varchar(50),@Sql varchar(8000);

    set @val1='SQLSRV'

    set @val4=CONVERT(VARCHAR(10), GETDATE(), 101)

    Declare sample_cur cursor for

    SELECT Item_Code,Quantity FROM ReorderS

    OPEN sample_cur

    Fetch next from sample_cur into @val2, @val3

    While (@@fetch_status<>-1)

    BEGIN

    SET @Sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'

    print @Sql

    Fetch next from sample_cur into @val2, @val3

    END

    Close sample_cur

    Deallocate sample_cur

    PART TWO SHOULD EXECUTE AFTER PART ONE IS COMPLETE AND ALSO ONLY WHEN SELECT COUNT(*) FROM [ReorderS] > 0

    HOW DO I DO THIS?THANKS IN ADVANCE.

  • Refer to Books On Line (BOL)

    IF...ELSE (Transact-SQL)

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/676c881f-dee1-417a-bc51-55da62398e81.htm

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • There are several ways, but I would create 2-step job using your 2 scripts and put something like

    if (select count(*) from FROM [ReorderS]) > 0

    in the beginning of second step.

  • also note you cannot have a GO statement inside your IF statements BEGIN /END block...

    your example for part one would not be valid, it would have to be like this:

    IF EXISTS(SELECT 1 FROM [ReorderS]) --what is your condition?

    BEGIN

    DELETE FROM [ReorderS]

    INSERT INTO [ReorderS]

    SELECT ITEM,NULL FROM [INV_ITEM_MAS]

    UPDATE [ReorderS] SET Quantity = [PO_Qty]

    FROM [PO ITEM INTERFACE] INNER JOIN [INV_ITEM_MAS]

    ON [PO ITEM INTERFACE].[PO_Item]= [ReorderS].ITEM

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Gurus,

    I am not able to make much progress.If I have the below

    if (select count(*) from FROM [ReorderS] where Item is not null) > 0

    I get error incorrect systax near > and also what will be in the ELSE part?

  • if you need the actual count, instead of just checking for orders, try this way:

    declare @TheCount int

    select @TheCount = count(*) FROM [ReorderS] where Item is not null

    if @TheCount> 0

    BEGIN

    --do stuff

    END

    ELSE

    BEGIN

    --do other stuff

    END

    alternatively, you can use EXISTS:

    if EXISTS(SELECT * FROM [ReorderS] where Item is not null)

    BEGIN

    --do stuff

    END

    ELSE

    BEGIN

    --do other stuff

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • hi lowell,thanks for the help.2 questions apart from SELECT * FROM [ReorderS] where Item is not null there is one more count.so it is acually SELECT * FROM [ReorderS] where Item is not null OR

    SELECT * FROM [AnotherTable] where Item is not null IF either of these is not null then it should execute and THERE is NO ELSE part.That is if either of these is count is not there then there is nothing to execute.so how do I modify.Thanks in advance.

  • mathewspsimon (9/22/2009)


    hi lowell,thanks for the help.2 questions apart from SELECT * FROM [ReorderS] where Item is not null there is one more count.so it is acually SELECT * FROM [ReorderS] where Item is not null OR

    SELECT * FROM [AnotherTable] where Item is not null IF either of these is not null then it should execute and THERE is NO ELSE part.That is if either of these is count is not there then there is nothing to execute.so how do I modify.Thanks in advance.

    mathewspsimon,

    "ELSE" is not a mandatory part, and we don't need this part in your case.

Viewing 8 posts - 1 through 7 (of 7 total)

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