Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

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

0
maximus1974
Asked:
maximus1974
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
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
 
SharathData EngineerCommented:
Why dynamic SQL? "SELECT * INTO" shall create the tables. right?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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:
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
 
Scott PletcherSenior DBACommented:
Hmm, doesn't that code give you only the steps that you would have deleted?!
0
 
Vitor MontalvãoMSSQL 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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now