Solved

Need help creating a stored procedure

Posted on 2016-09-19
8
63 Views
Last Modified: 2016-09-22
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

0
Comment
Question by:maximus1974
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41805683
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
 
LVL 24

Expert Comment

by:chaau
ID: 41805783
@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
 
LVL 40

Expert Comment

by:Sharath
ID: 41805805
Why dynamic SQL? "SELECT * INTO" shall create the tables. right?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41806020
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 41807069
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41807154
Hmm, doesn't that code give you only the steps that you would have deleted?!
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41808143
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now