maximus1974
asked on
Need help creating a stored procedure
I want to create a stored procedure with the following script below. Reason being I want schedule this procedure as a job. How can I do this?
DROP TABLE DBO.WOMAST01B
DROP TABLE DBO.WOLABO01B
SELECT * into WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0')
SELECT * into WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM wolabo01 where 1=0')
INSERT INTO WOMAST01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01')
INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')
DELETE FROM WOMAST01B
WHERE STEP = 'IV'
DELETE FROM WOMAST01B
WHERE STEP = 'CL'
DELETE FROM WOMAST01B
WHERE STEP = 'PA'
DELETE FROM WOMAST01B
WHERE STEP = 'HD'
DELETE FROM WOMAST01B
WHERE STEP = 'ST'
DELETE FROM WOMAST01B
WHERE STEP = 'EG'
DELETE FROM WOMAST01B
WHERE STEP = 'BK'
DELETE FROM WOMAST01B
WHERE STEP = 'RE'
DELETE FROM WOMAST01B
WHERE STEP = 'RR'
DELETE FROM WOMAST01B
WHERE STEP = 'DR'
DELETE FROM WOMAST01B
WHERE STEP = 'SC'
DELETE FROM WOMAST01B
WHERE STEP = 'ML'
DELETE FROM WOMAST01B
WHERE STEP = 'AM'
DELETE FROM WOMAST01B
WHERE STEP = 'TE'
DELETE FROM WOMAST01B
WHERE STEP = '12'
DELETE FROM WOMAST01B
WHERE STEP = '01'
DELETE FROM WOMAST01B
WHERE STEP = 'QC'
Delete from WOMAST01B
WHERE RECDATE = '1899-12-30'
Delete from WOMAST01B
WHERE RECDATE < '2011-01-01'
@Sharath: I think you need to execute DDLs as dynamic SQL statements. Your stored procedure may not compile as the tables WOMAST01B and WOLABO01B may not be available
Why dynamic SQL? "SELECT * INTO" shall create the tables. right?
Your code can be easily improved in one single command for each table:
CREATE PROCEDURE sp_MyProcName
AS
DROP TABLE DBO.WOMAST01B
DROP TABLE DBO.WOLABO01B
INSERT INTO WOMAST01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01
WHERE STEP IN (''IV'',''CL'',''PA'',''HD'',''ST'',''EG'',''BK'',''RE'',''RR'',''DR'',''SC'',''ML'',''AM'',''TE'',''12'',''01'',''QC'')
OR RECDATE < ''2011-01-01''')
INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')
RETURN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmm, doesn't that code give you only the steps that you would have deleted?!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
Do you have lot of data to delete? Why don't you move all the filter conditions into one statement?
Open in new window