I have a SQL Server Stored Procedure that updates a table. The procedure has to be run for multiple rows and should return nothing.
How can I call SQL Server Stored Procedure and pass multiple rows as input?
Definition of Stored Procedure
USE [IBMDB]
GO
/****** Object: StoredProcedure [dbo].[IBMEDW_Update_Process] ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[IBMEDW_Update_Process]
@Interface_System char(3),
@Process_Type char(6),
@Process_Date datetime,
@Process_Flag char(1),
@Variables char(5)
AS
SET NOCOUNT ON
BEGIN
UPDATE DBO.IBM_PROCESS_INT SET
PROCESS_FLAG = @Process_Flag,
PROCESS_DATE = @Process_Date
WHERE INTERFACE_SYSTEM = @Interface_System
AND PROCESS_TYPE = @Process_Type
AND substring(VARIABLES,1,5) = @Variables
END
Calling SQL Server Stored Procedure
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 71
- Joined: Mon Jul 09, 2007 1:12 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 71
- Joined: Mon Jul 09, 2007 1:12 pm
Use ODBC Stage as you suggested in your email.
Source > ODBC Stage (Server Job)
From Source, pull the column name in the same order as mentioned in the INPUT PARAMETER and via a transformer drag all the columns to an ODBC Stage. In one of the TAB in ODBC Stage Use "Server Procedure", import the definition.. the Stored Procedure SQL will be something like
{CALL STOREDPROC(?,?,?,?,?)} 1 question mark for every imput and output. Since your SP doesn't return any rows there are no output parameters.
This should work using a SERVER JOB.
If you want to make PARALLEL JOB work the same way, see if you have DRS ODBC STAGE (TARGET) and then COPY the EXECUTION SQL from server job and PASTE it in TARGET where the SQL OPERATION should be USER DEFINED.
Good Luck !
Source > ODBC Stage (Server Job)
From Source, pull the column name in the same order as mentioned in the INPUT PARAMETER and via a transformer drag all the columns to an ODBC Stage. In one of the TAB in ODBC Stage Use "Server Procedure", import the definition.. the Stored Procedure SQL will be something like
{CALL STOREDPROC(?,?,?,?,?)} 1 question mark for every imput and output. Since your SP doesn't return any rows there are no output parameters.
This should work using a SERVER JOB.
If you want to make PARALLEL JOB work the same way, see if you have DRS ODBC STAGE (TARGET) and then COPY the EXECUTION SQL from server job and PASTE it in TARGET where the SQL OPERATION should be USER DEFINED.
Good Luck !
-
- Participant
- Posts: 71
- Joined: Mon Jul 09, 2007 1:12 pm