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'

Open in new window

maximus1974Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Here's my adjustments:
1) make sure ANSI_NULLS and QUOTED_IDENTIFIER are set properly
2) check for tables before DROPping them (otherwise you'll get an error if they don't exist)
3) don't copy rows from the remote server that you don't need (that you'll just delete anyway).  Alphabetize the list of STEPs to be excluded to make it easier to adjust / verify the list.


SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE procedure_name
AS
SET NOCOUNT ON;
IF OBJECT_ID('DBO.WOMAST01B') IS NOT NULL
    DROP TABLE DBO.WOMAST01B;
IF OBJECT_ID('DBO.WOLABO01B') IS NOT NULL
    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 WHERE STEP NOT IN(''01'',''12'',''AM'',''BK'',''CL'',''DR'',''EG'',''HD'',''IV'',''ML'',''PA'',''QC'',''RE'',''RR'',''SC'',''ST'',''TE'') AND RECDATE >= ''2011-01-01''');

INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01');
GO /*end of proc*/
0
 
SharathData EngineerCommented:
To create the SP, you can try like this.
create procedure your_proc as 
begin
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'
end

Open in new window


Do you have lot of data to delete? Why don't you move all the filter conditions into one statement?
create procedure your_proc as 
begin
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 IN ('IV', 'CL', 'PA', 'HD', 'ST', 'EG', 'BK', 'RE', 'RR', 'DR', 'SC', 'ML', 'AM', 'TE', '12', '01', 'QC')
OR RECDATE < '2011-01-01'

end

Open in new window

0
 
chaauCommented:
@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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
SharathData EngineerCommented:
Why dynamic SQL? "SELECT * INTO" shall create the tables. right?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
 
Scott PletcherSenior DBACommented:
Hmm, doesn't that code give you only the steps that you would have deleted?!
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Scott is right. I forgot to negate the filter:
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 NOT 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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.