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.
| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2011-06-29 : 17:24:28
|
| I have huge sql string to be passed to the sp_executesql.I did define the @sqlstring as nvarchar(max)but it does not fit the entire sql statment when I use PRINT @sqlstring to see the results,I only see half of the statment.So I decided to declare a second variable @sqlstring1 as nvarchar(max).How do I pass the both to the sp_executesql.The reason for using sp_executesql is because I am passing different parameter to the sql statment each time.sp_executesql @sqlstring @sqlstring1Is there a better way of doing this, I dont want to use EXEC() but want to accomplish this using the sp_executesql |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-29 : 19:35:56
|
Although you can pass parameters, I don't think you can pass the statement itself in two chunks. Regardless, that you see only part of the sql when you use print command may be an artifact of SSMS. You can change how many characters are displayed in SSMS from Tools->Options->Query Results -> Results To Grid dialog.Also, you can use LEN or DATALENGTH function to see the length of your SQL statement. For example:DECLARE @x NVARCHAR(MAX);SET @x = N'select len('''+REPLICATE(CAST('a' AS VARCHAR(MAX)),10000) + ''')';SELECT LEN(@x);exec sp_executesql @x; |
 |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2011-06-30 : 10:11:12
|
| Thanks Sunita, yes the character length is about 16,000 +. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-07-01 : 07:25:25
|
| Hi.I ran into this problem a few times. In the end I wrote a sp that takes the string and just prints it *line by line*. (splitting on endline tokens (CHAR(13)CHAR(10)). (I think I can never remember if it's carriage return / line feed or line feed / carriage return).If it's only debug that you are using this for then that's all you need.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
sp_executesql