I have some (say, 3) stored procedures, and they all have the same one parameter. Each SP returns a dataset. I need to import each dataset to a table using DTS.

So, I created an "Execute SQL Task" and typed in "SQL Statement" something like this:

INSERT INTO TABLE TABLE1
EXEC SP1 ?
GO
INSERT INTO TABLE TABLE2
EXEC SP2 ?
GO
INSERT INTO TABLE TABLE3
EXEC SP3 ?
GO

And the execution return an error: No value given for one or more required parameters.

Is it possible to assign a parameter to all three SPs?

Solution.

INSERT INTO TABLE TABLE1
EXEC SP1 @sp1para=1
GO


INSERT INTO TABLE TABLE2
EXEC SP2 @sp2param=1
GO


INSERT INTO TABLE TABLE3
EXEC SP3 @sp3param=1
GO

I you use go statement once in the end. then you can do following
DECLARE @VALUE INTEGER
SET @VALUE=1

INSERT INTO TABLE TABLE1
EXEC SP1 @sp1para=@VALUE

INSERT INTO TABLE TABLE2
EXEC SP2 @sp2param=@VALUE

INSERT INTO TABLE TABLE3
EXEC SP3 @sp3param=@VALUE
GO


urt

Thanks for the reply urt.

The actual value of the parameter comes from a global variable of the DTS package. So I changed the SQL statement as follow:

DECLARE @VALUE INTEGER
SET @VALUE=?

INSERT INTO TABLE TABLE1
EXEC SP1 @sp1para=@VALUE

INSERT INTO TABLE TABLE2
EXEC SP2 @sp2param=@VALUE

INSERT INTO TABLE TABLE3
EXEC SP3 @sp3param=@VALUE
GO

It's working!