Concatenating Large Queries

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

AmitJain001Asked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
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
 
AmitJain001Author Commented:
Thx ValentinoV.
0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
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
 
AmitJain001Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.