Solved

Concatenating Large Queries

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

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

687 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