Stored procedures
-
HI,
Ran into this likely newbie 😉 problem which I could not resolve. Solution loogs fairly simple but just can't figure it out. Anyway have a Stored procedure which I need to cut in two pieces to work, trying to save it into one just does not work. could anyone have a quick look and tell what I'm doing wrong here :-). Have similar Sp's working fine
Thanks in advance
Danny
CREATE PROCEDURE exec SP_ADD_SCRAP_EXCEPTION_REVIEW1
AS
--- ADD COLUMN TO SCRAP TABLE
ALTER TABLE SCRAP ADD [EPM] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [REASON_NOT_TO_SCRAP] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [PROPOSED_ACTION] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [ACTION_FINISHED_DATE] SMALLDATETIME NULL
ALTER TABLE SCRAP ADD [DATE_REVIEW_ADDED] SMALLDATETIME NULL
ALTER TABLE SCRAP ADD [OWNER] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [COMMENT] NVARCHAR (255)NULL
----------------------------------------------------------------------------------------------
CREATE PROCEDURE SP_ADD_SCRAP_EXCEPTION_REVIEW2
AS
--- ADD EXCEPTION REVIEW COMMENTS TO SCRAP TABLE
Update Scrap
Set EPM = B.EPM
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
Update Scrap
Set REASON_NOT_TO_SCRAP = B.REASON_NOT_TO_SCRAP
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
Update Scrap
Set PROPOSED_ACTION = B.PROPOSED_ACTION
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
Update Scrap
Set ACTION_FINISHED_DATE = B.ACTION_FINISHED_DATE
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
Update Scrap
Set DATE_REVIEW_ADDED = B.DATE_REVIEW_ADDED
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
Update Scrap
Set OWNER = B.OWNER
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
Update Scrap
Set COMMENT = B.COMMENT
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
-
If you want to create proc SP_ADD_SCRAP_EXCEPTION_REVIEW2
from within SP_ADD_SCRAP_EXCEPTION_REVIEW1,
you'll have to use exec(@sql).
Else
put a GO before the second create proc statement
end if
CREATE PROCEDURE SP_ADD_SCRAP_EXCEPTION_REVIEW1
AS
BEGIN
--- ADD COLUMN TO SCRAP TABLE
ALTER TABLE SCRAP ADD [EPM] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [REASON_NOT_TO_SCRAP] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [PROPOSED_ACTION] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [ACTION_FINISHED_DATE] SMALLDATETIME NULL
ALTER TABLE SCRAP ADD [DATE_REVIEW_ADDED] SMALLDATETIME NULL
ALTER TABLE SCRAP ADD [OWNER] NVARCHAR (255)NULL
ALTER TABLE SCRAP ADD [COMMENT] NVARCHAR (255)NULL
----------------------------------------------------------------------------------------------
declare @sql varchar(5000)
set @sql = 'CREATE PROCEDURE SP_ADD_SCRAP_EXCEPTION_REVIEW2
AS
--- ADD EXCEPTION REVIEW COMMENTS TO SCRAP TABLE
Update A
, EPM = B.EPM
, REASON_NOT_TO_SCRAP = B.REASON_NOT_TO_SCRAP
, PROPOSED_ACTION = B.PROPOSED_ACTION
, ACTION_FINISHED_DATE = B.ACTION_FINISHED_DATE
, DATE_REVIEW_ADDED = B.DATE_REVIEW_ADDED
, OWNER = B.OWNER
, COMMENT = B.COMMENT
From Scrap A , dbo.Scrap_exception_review B
Where A.INV_ITEM_ID = B.INV_ITEM_ID
'
exec (@sql)
END
What's the reason you put this in a SP ?
It's supposed to be run only once
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi,
Tried it and works perfectly, thanks for the reply time to refrech the SQL training 🙂
Regards
Danny
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply