Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Concatenating Large Queries

Posted on 2013-11-15
5
Medium Priority
?
592 Views
Last Modified: 2013-11-15
Hi,

I have a very large query which I am trying to store in a variable which has been declared as NVARCHAR(MAX)

DECLARE @mySQL NVARCHAR(MAX)
DECLARE @finalSQL NVARCHAR(MAX), @SQLCommon NVARCHAR(4000)

Open in new window

Once I build individual queries in @mySQL and @SQLCommon, I combine them in @finalSQL

SELECT @finalSQL = CAST(CAST(@mySQL AS NVARCHAR(MAX)) + CAST(@SQLCommon AS NVARCHAR(MAX)) AS NVARCHAR(MAX))
PRINT CONVERT(NVARCHAR(MAX), @finalSQL)

Open in new window

As you can see, I have tried everything from Cast to Convert but when I try to print my query gets truncated.

PRINT CONVERT(NVARCHAR(MAX), @finalSQL)

Open in new window

Can someone pls help me here ?

Amit



Here is the complete code: thought it might help to understand the issue

DECLARE @mySQL NVARCHAR(MAX)
DECLARE @finalSQL NVARCHAR(MAX), @SQLCommon NVARCHAR(4000)

SET @SQLCommon = N'   INNER JOIN emp_Country emp_cntry ( NOLOCK ) ON bld_bld.CountryID = emp_cntry.CountryID
	LEFT OUTER JOIN dbo.ADR_Suburb (NOLOCK) adr_subrb ON bld_bld.SuburbID = adr_subrb.SuburbID
	LEFT OUTER JOIN dbo.ADR_SubDistrict (NOLOCK) adr_subdis ON adr_subrb.SubDistrictID = adr_subdis.SubDistrictID
	LEFT OUTER JOIN dbo.ADR_District (NOLOCK) adr_dis ON adr_subdis.DistrictID = adr_dis.DistrictID
	LEFT OUTER JOIN dbo.ADR_Region (NOLOCK) adr_reg ON adr_dis.RegionID = adr_reg.RegionID

	INNER JOIN dbo.StringToTable_fn(' + @intCountry + ') userCountry ON bld_bld.CountryID = CASE
																						WHEN ' + @intCountry + ' = '''' THEN bld_bld.CountryID
																						ELSE userCountry.Value
																					END
	INNER JOIN StringToTable_fn(' + @intRegion + ') userRegion ON adr_reg.RegionID =	CASE
																					WHEN ' + @intRegion + ' = '''' THEN adr_reg.RegionID
																					ELSE userRegion.Value
																				END
	INNER JOIN StringToTable_fn(' + @intCity + ') userCity ON bld_bld.SuburbID =	CASE
																				WHEN ' + @intCity + ' = '''' THEN bld_bld.SuburbID
																				ELSE userCity.Value
																			END
	WHERE 1=1 '


	SET @mySQL = N''
	
	SELECT @mySql = SUBSTRING(
		(SELECT 
					N' , ' + syscol.Name
							FROM Sys.Columns (NOLOCK) syscol
							WHERE Object_ID = (SELECT Object_ID FROM Sys.Tables (NOLOCK) where Name = @tblName)
								AND syscol.System_Type_ID NOT IN (34, 36, 43, 98, 240, 240, 240, 165, 173, 189, 241)
								AND syscol.is_identity = 0
							FOR XML PATH(''), Type).value('.', 'nvarchar(max)'), 3, 10000)

	SELECT @intCount = Count(*) from StringToTable_fn(@mysql) where value <> ''

	SELECT 

	@mySQL = N'SELECT ''' + @tblName + ''', CreatedBy, ModifiedBy, ((' +  
					SUBSTRING((SELECT 
						'+ CASE WHEN ' + @TblPrefix + syscol.Name + ' IS NULL OR ' + @TblPrefix + syscol.Name + ' = ' 
								+ CASE WHEN syscol.System_Type_ID IN (35, 99, 167, 175, 231, 239) THEN '''''' ELSE '0' END + ' THEN 0 ELSE 1 END ' 
					FROM Sys.Columns (NOLOCK) syscol
					WHERE Object_ID = (SELECT Object_ID FROM Sys.Tables (NOLOCK) where Name = @tblName)
						AND syscol.System_Type_ID NOT IN (34, 36, 43, 98, 240, 240, 240, 165, 173, 189, 241)
						AND syscol.is_identity = 0
					FOR XML PATH(''), Type
					).value('.', 'nvarchar(max)'), 3, 10000) + ') * 100.00 / ' + CAST(@intCount AS nVarchar(5)) + ' ) '
				+ ' AS myCount FROM ' + @TblLongName + ' ' --+ @SQLCommon

SELECT @finalSQL = CAST(CAST(@mySQL AS NVARCHAR(MAX)) + CAST(@SQLCommon AS NVARCHAR(MAX)) AS NVARCHAR(MAX))
PRINT CONVERT(NVARCHAR(MAX), @finalSQL)

Open in new window

0
Comment
Question by:AmitJain001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Author Comment

by:AmitJain001
ID: 39650818
Thx ValentinoV.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39651030
Don't use print but just SELECT @finalSQL to display it in the Output pane.

You may also need to split up that large string assignment into portions shorter than 4000 bytes.  Something like this:

SET @SQLCommon = N' <first portion of the query> '

SET @SQLCommon = @SQLCommon + N' <second portion of the query> '

...
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 2000 total points
ID: 39651047
Why you shouldn't use print:

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

Ref. PRINT (Transact-SQL)
0
 

Author Comment

by:AmitJain001
ID: 39652871
Issue Resolved. There were issues with String Concatenation and after making the Variable Datatype as nVarchar(Max) I was trying to Print the statement to check if the Concatenation worked or not. Not realizing that Print itself has size issues. Foolishly spent so much time on it.

Thanks Valentino for the tip. Now my query is working fine.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question