June 29, 2011 at 3:25 pm
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 @sqlstring1
Is there a better way of doing this, I dont want to use EXEC() but want to accomplish this using the sp_executesql
June 29, 2011 at 3:33 pm
The statement fits in nvarchar(max) OK, you just can't see all of it using the PRINT command.
If you really need to print the entire statement, you can use the SUBSTRING function to print one part at a time.
June 29, 2011 at 3:34 pm
There is a limit in the output of a query in SSMS. Just because you can't see it doesn't mean it is not in your variable. Tools -> Options -> Query Results -> SQL Server -> Results to Text.
The setting for max characters displayed in each column. You can increase the amount of text displayed here. My guess is your variable is fine and you should just execute it. If you really want to know what is in it. Take your variable and insert it to a persistent table and use something like .net to select that value into a string so you can see what the full contents are.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2011 at 3:35 pm
Hi michael, thanks for your prompt response. can you show me how you use substring to accomplish this
June 29, 2011 at 3:50 pm
hi sean, i did try your advise and copied over my query to a new window but still only see half of it.
June 30, 2011 at 6:58 am
Your string really is still in the variable you just can't see it. You can check the length of it with
select datalength(@sqlstring)
Here is an example of what Michael was talking about.
select SUBSTRING(@sqlstring, 0, 4000), SUBSTRING(@sqlstring, 4000, 4000) --continue until you have it all
Just get pieces of it and put it back together so you can view the whole thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2011 at 8:11 am
Thanks Sean, I checked the character length and it is about 16,000 +.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply