Calling SQL Server Stored Procedure

horserider · Post by **horserider** » Thu Jun 05, 2008 8:24 pm

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

chulett · Post by **chulett** » Thu Jun 05, 2008 8:33 pm

Sorry, can't answer your SQL Server question but I have to ask - why a stored procedure just to do that? Why not do your updates directly in DataStage?

ray.wurlod · Post by **ray.wurlod** » Thu Jun 05, 2008 8:35 pm

If you call your stored procedure in an ODBC stage all rows delivered to the stage will be passed as inputs to the stored procedure.

horserider · Post by **horserider** » Thu Jun 05, 2008 9:05 pm

So, if all my parameters are in a Sequential File, I should design my job this way in a SERVER JOB?

(Server Job)

SQL FILE > TRANSFORMER > ODBC Stage

I don't have ODBC STAGE in my Parallel Job pane but I only see ODBC ENTERPRISE. So can I design SERVER job to accomplish this?

chulett · Post by **chulett** » Fri Jun 06, 2008 6:34 am

Well, the Server ODBC stage has an Update action of 'Call stored procedure' so would work as shown. Does the Enterprise stage? If so, it should work in a similar manner.

shamshad · Post by **shamshad** » Fri Jun 06, 2008 4:43 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 !

horserider · Post by **horserider** » Fri Jun 06, 2008 4:45 pm

Shamshad,

Thanks bunch ! It works as you mentioned in SERVER JOB. I am sure it will work in PARALLEL JOB too.

Thanks once gain. I love this Forum.