Solved

Concatenating Large Queries

Posted on 2013-11-15
5
532 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

20 Experts available now in Live!

Get 1:1 Help Now