Variable has a limitation

Question

Variable has a limitation

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
August 21, 2002 at 6:27 pm

#79561

Is there any way to pass more than 8000 character in a variable.
for example.
@exec varchar(8000)
I want to store more than 8000 character in the variable @exec.
thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

No not really, however I posted previously a way to use a temp table or table variable with a single text datatype field in it so you can use it as a psuedo variable. If you cannot find it let me know and I will try to dig up the copy I kept.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Thanks for your reply.

I have created Stored Procedure which has a parameter and can accespt more than 8000 character.

for example

StoreProcedure name ('tt,tt1,tt2,tt3')

the paramter values is coming from ASP program and this value may exceed more than 8000 character.

I did not find, can u please send me the syntax of using table variable.

thanks.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Sorry, if you are trying to pass characters into a stored procedure via variable you can use a variable of text type for input and output in an SP. If this is not what you mean can you plase break down what it is you are trying to accomplish?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

let's say there is a SP called Get_executive_id which has one parameter called exec_name varchar(8000) and this SP returns record set of executive ID.

for example.

get_executive_id @exec_name

I am getting an input string from ASP program like this.

@exec_name='bob,rob,tam,sam,'

this string is more than 8000 character .

Iam storing this string in a temperory table and then join with exec_master table to get executive id in a record set.

As we know that varchar cannot accept more than 8000 character so i decided to breakup one parameter into two so that one parameter cannot accept more than 8000 character.

We cannot use text data type as a input variable in SP because we cannot store text data in a local variable.

I heard that in Oracle varchar storage is more than that.

Is there any better way to store one big string in a variable so that I should not break up one parameter into two

Thanks f