Combining two stored procedures

Last Post 18 Jun 2008 09:56 AM by pjr222. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
pjr222
New Member
New Member

--
16 Jun 2008 12:49 PM
I'm in the process of doing some housekeeping on a database that I've inherited. One of my objectives is to combine two stored procedures into one. Both procedures use the same input parameters and run against the same table. The first procedure runs a simple SELECT statement to ensure that duplicate values don't exist in the table. The 2nd procedure does an INSERT. I've combined the procs in a number of manners; however, nothing seems to work. It seems as if the parameters that get passed into the SELECT statement are forgotten once I get down to the INSERT statement.

Here are the two procedures. Any ideas on how to combine the two into one? Thanks!

**** PROC1****************************************
CREATE PROCEDURE Proc1
(
@ID_List int,
@ID_Job_No int,
@UserName varchar(100),
@Effective_Date datetime,
)
AS
SET NOCOUNT ON

SELECT ID_List, ID_Job_No, UserName, Effective_Date
FROM Job_List
WHERE (ID_List = @ID_List)
AND (ID_Job_No = @ID_Job_No)
AND (UserName = @UserName)
AND (Effective_Date = @Effective_Date)
GO

IF @@ROWCOUNT <> 0
BEGIN
print N'ERROR: Attempt to insert duplicate values.';
if @@error != 0
SELECT @@error as ErrorNumber
SELECT @@error as ErrorLine
SELECT @@error as ErrorMessage
end
else
begin
print N'No duplicate values found.';
-- BEGIN INSERT HERE
end
GO
********************************************

****** PROC2 **************************************

CREATE PROCEDURE Proc2
(
@ID_List int,
@ID_Job_No int,
@UserName varchar(100),
@Effective_Date datetime,
)
AS
SET NOCOUNT ON

INSERT INTO Job_List
(
ID_List,
ID_Job_No,
UserName,
Effective_Date,
)

VALUES
(
@ID_List,
@ID_Job_No,
@UserName,
@Effective_Date,
)
GO
IF @@ERROR <> 0
SELECT @@error as ErrorNumber
ELSE
BEGIN
PRINT N'The insert was successful.';
END;
Go
********************************************
SQLUSA
New Member
New Member

--
16 Jun 2008 09:04 PM
Try the combined sproc. Let us know how it goes.

This is the way to handle errors ( I did not change error part):
-----------------------------------------------------------------------------------------
declare @Error int
insert into emp (empno,ename,sal,deptno)
values (@empno,@ename,@sal,@deptno)

set @Error = @@ERROR
if @Error <> 0 --if error is raised
begin
goto LogError
end
------------------------------------------------------------------------------------------


CREATE PROCEDURE Proc1
(
@ID_List int,
@ID_Job_No int,
@UserName varchar(100),
@Effective_Date datetime
)
AS
SET NOCOUNT ON

SELECT ID_List, ID_Job_No, UserName, Effective_Date
FROM Job_List
WHERE (ID_List = @ID_List)
AND (ID_Job_No = @ID_Job_No)
AND (UserName = @UserName)
AND (Effective_Date = @Effective_Date)


IF @@ROWCOUNT <> 0
BEGIN
print N'ERROR: Attempt to insert duplicate values.';
if @@error != 0
SELECT @@error as ErrorNumber
SELECT @@error as ErrorLine
SELECT @@error as ErrorMessage
end
else
begin
print N'No duplicate values found.';
-- BEGIN INSERT HERE
INSERT INTO Job_List
(
ID_List,
ID_Job_No,
UserName,
Effective_Date
)

VALUES
(
@ID_List,
@ID_Job_No,
@UserName,
@Effective_Date
)
IF @@ERROR <> 0
SELECT @@error as ErrorNumber
ELSE
BEGIN
PRINT N'The insert was successful.';
END;
end
GO



Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/

pjr222
New Member
New Member

--
17 Jun 2008 10:15 AM
Thanks for the info! Unfortunately I get the same error message I got in my earlier attempts to combine both procedures - "Must declare the scalar variable "@ID_List." It looks like this is occurring as soon as I declare the values for the insert. I've tried a number of workarounds but no success yet. Any ideas? Thanks!
pjr222
New Member
New Member

--
18 Jun 2008 09:56 AM
Yep, had an extra "GO" in there. Good catch. Thanks, that solved the problem!
You are not authorized to post a reply.

Acceptable Use Policy