Dynamic SQL > 4000 Characters Issue

11 Aug 2006 4:11 PM

RitaG

Hello.

I have a Stored Procedure that has a required argument passed to it. This
argument is then used to create dynamic SQL which is then exectued via the
EXEC
statement. I'm using an nVarChar(4000) since nVarChar has a limitation of
4000.
However, my SP has > 4000 characters.

What can I do to overcome this?

I thought about using @SQL1 and @SQL2 but wasn't sure how to execute this.
Exec (@SQL1) + (@SQL2)?

Any suggestion will ge greatly appreciated :-).

Thanks,
Rita

11 Aug 2006 4:22 PM

Vern Rabe

In SQL 2000,

EXEC (@SQL1 + @SQL);

HTH
Vern Rabe

Show quote

"RitaG" wrote:

> Hello.
>
> I have a Stored Procedure that has a required argument passed to it. This
> argument is then used to create dynamic SQL which is then exectued via the
> EXEC
> statement. I'm using an nVarChar(4000) since nVarChar has a limitation of
> 4000.
> However, my SP has > 4000 characters.
>
> What can I do to overcome this?
>
> I thought about using @SQL1 and @SQL2 but wasn't sure how to execute this.
> Exec (@SQL1) + (@SQL2)?
>
> Any suggestion will ge greatly appreciated :-).
>
> Thanks,
> Rita

11 Aug 2006 6:50 PM

RitaG

Hi Vern.
I landed up using the VarChar(8000) which worked for me.
Thanks for your response.
Rita

Show quote

"Vern Rabe" wrote:

> In SQL 2000,
>
> EXEC (@SQL1 + @SQL);
>
> HTH
> Vern Rabe
>
> "RitaG" wrote:
>
> > Hello.
> >
> > I have a Stored Procedure that has a required argument passed to it. This
> > argument is then used to create dynamic SQL which is then exectued via the
> > EXEC
> > statement. I'm using an nVarChar(4000) since nVarChar has a limitation of
> > 4000.
> > However, my SP has > 4000 characters.
> >
> > What can I do to overcome this?
> >
> > I thought about using @SQL1 and @SQL2 but wasn't sure how to execute this.
> > Exec (@SQL1) + (@SQL2)?
> >
> > Any suggestion will ge greatly appreciated :-).
> >
> > Thanks,
> > Rita

11 Aug 2006 4:52 PM

Reg Besseling

Hi Rita

Do you need the unicode cahr set ? if not use the varchar type which has a
8000 limir

HTH

--
Regards

Reg Besseling

Show quote

"RitaG" wrote:

> Hello.
>
> I have a Stored Procedure that has a required argument passed to it. This
> argument is then used to create dynamic SQL which is then exectued via the
> EXEC
> statement. I'm using an nVarChar(4000) since nVarChar has a limitation of
> 4000.
> However, my SP has > 4000 characters.
>
> What can I do to overcome this?
>
> I thought about using @SQL1 and @SQL2 but wasn't sure how to execute this.
> Exec (@SQL1) + (@SQL2)?
>
> Any suggestion will ge greatly appreciated :-).
>
> Thanks,
> Rita

11 Aug 2006 6:49 PM

RitaG

Thanks Reg. I tried the VarChar(8000) and that worked fine.
For some reason I thought I was restricted to nVarChar.

Show quote

"Reg Besseling" wrote:

> Hi Rita
>
> Do you need the unicode cahr set ? if not use the varchar type which has a
> 8000 limir
>
> HTH
> --
> Regards
>
> Reg Besseling
>
>
> "RitaG" wrote:
>
> > Hello.
> >
> > I have a Stored Procedure that has a required argument passed to it. This
> > argument is then used to create dynamic SQL which is then exectued via the
> > EXEC
> > statement. I'm using an nVarChar(4000) since nVarChar has a limitation of
> > 4000.
> > However, my SP has > 4000 characters.
> >
> > What can I do to overcome this?
> >
> > I thought about using @SQL1 and @SQL2 but wasn't sure how to execute this.
> > Exec (@SQL1) + (@SQL2)?
> >
> > Any suggestion will ge greatly appreciated :-).
> >
> > Thanks,
> > Rita

12 Aug 2006 11:37 PM

Mike C#

sp_executesql requires NVARCHAR. EXEC by itself can use VARCHAR.

--
================================
Mike C#
Not affiliated with MSFT

Show quote

"RitaG" <Ri***@discussions.microsoft.com> wrote in message
news:1F292C51-FBD1-47FD-BE3E-8C6658B2C75A@microsoft.com...
> Thanks Reg. I tried the VarChar(8000) and that worked fine.
> For some reason I thought I was restricted to nVarChar.
>
> "Reg Besseling" wrote:
>
>> Hi Rita
>>
>> Do you need the unicode cahr set ? if not use the varchar type which has
>> a
>> 8000 limir
>>
>> HTH
>> --
>> Regards
>>
>> Reg Besseling
>>
>>
>> "RitaG" wrote:
>>
>> > Hello.
>> >
>> > I have a Stored Procedure that has a required argument passed to it.
>> > This
>> > argument is then used to create dynamic SQL which is then exectued via
>> > the
>> > EXEC
>> > statement. I'm using an nVarChar(4000) since nVarChar has a limitation
>> > of
>> > 4000.
>> > However, my SP has > 4000 characters.
>> >
>> > What can I do to overcome this?
>> >
>> > I thought about using @SQL1 and @SQL2 but wasn't sure how to execute
>> > this.
>> > Exec (@SQL1) + (@SQL2)?
>> >
>> > Any suggestion will ge greatly appreciated :-).
>> >
>> > Thanks,
>> > Rita