Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Connditional executing of script Expand / Collapse
Author
Message
Posted Saturday, September 19, 2009 6:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89, 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.
Post #790785
Posted Saturday, September 19, 2009 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 5,473, Visits: 23,560
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
Post #790792
Posted Saturday, September 19, 2009 7:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:25 AM
Points: 201, Visits: 390
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.
Post #790793
Posted Saturday, September 19, 2009 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 12,755, Visits: 31,122
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #790802
Posted Tuesday, September 22, 2009 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89, 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?
Post #791740
Posted Tuesday, September 22, 2009 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 12,755, Visits: 31,122
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #791764
Posted Tuesday, September 22, 2009 10:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89, 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.
Post #791996
Posted Tuesday, September 22, 2009 11:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:25 AM
Points: 201, Visits: 390
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.
Post #792014
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse