Building dynamic variables - 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

Transact-SQL (2000)

Building dynamic variables

Author

Topic

paolo.balbarini
Starting Member

2 Posts

Posted - 2003-03-14 : 03:52:03

My stored procedure is:

CREATE PROCEDURE Stp_gnetExecSqlText

@colonna varchar(500),
@tabella varchar(500)
AS

DECLARE @lunghezza int
DECLARE @resto int
DECLARE @chunk int
DECLARE @nstep int
DECLARE @index int
DECLARE @inizio int
DECLARE @str_resto varchar(8000)

DECLARE @v1 varchar(8000), @v2 varchar (8000), @v3 varchar(8000), @v4 varchar(8000), @v5 varchar(8000), @v6 varchar(8000), @v7 varchar(8000), @v8 varchar(8000), @v9 varchar(8000), ......., @v100 varchar(8000)

set @index = 1
set @chunk = 8000

'-- I've a table with a text data type column
EXEC("SELECT DATALENGTH(" + @colonna + ") AS lung INTO ##tmplung from " + @tabella)
SELECT @lunghezza = lung from ##tmplung
DROP TABLE ##tmplung

SET @nstep = @lunghezza/@chunk
SET @resto = @lunghezza - @nstep*@chunk

EXEC("SELECT TOP 1 SUBSTRING(" + @colonna + ", 1, " + @resto + " ) AS substr INTO ##tmpchunk from " + @tabella)
SELECT @str_resto = substr from ##tmpchunk
DROP TABLE ##tmpchunk

SET @inizio = @resto + 1

WHILE (@index <= @nstep) BEGIN

EXEC("SELECT TOP 1 SUBSTRING(" + @colonna + "," + @inizio + "," + @chunk + " ) AS substr INTO ##tmpchunk from " + @tabella)

IF @index = 1
SELECT @v1 = substr from ##tmpchunk
IF @index = 2
SELECT @v2 = substr from ##tmpchunk
IF @index = 3
SELECT @v3 = substr from ##tmpchunk
IF @index = 4
SELECT @v4 = substr from ##tmpchunk
IF @index = 5
SELECT @v5 = substr from ##tmpchunk
IF @index = 6
SELECT @v6 = substr from ##tmpchunk
.
.
.
IF @index = 100
SELECT @v100 = substr from ##tmpchunk

DROP TABLE ##tmpchunk
SET @inizio = @inizio + @chunk
SET @index = @index + 1

END -- end while

EXEC (@str_resto + @v1 + @v2 + @v3 + @v4 + @v5 + @v6 +... + @v100)

My question is: it's possible, in while statements, builting variables like 'select @v' + @index and not write @v1,@v2, ecc .. ??????
Any suggest or ideas?

Edited by - paolo.balbarini on 03/14/2003 05:55:40

Edited by - paolo.balbarini on 03/14/2003 07:00:43

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-14 : 05:47:29

I dont understand your question.
Still, Dynamic Query will do the trick.
Is this you want.
declare @i int, @strVar varchar(10)
set @i=1
while (@i<5)
begin
set @strVar = '@var'+convert(char,@i)
print 'variable:'+@strVar
exec ('declare ' +@strVar+ ' varchar(10)')
set @strvar = Convert(char,@i)
print 'assigned:'+@strvar
set @i=@i+1
end

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-14 : 07:30:46

paolo --

what are you trying to do? You are asking for an array ... but SQL has no need for arrays, you have tables and table variables at your disposal -- which are even better and more powerful !

Please give us some sample data and expected results and I'm confident we can re-write this for you using only 1 or 2 SQL statements ...

- Jeff

paolo.balbarini
Starting Member

2 Posts

Posted - 2003-03-14 : 08:39:43

quote:

paolo --

what are you trying to do? You are asking for an array ... but SQL has no need for arrays, you have tables and table variables at your disposal -- which are even better and more powerful !

Please give us some sample data and expected results and I'm confident we can re-write this for you using only 1 or 2 SQL statements ...

- Jeff

Ok, I try to explain my problem.
I've to execute, inside a stored procedure, a query that is stored in a column of a temp table (text data type).
The problem is that the query is composed of 20000 characters or more and i can't store it in a variable inside the stored procedure ( max varchar(8000) ). So i've thought to split it in N pieces and execute it like a sum EXEC (@v1 +@v2 + ... + @vn) . It's the right way?? thanks

Edited by - paolo.balbarini on 03/14/2003 08:41:01

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources