Solved

Concatenating Large Queries

Posted on 2013-11-15
5
550 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
  • 2
  • 2
5 Comments
 

Author Comment

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

Accepted Solution

by:
ValentinoV earned 500 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now