Stored procedure parameters

[djfeldspiff]

Hi everyone,

Below is a stored procedure referenced within a config file as part of an executable running within a SQL job. My goal is to create a DTS package and remove the dependance of the executable within the job, but I'm unsuccessful in setting the parameters to point to data the procedure process. I'll handle multiple records each time the job runs.

Can I set the parameters within the DTS package in advance of executing the stored procedure? How do I make sure I handle multiple rows of data?

Thanks so much for your direction.


Here is the stored procedure:

CREATE PROCEDURE spInsertControllerData
@AH_ALLOC_NUM as integer,
@AH_ALLOC_INV_NUM as integer,
@AL_PICK_NUM as integer,
@AH_ORD_NUM as integer,
@AL_WHS_PICK_LOC as char(7),
@PK_SHIP_METH_CD as char(1)

AS

Declare @InvNum as Integer
Declare @INVPICK as Integer
Declare @Zone as varchar (3)
Declare @Sort as integer
Declare @date as datetime
Declare @ZString as varchar(255)

Set @INVPICK = left(cast(@AH_ALLOC_INV_NUM as varchar),7 + '00') + right('00'+ cast(@AL_PICK_NUM as varchar),2)
Set @InvNum = (select invnum from tblKAdata where invnum = @INVPICK)
Set @Zone = (SELECT tblWHZONES.ZONE FROM tblWHZONES INNER JOIN tblWHLOCS ON tblWHZONES.ZNID = tblWHLOCS.ZNID WHERE (((tblWHLOCS.LOC)=substring(@AL_WHS_PICK_LOC,1,4))))
Set @Sort = (SELECT tblWHZONES.ZNSRTORD FROM tblWHZONES INNER JOIN tblWHLOCS ON tblWHZONES.ZNID = tblWHLOCS.ZNID WHERE (((tblWHLOCS.LOC)=substring(@AL_WHS_PICK_LOC,1,4))))
Set @date = getdate()

--Insert new record into tblKAdata, shipmethod (1=Air, 2=Customer Pickup, 3=International, 4=Ground, 5=USPS), date added
If isNull( @InvNum, 0) = 0 Begin
Insert Into tblKAdata values (@INVPICK, Null, CASE @PK_SHIP_METH_CD
WHEN '4' THEN '1'
WHEN '5' THEN '1'
WHEN '6' THEN '1'
WHEN 'A' THEN '1'
WHEN 'F' THEN '1'
WHEN 'N' THEN '1'
WHEN 'P' THEN '1'
WHEN 'T' THEN '1'
WHEN 'H' THEN '2'
WHEN '2' THEN '2'
WHEN 'I' THEN '3'
WHEN 'J' THEN '3'
WHEN 'K' THEN '3'
WHEN 'L' THEN '3'
WHEN 'M' THEN '3'
WHEN '7' THEN '5' else '4' END, Null, Null,@date,null,null)
End


--Insert to tblIZ Inv, Zone
Declare @Inv as integer
Set @Inv = (select inv from tblIZ where inv = @INVPICK and Zone=@Zone)

--Insert new record into tblIZ
If isNull( @Inv, 0) = 0 Begin
Insert into tblIZ values (@INVPICK, @Zone, @Sort)
-- Insert into tblIZ values (@AH_ALLOC_INV_NUM, @Zone, @Sort)
end

--select distinct zones based on Inv, order into cursor
declare zones_curs cursor for
select Zone
from tblIZ
where Inv = @INVPICK and Zone is not null
for read only

--create new variable=string of the zone data in proper order
Open zones_curs
set @Zstring=''
fetch next from zones_curs into @Zone
while @@fetch_status = 0 begin
Set @ZString = @ZString +','+ @Zone
fetch next from zones_curs into @Zone
end
close zones_curs
deallocate zones_curs

--update tblkadata.Wzone with new variable
Update tblKAdata
Set tblKAdata.WZONE = substring(@Zstring,2,254)
where tblKAdata.INVNUM = @INVPICK

GO

 

[SQLSister]

Don't have time to really look closely at this but I can tell you that if you want to handle multiple rows of insert you should not be using the values clause. Use a selct statment to pick what to insert insetead. The only other choice is to cursor through the records and exec the proc each time but that is horribly inefficient and would be a worst practice and thus not recommended.

"NOTHING is more important in a database than integrity." ESquared

 

[djfeldspiff]

Thanks SQLSister,

Within the DTS package I set up a transform-data task to extract the data I need to populate a table within SQL 2000. The task mirrors the query contained within the config file. I've attempted to set up input paramenters within the DTS package to point to the fields of the table with the same alias referenced within the stored procedure, thinking this would be best, but I've yet to get the results I need.

Thanks for your input!

 

[SQLSister]

Usually I insert to a work table with the transform task and then use an Exc SQL task to do the rest of the work. In this case I would wrte a proc that works on the work table and does set-based inserts (using the select statments) to insert into your real tables.

"NOTHING is more important in a database than integrity." ESquared