Building dynamic parameters - 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)

Building dynamic parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-25 : 07:17:06

daniel.hammerberg@gmail.com writes "I have been looking around the web and msdn for the past day or so and couldn't find a definitive answer to my problem. I am calling a nested stored procedure that will insert a variable number of records into a table. I want to build a loop that will dynamically create the parameter values as the counter increments.

For instance, instead of


IF @var1 IS NOT NULL
    EXEC procTestInsert @foo = @var1

IF @var2 IS NOT NULL
    EXEC procTestInsert @foo = @var2

IF @var2 IS NOT NULL
    EXEC procTestInsert @foo = @var2

..... and so on

I would like to buld the parameters dynamically and loop through the variables.


SET @counter = 1
WHILE @counter <= numRecords
BEGIN
    IF @var + @counter IS NOT NULL
    EXEC procTestInsert
        @foo = @var + @counter
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-25 : 08:32:09

You could probably do it without the loop by structuring your output to match table definition like this:


--structure output of outer proc to match target table and use the output as the insert values directly
if object_id('proc1') > 0 drop proc proc1
GO
--generate a random number of FOOs between 1 and 10
create proc proc1 as 
set nocount on
declare @tb table (foo int identity(1,1), col1 char(1))
declare @recCount int
Select @recCount = floor(rand() * 10) + 1
set rowcount @recCount
insert @tb (col1)
select 'a' from sysObjects
set rowcount 0

Select foo from @tb

GO
--create a Foo table 
if object_id('foos') > 0 drop table foos
GO
create table foos (foo int)
GO

--Insert into FOOs table as many foo records as are returned from proc1
insert foos
exec proc1

--look at insert results
Select * from foos
return
--============================================================================
--OR- to answer your question:
/*
declare @counter int
SET @counter = 1
WHILE @counter <= numRecords
BEGIN
	IF @var + @counter IS NOT NULL
		EXEC ('procTestInsert @foo = @var' + convert(varchar,@counter)
END
*/

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources