September 19, 2009 at 6:13 am
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.
September 19, 2009 at 7:25 am
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
September 19, 2009 at 7:32 am
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.
September 19, 2009 at 8:58 am
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
September 22, 2009 at 7:10 am
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?
September 22, 2009 at 7:49 am
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
September 22, 2009 at 10:53 am
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.
September 22, 2009 at 11:13 am
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 ORSELECT * 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