SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connditional executing of script


Connditional executing of script

Author
Message
mathewspsimon
mathewspsimon
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 231
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.
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9609 Visits: 25280
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

Before posting a performance problem please read
Sergey Vavinskiy
Sergey Vavinskiy
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 392
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.
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36865 Visits: 40274
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!

mathewspsimon
mathewspsimon
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 231
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?
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36865 Visits: 40274
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!

mathewspsimon
mathewspsimon
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 231
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.
Sergey Vavinskiy
Sergey Vavinskiy
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 392
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search