May 11, 2011 at 6:28 am
Dear all,
I'm trying to create a local variable in a stored procedure that consists of a long sql query. This sql query will be used to select all column names (table has more than 50 columns) and the data on one of the rows.
This requirement was discussed in http://www.sqlservercentral.com/Forums/Topic1095074-391-1.aspx previously.
Stored proc I'm trying to write is:
DECLARE @sql NVARCHAR(4000); // 4000 is max for nvarchar
SET @sql = 'SELECT ''' + STUFF((
SELECT '+'':' + name + ';''+CONVERT(VARCHAR(100), ' + name + ')'
FROM sys.columns
WHERE object_id = OBJECT_ID('boss..dealpending','U')
ORDER BY column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +
' FROM dealpending where dealid = 2005502';
PRINT @sql;
EXECUTE sp_executesql @sql;
This query is writing all column names to @sql variable but since it's size is 4000, it's cutting the query in between, like:
SELECT 'dealid;'+CONVERT(VARCHAR(100), dealid)+':dealsetid;'+....................+CONVERT(VARCHAR(100), dealsetid)+':dealsetnumber;'+CONVERT(VA
So, here's the question:
What else can I use to fit a longer query in @sql variable?
Thanks
May 11, 2011 at 6:53 am
cant you use nvarchar(max) meaning maximum storage size is 2^31-1 bytes?
May 11, 2011 at 7:18 am
For the time being, no I can't.
I replied to this at:
http://www.sqlservercentral.com/Forums/Topic1106818-391-1.aspx
Thank you.
May 11, 2011 at 8:20 am
Please don't create new posts to continue a topic. it just fragments discussions and makes it harder for other to learn from the solution. As you stated please direct all replies to the original thread here
_______________________________________________________________
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/
May 11, 2011 at 9:08 am
Sean Lange (5/11/2011)
Please don't create new posts to continue a topic. it just fragments discussions and makes it harder for other to learn from the solution. As you stated please direct all replies to the original thread here
Yes you are right. I'm generally careful about that but this time that thread was created 6 minutes before than mine so I haven't seen it while writing mine.
May 11, 2011 at 9:52 am
arun.sirpal (5/11/2011)
cant you use nvarchar(max) meaning maximum storage size is 2^31-1 bytes?
NVARCHAR(MAX) works. Thank you!
May 11, 2011 at 10:10 am
ilker.cikrikcili (5/11/2011)
arun.sirpal (5/11/2011)
cant you use nvarchar(max) meaning maximum storage size is 2^31-1 bytes?NVARCHAR(MAX) works. Thank you!
Glad you found a working solution. 😀
_______________________________________________________________
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply