Execute SP using arrays - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execute SP using arrays

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-26 : 08:33:22

Deenie writes "I am using SQL 8.0. From a asp.net page, the user builds an array of 1-5 numbers (213, 348, 7892, 84683) and these numbers are passed to a stored procedure. The stored procedure contains another array containing a list of stored procedures (sp1, sp2, sp3, sp4, sp5) which need to be executed. Each called stored procedure will return an output (@ratio output) of a single ratio.

I need to build a temporary table that will consist of six columns. The columns will be:
SPName, ratio1(for the first number in the array), ratio2(for the second number in the array), ratio3(for the third number in the array) etc. Some of the columns may result in a null value, depending on the numbers passed to the primary sp.

I will need to execute the first stored procedure in the stored procedure array up to 5 times, return the value from the output, and append that value to the first row of my table in the proper place.

I am able to retreive the ratios one at a time, but do not know how to build my temporary table.

This is what I have thus far, but do not know how to continue:

CREATE PROCEDURE myProc
@numList varchar(500),
@reportYear char(4),
@quarter char(1),
@maxquarter char(1),
@stProcedure nvarchar(1000),
@strSQLExec nvarchar(1000), -- use to build table insert stmt
@strGetExec nvarchar(1000),
@rtrnValue float(30) -- hoping to use to return @ratio

(Here is a sample of what could be in the variables)
select @numList = '213, 5796, 248',
@reportYear = '2004',
@quarter = '3',
@stProcedure = 'sptierone' -- Using only 1 sp name for testing purposes. In production, this list will be much larger

create table #ParsedList
(
stProcedure varchar(80),
ratio1 int,
ratio2 int,
ratio3 int,
ratio4 int,
ratio5 int,

)

DECLARE @numCert varchar(10), @Pos int,
@spName varchar(80), @posSP int,
@blCount int, @posCount int,
@bankTemp varchar(500)
-- Set up a new variable to hold number list so as not to
-- empty the original list. I probably have to do something
-- here This is intended only for testing. I will also need
-- to do this for the stored procedure list
select @numTemp = @numList
select @posCount = CHARINDEX(',', @bankTemp)

BEGIN
SET @numTemp= LTRIM(RTRIM(@numTemp))+ ','
SET @stProcedure = LTRIM(RTRIM(@stProcedure))+ ','
SET @Pos = CHARINDEX(',', @numTemp)
SET @posSP = CHARINDEX(',', @stProcedure)

IF REPLACE(@stProcedure, ',', '') <> ''
BEGIN
WHILE @posSP > 0
BEGIN
SET @spName = LTRIM(RTRIM(LEFT(@stProcedure, @posSP - 1)))
IF @spName <> ''
--Needs further development set @strSQLExec = 'insert into temptable values (' + @spName
WHILE @pos > 0
BEGIN
set @numCert = LTRIM(RTRIM(LEFT(@numTemp, @pos - 1)))
select @maxquarter = max(quarter)
from ristatement
where year = @reportyear
SET @strGetExec = @spName + ' ' + @numCert + ',"' + @maxquarter + '","' + @quarter + '","' + @reportyear + '"'
set @strGetExec = @strGetExec + ', @ratio output'
set @numTemp= right(@numTemp, len(@numTemp) - @pos)
set @pos = charindex(',', @numTemp)
set @strGetExec = 'declare @ratio float(30) exec ' + @strGetExec + ' select @ratio'
exec (@strGetExec)
-- exec('declare @ratio float(30)
-- exec ' + @spName + ' ' + @numCert + ',' + '"' + @maxquarter + '","' + @quarter + '","' + @reportyear + '",@ratio output
END
-- Needs further development set @strSQLExec = @strSQLExec + ')'
-- BEGIN
-- INSERT INTO #ParsedList (stProcedure, ratio1, ratio2, ratio3, ratio4, ratio5 )
-- VALUES (@spName, ratios)
-- END
SET @stProcedure = RIGHT(@stProcedure, LEN(@stProcedure) - @posSP)
SET @posSP = CHARINDEX(',', @stProcedure)

END
END
END

--select * from #parsedList
drop table #parsedList

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 09:20:42

use output paramteres
create procedure myProc
...
@rtrnValue float(30) OUTPUT

go

dim @rtrnValue1 float(30)
exec myProc ..., @rtrnValue1 output

this way you get all 5 ratios and then you simply put them in a table
insert into #ParsedList(ratio1, ratio2, ...)
values (@rtrnValue1, @rtrnValue2, ...)

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-26 : 10:46:55

If the # of values is from 1-5, and the resultset you are returning has a fixed number of columns, then I would just use 5 parameters (leave them as NULL if there are less than 5 values to pass in). I would personally not pass in a CSV string and parse it in this case.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources