Create Variable Dynamically

[Moebius01]

I'm not sure if this is possible, but I need to create a set of variables dynamically within a WHILE loop. For example, if the loop needs to execute 5 times, it would create 5 new variables named @var1, @var2, etc... then set each one to a given value. If I use EXEC, the variable is only assigned within the scope of that batch and nowhere else. I'm not very familiar with sp_executesql, but I could not get it to work either. My initial code looked like this.

declare @loopcount int
declare @stmt varchar(1000)
set @loopcount = 1
WHILE @loopcount <= 5
BEGIN
   set @stmt = 'declare var' + cast(@loopcount as varchar(1)) + ' varchar(100) set @var' + cast(@loopcount as varchar(1)) + ' = ' + @loopcount
   exec (@stmt)
END

print @var1

This errors out as var1 is only in the scope of the exec batch (at least I'm assuming that's how it works). Is there a way to do something like this where the dynamically created variable would be accessible globally?

 

[donutman]

Why do you feel the need to do this...what problem are you trying to solve?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[checkai]

good question...if it's a dynamic rows problem...you can declare a table variable or some other method to accomplish this...

dlc

 

[Moebius01]

Actually, the idea was to be able to build an 8000+ char string without having to use a temp table to dump it in. It relates to my previous post on the Subquery grouping (donutman answered it). In this case, I need to pass a comma seperated list of applications to run a query against, then return a variable number of fields based on the list (ie.. 12 apps, = 14 fields, 24 apps = 26 fields, and so on). Due to the length of the subquery, after about 40 apps, I reach the 8000 character limit for a local variable. I have several clients with 40+ apps and a couple with around 400 non-sequential application numbers (ugly, ain't it?).

So, my idea was to have a variable created during each loop of the application numbers, then just execute them in sequence after all the strings are built. (exec @var1 + @var2 + @var3 etc...)

The original post was here.

http://www.tek-tips.com/viewthread.cfm?qid=1024029&page=2

I saw some ideas while searching about using a temp table with a text field to build, but I was afraid that would slow the query performance down too much, and I was having a little trouble understanding how it worked.

 

[donutman]

I suspect it would pay big dividends to rethink the overall strategy as it now sounds like the dynamic SQL that you had written is not to just create a query. You want to execute the dynamic SQL and dynamically execute the query it generates. That's not going to be efficient.
I don't understand the big picture so I can't help you. Want more help, then invest the time to explain the application more throughly.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Moebius01]

Ok, let me see if I can clarify this. What we're needing is a method to generate an effective cross-tab report with a variable number of fields returned based on the data passed in. The SP in its current revision:

ALTER  procedure DISP6_RPT 
    (@Orig char(7),
     @Switch char(15), 
     @Array varchar(1000), 
     @separator char(1) = ',') 
AS 
set nocount on 
declare @Applist varchar(1000)
set @Applist = @Array
declare @separator_position int
declare @array_value varchar(1000) 
declare @loopcount int
set @loopcount = 0
declare @sql varchar(8000)
set @sql = 'select count(*) as Summary, ' -- First part of SQL Statement.
set @array = @array + @separator 
while patindex('%' + @separator + '%' , @array) <> 0 -- Parse String for separator
     BEGIN 
          set @loopcount = @loopcount + 1
     	select @separator_position = patindex('%' + @separator + '%' , @array) 
     	select @array_value = left(@array, @separator_position - 1) 
     	--@array_value = current value in array loop
     	-- Build on to sql statement with sub queries.
     	set @sql = @sql + '(select count(*) from calldetail where orig_date = ' + @Orig + ' and switch = ' + rtrim(@Switch) + ' and applic_num = ' + @array_value + ' and call_type = 2 and disposition = 6) as App' + cast(@array_value as varchar(5)) + ','
     	select @array = stuff(@array, 1, @separator_position, '') 
     END 
--print @loopcount
-- Finish master sql statement.
set @sql = substring(@sql, 0, len(@sql)) + ' from calldetail where orig_date = ' + @Orig + ' and switch = ' + rtrim(@Switch) + ' and applic_num IN (' + @Applist + ') and call_type = 2 and disposition = 6'
exec (@sql)
print @sql -- show what we just created
set nocount off

So, when called dbo.DISP6_RPT 1050310, 9999, '320,350,374' I get

Summary App320 App350 App374
147 84 32 31

This works fine as long as the list of apps is not too large. Once I need to go beyond about 45 apps or so, the @SQL variable grows larger than 8000 characters.

So, my problem came with how to build a sql statement dynamically that could be larger than 8000. Does that make more sense?

 

[donutman]

What's the big picture?
The users want what?
How often do they want it?
Where does this variable number of input parameters come from?
The tables you have contain what?
How many rows?
What reporting tool do you have?
etc?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Moebius01]

Sorry was thinking within the scope of the current process.

It's used to generate a cross tab report on Trunk to Trunk transfers (our agent transferred the call elsewhere). The report looks much like the output from the query. Date, Summary of all apps passed, then total for each individual application.

The proc will be used by a custom built reporting process (co-worker designs it utilizing VBA mostly). The data output needs to be as specified (variable number of fields, etc...) The VBA app will connect to the DB and call the proc, passing it the Switch Number, Date, and the Application Numbers needed. The data returned from the query is used to populate an Excel doc with the numbers.

It will run once daily for each client/application grouping. Currently, the query takes less than a second to run for up to 40 apps. The Excel doc is output in about 2 seconds.

Source table (calldetail) contains a record for each call record on each of our switches (9 total). Each record will have a trans_type, call_type, and disposition, as well as application number, switch number, and orig_date stamp. (more fields, but these are the ones used for this report)

The table has rolling data for 90 days and contains approximately 2.5 million rows.

As-is, this will work for 95% of the reports we need to generate as most clients only have 1-5 applications. We do however have a few clients (who don't seem to know how to structure things) with 50+ applications. It's these latter cases where the problem occurs. Each subquery is 130 characters, so after about 45 applications or so, the entire @SQL variable grows larger than 8000 characters.

 

[donutman]

The crosstab data that you are after isn't really crosstab data. It appears to be just summary data that you want to appear in the same row as every call detail row that satisfies your Where clause. Since you are using Excel, why not just generate a rowset of the nature:
Select count(*) from CallDetail Where {whatever} Group By AppNo. Pivot that in Excel and you tag that onto the main query. No dynamic SQL needed.
I think you can further simplify the subqueries, because it looks like the AppNo is somewhat fixed by client. If so you can build a permanent table of Client AppNos or alternatively build a temporary table with all the possible AppNo's based upon the client No, thereby bypassing the CSV string input. Or are you saying that the report isn't necessarily for all AppNos that the client participates in.
I may not be making sense with regard to my references to the overall system, because you haven't explained exactly what your business is doing.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]