Runtime sql execution using sp_executesql

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

SQL Server Development (2000)

Runtime sql execution using sp_executesql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-03 : 08:37:17

Nitin writes " Hello All,

Is there any way to pass an argument to sp_executesql
procedure which is more than 4000 characters?

I am using sp_execute proc. to execute sql statement
having character size more than 5000 characters.

sp_executesql accepts arguments of data type NCHAR, NVARCHAR
and NTEXT. In this NVARCHAR max. size is 4000 characters.

Could we use NTXT in above case? If yes how to use it?

Thanks & Regards

Nitin"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-03 : 08:50:10

yes...upto 8000 with CHAR, VARCHAR ...the 'n' versions halve the data that the variables can hold....and are only needed for 'internationalised' applications....particularly the asian/arabic markets

re ntext.....split it into multiple text variables....pass in the multiples and combine inside the sp's back into one large variable....

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-03 : 09:45:57

sp_executesql only accepts unicode Andrew so the limit is 4000. You might want to look at using osql though. Better yet, just use a stored procedure and stop using dynamic sql.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-04 : 07:19:28

If you must you can use xp_execresultset to execute statements in a table containing as many rows as you like.
Edit: This is undocumented. Here is an example:


create table ##tt (id int identity, cmd varchar(255))
insert ##tt (cmd) select 'select newid() num '

while @@rowcount < 500
   insert ##tt (cmd) select 'union all select newid() ' from ##tt

EXEC master..xp_execresultset N'Select cmd from ##tt',N'yourdbname'

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-04 : 10:12:55

Thank You Ken

!!!,
that was the nicest undocumented xp that I ran across in many a day !
And only googled at 288 hits ( quite undocumented ).

For the record
Another way is to use EXEC()
DECLARE @s1 VARCHAR(8000), @s2 VARCHAR(8000)
....
EXEC( @s1 + @s2 )

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-06 : 07:02:57

you can pass ntext to a sproc that will issue the sp_executesql command. just be sure to process the parameter value before passing to the sproc since you can't locally declare ntext data types.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources