July 14, 2010 at 8:45 pm
At the moment, my code looks a bit like this:
exec dbo.usp_my_stored_proc 1, 12, 10000;
exec dbo.usp_my_stored_proc 2, 12, 10000;
exec dbo.usp_my_stored_proc 3, 13, 9000;
exec dbo.usp_my_stored_proc 7, 14, 10000;
exec dbo.usp_my_stored_proc 7, 15, 11000;
exec dbo.usp_my_stored_proc 8, 16, 10000;
.
.
.
and so on.
There must be a way I can move those parameter values into a table (of three cols in this case) and then execute the stored proc N times based on the results of a select statement.
I could do it easily enough with a while loop or a cursor, but is there a way I can do it without these?
Cheers
GPO
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
July 15, 2010 at 12:28 am
Hi There,
(edited my post)
--// SAMPLE PROCEDURE
CREATE PROCEDURE dbo.usp_my_stored_proc
@value1 INT,
@value2 INT,
@value3 INT
AS
BEGIN
SELECT @value1 AS 'Value1'
, @value2 AS 'Value2'
, @value3 AS 'Value3'
END
RETURN
GO
--// SAMPLE DATA
DECLARE @tbl TABLE
(
ID INT IDENTITY,
value1 NVARCHAR(MAX),
value2 NVARCHAR(MAX),
value3 NVARCHAR(MAX)
)
INSERT INTO @tbl
SELECT 1, 12, 10000
UNION SELECT 2, 12, 10000
UNION SELECT 3, 13, 9000
UNION SELECT 7, 14, 10000
UNION SELECT 7, 15, 11000
UNION SELECT 8, 16, 10000
--// WHAT YOU NEED
DECLARE @i INT, @max-2 INT, @query NVARCHAR(MAX)
SET @query=''
SELECT @query= @query +'EXEC dbo.usp_my_stored_proc '
+ value1 + ', '
+ value2 + ', '
+ value3 + ';
'
FROM @tbl
EXEC (@query)
--// DROP OF SAMPLE PROCEDURE
DROP PROCEDURE dbo.usp_my_stored_proc
I just recreated your script and ran it in one go.
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
July 15, 2010 at 12:55 am
GPO (7/14/2010)
At the moment, my code looks a bit like this:exec dbo.usp_my_stored_proc 1, 12, 10000;
exec dbo.usp_my_stored_proc 2, 12, 10000;
exec dbo.usp_my_stored_proc 3, 13, 9000;
exec dbo.usp_my_stored_proc 7, 14, 10000;
exec dbo.usp_my_stored_proc 7, 15, 11000;
exec dbo.usp_my_stored_proc 8, 16, 10000;
.
can use table valued parameters allows us to pass a table as a single parameter to a stored procedure.
Create Type YouTable as Table
(
col1 int,
col2 int,
col3 int
)
declare @T YouTable
insert into @T values
(1, 12, 10000),
(2, 12, 10000),
(3, 13, 9000)
exec dbo.usp_my_stored_proc @T
but first you must change your sp to receive a table valued parameter.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply