Syntax for dynamic variable declarations

[Bygbobbo]

I am trying to dynamically create variables and set them. Does anyone know the syntax.

declare @loop int
set @loop = 4

WHILE (@loop > 0)
BEGIN
declare @sql+convert(varchar(1000),@loop) varchar(8000)
set @sql+convert(varchar(1000),@loop) = 'select * from table'
set @loop = @loop - 1
END

Exec(@sql4+@sql3+@sql2+@sql1)

thanks in advance,
bygs

 

[ClaireHCM]

Ah~~

Maybe you should directly explain what you want,instead of posting a query which is not understandable.Sorry to say that,but your syntax is totally wrong.

Could you explain what you need instead~~

 

[Bygbobbo]

I am trying to dynamically create a query that is greater than 8000 characters, then execute it.

The only thing I can think of is create a new variable when my statement reaches 8000 characters then pass the remaining characters to new variables.

Then execute the N number of variables within an exec:

exec (@sql1 + @sql2 +@sqlN...)

 

[Bygbobbo]

no offense ClarieHsu but I clearly state that initially, get off your high horse.

Im still working on making the body portion dynamic...

Mind you this is just for concept, not actully what I am using it for.

I was trying to get something like this with better syntax:

declare @c int,
@d int,
@dyn varchar(8000),
@declarations varchar(8000),
@sets varchar(8000),
@body varchar(8000)
set @d = 300
set @c = @d --you can set this to a higher number
set @dyn=''
set @declarations=''
set @sets = ''

WHILE(@c>0)
begin
set @dyn = @dyn + ', @s_'+convert(varchar(1000),@c)+' varchar(8000)'
set @c=@c-1
end

set @declarations = 'declare @l int, @c int,@lc int, @sql_a varchar(8000)'+@dyn

set @dyn=''
WHILE(@d>0)
begin
set @dyn = @dyn + 'set @s_'+convert(varchar(1000),@d)+'='''' '
set @d=@d-1
end
set @sets = 'set @c ='+convert(varchar(1000),@d)+' ' +@dyn
---------------------------declarations and sets built----------------------------------------------
-----build body
set @body = 'set @l = (select count(*) from test) set @lc = 0 WHILE (@l > 0)
BEGIN
set @sql_a = arc_telution_conv.dbo.UDF_create_qry(''select * from test '')
if(len(@s_1) + len(@sql_a) < 8000)
BEGIN
set @s_1 = @s_1 + @sql_a
set @l = @l - 1
set @lc = 1
END
else
BEGIN
if(len(@s_2) + len(@sql_a) < 8000)
BEGIN
set @s_2 = @s_2 + @sql_a
set @l = @l - 1
set @lc = 2
END
END
END'



Exec(@declarations + @sets + @body + ' Exec(@s_1 + @s_2)')

Anything can be done with a little imagination,

Bygs

 

[ClaireHCM]

I can only say it cant be done,do a small test


(1)query this and run
select len(@declarations + @sets + @body + ' Exec(@s_1 + @s_2)')
----return 7999

(2)
select len(@declarations)+len(@sets)+@body
----return 11102

It's not the thing about whether the syntax is right or wrong.It's the string length.No matter how you correct the syntax.QA cant finish your task,that's the thing!!

--I only sit inside the Car

 

[Bygbobbo]

Hmmm....

This was taken from microsoft's documentation:

Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.

Although each [N] 'tsql_string' or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:

USE master EXEC (&quot;USE pubs&quot SELECT * FROM authors
------------------------------------------------------------

Im not poking at you, I think maybe your taking this personally. If you read what I just posted carefully, it will show that the test you just posted is wrong.

Like I said I was looking for better syntax, not your opinion.

When I totalled everything the numbers where exact.

Innovative thinkers always have their ideas stolen....