Dynamic SQL for more than 4000 Bytes
-
Hi
Does any one have an idea how to dynamically execute t-sql statement which is above 4000 Bytes(nvarchar) ?
As an example I have view which is more than 4000 Bytes (as test column of syscomments takes 2 rows), also some more select statements get appended to it over a period of time. so at this time i have to drop older one and create new view. Since view defination is more than 4000 Bytes i can't use nvarchar to store sql statement
Regards
Shrikant
Regards
Shrikant Kulkarni -
create a view from the SQL statement and then execute the view
SQL = Scarcely Qualifies as a Language
-
Don't use sp_Execute. Just use EXEC...
EXEC (@SQL1+@SQL2+@SQL3....+@SQLn)
... and each of the variables can be VARCHAR(8000).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You can nest EXEC and sp_executesql
EXEC (N'EXEC sp_execuresql ' + @SQL1+ N', ' + @SQL2 + ', ' ...)
The query plan is then cached
You cab also use Varchar(MAX) and SQL 2005
-
Interesting... THAT allows you to use sp_ExecuteSQL with more than 4k bytes? I gotta try that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
would xp_execresultset work? Undocumented and Risky, but available.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply