[SQL Server] how to resolve a string that cannot exceed 8000

/* -- The method for resolving strings that cannot exceed 8000

It is often mentioned that when data is processed using the dynamic generation of SQL statements, the processing statements are too long and cannot be processed.
This issue is discussed below:
-- Producer build 2003.9 (reference please keep this information )--*/

/* -- Test environment

-- The system table syscolumns is used as the test data, and the xtype column and name column are used as the row fields to calculate the sum of colid and
-- Required results

Xtype filedname_1 fieldname_2... fieldname_n
----------------------------------------------------------
34 0 0... 1
--*/

/* -- General processing method (errors may occur due to the generated string benefit if the number of rows is not limited)
Set rowcount 10 -- Because syscolumns records are large, the generated string will overflow, so limit the number of rows

Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + N', '+ quotename ([name])
+ N' = sum (Case name when' + quotename ([name], ''')
+ N' then [colid] else 0 end )'
From (select distinct [name] from [syscolumns])

Set rowcount 0 -- cancel the limit
Exec (n' select [xtype] '+ @ s + N' from [syscolumns] group by [xtype]')

/* -- Problem
When the limit on the number of rows is not added, an error occurs because of the generated string.
--*/
--*/

/* -- Method 1. process multiple variables

-- Define a variable based on the query result (in actual processing, it should be an estimate of the number of variables required and a sufficient number of variables should be defined. Defining multiple variables does not affect the processing. The following defines one more variable)
-- Generate a temporary data processing table
Select id = identity (INT, 0, 1 ),
G = 0,
A = cast (n', '+ quotename ([name])
+ N' = sum (case [name] When n' + quotename (name, n '''')
+ N' then [colid] else 0 end )'
As nvarchar (4000 ))
Into # From syscolumns
Where Name> n''
Group by name

-- Group temporary table
Update a set G = ID/I
From # A, (select I = 3800/MAX (LEN (A) from #) B
Select max (G) + 1 as n' number of required variables 'from #

Declare @ 0 nvarchar (4000), @ 1 nvarchar (4000), @ 2 nvarchar (4000), @ 3 nvarchar (4000), @ 4 nvarchar (4000)
Select @ 0 = n'', @ 1 = n'', @ 2 = n'', @ 3 = n'', @ 4 = n''
Select
@ 0 = case G when 0 then @ 0 + A else @ 0 end,
@ 1 = case G when 1 then @ 1 + a else @ 1 end,
@ 2 = case G when 2 then @ 2 + A else @ 2 end,
@ 3 = case G when 3 then @ 3 + A else @ 3 end,
@ 4 = case G when 4 then @ 4 + A else @ 4 end
From #
Exec (N 'select xtype' + @ 0 + @ 1 + @ 2 + @ 3 + @ 4 + N' from syscolumns group by xtype ')
Drop table #

/* -- Method description

Advantage: relatively flexible. You only need to add a variable when the data volume is large. You do not need to change other parts.
Disadvantage: errors may occur if the data to be processed is estimated on its own.
--*/
--*/

/* -- Method 2. BCP + iSQL

-- This information is required because BCP + iSQL is used.
Declare @ servername sysname, @ username sysname, @ PWD sysname
Select @ servername = @ servername -- server name
, @ Username = n'' -- User Name
, @ Pwd = n'' -- Password

Declare @ tbname sysname, @ s nvarchar (4000)

-- Create a temporary data processing table
Set @ tbname = quotename (n' # temp _ '+ Cast (newid () as varchar (36 )))
Set @ s = n'create table' + @ tbname + '(a nvarchar (4000 ))
Insert into '+ @ tbname + N'
Select n '''create view'
+ Stuff (@ tbname, 2, 2, n '')
+ N'
Select [xtype]''
Union all
Select n'', ''+ quotename ([name]) +'' = sum (case [name] When n''
+ Quotename ([name], ''')
+ ''Then [colid] else 0 end )''
From (select distinct [name] from [syscolumns] Where name <> N ''xtype'')
Union all
Select n'' from [syscolumns] group by [xtype] '''
Exec (@ s)

-- Generate the file for creating the view. Note that the file C:/temp.txt is used.
Set @ s = n'bcp "'+ @ tbname + N'" out "C:/' + @ tbname + N'"/s "'
+ @ Servername + N' "/u" '+ @ username + N' "/P"' + @ PWD + N' "/W'
Exec master .. xp_mongoshell @ s, no_output

-- Call iSQL to generate a data processing view
Set @ s = n'osql/s "'+ @ servername
+ Case
When @ username = n' then N' "/E'
Else N' "/u" '+ @ username + N' "/P"' + @ PWD + N '"'
End
+ N'/D "'+ db_name () + N'"/I "C:/' + @ tbname + '"'
Exec master .. xp_mongoshell @ s, no_output

-- Delete temporary files
Set @ s = n' del "C:/'+ @ tbname + '"'
Exec master .. xp_mongoshell @ s, no_output

-- Call the view to display the processing result
Set @ s = n'drop table' + @ tbname + N'
Select * from '+ stuff (@ tbname, 2, 2, n'') + N'
Drop view' + stuff (@ tbname, 2, 2, n '')
Exec (@ s)

/* -- Method summary

Advantage: The program is automatically processed and there is no error in judgment.
Disadvantages: complicated, with many steps and error-prone, and requires certain operator Permissions
--*/
--*/

--/* -- Method 3. processing of multiple variables, combining the advantages of method 1 and 2, solves the problem of human judgment and variable addition in method 1, and eliminates method 2, which requires complicated permissions and procedures.
Declare @ sqlhead nvarchar (4000), @ sqlend nvarchar (4000)
, @ Sql1 nvarchar (4000), @ sql2 nvarchar (4000), @ sql3 nvarchar (4000), @ sql4 nvarchar (4000)
, @ I int, @ IC nvarchar (10)

-- Generate a temporary data processing table
Select id = identity (INT, 0, 1 ),
G = 0,
A = cast (n ','
+ Quotename ([name])
+ N' = sum (case [name] When N'
+ Quotename (name, n '''')
+ N' then [colid] else 0 end )'
As nvarchar (4000 ))
Into # From (
Select distinct name from [syscolumns] Where Name> n')

-- Group temporary table
Update a set @ I = ID/I, G = @ I
From # A, (select I = 3800/MAX (LEN (A) from #) B
Set @ Ic = @ I

-- Generate data processing statements
Select
@ Sqlhead = n ''''
+ Replace (n'select [xtype] ', n''', n '''''')
+ '''',
@ Sqlend = n ''''
+ Replace (n' from [syscolumns] group by [xtype] ', n'''', n '''''')
+ N '''',
@ Sql1 = n'', @ sql2 = n'', @ sql3 = n'', @ sql4 = n''
While @ IC> = 0
Select
@ Sql1 = n', @ '+ @ IC + N' nvarchar (4000)' + @ sql1,
@ Sql2 = n', @ '+ @ IC + N' = n''' + @ sql2,
@ Sql3 = n', @ '+ @ IC
+ N' = case G when' + @ IC
+ N'then @ '+ @ IC + N' + A else @' + @ IC
+ N'end' + @ sql3,
@ Sql4 = n' + @ '+ @ IC + @ sql4,
@ Ic = @ ic-1
Select
@ Sql1 = stuff (@ sql1, n ''),
@ Sql2 = stuff (@ sql2, n ''),
@ Sql3 = stuff (@ sql3, n ''),
@ Sql4 = stuff (@ sql4, n '')

-- Execute
Exec (N 'desc' + @ sql1 + N'
Select '+ @ sql2 + N'
Select '+ @ sql3 + N' from #
Exec (n' + @ sqlhead + N' + '+ @ sql4 + N' + @ sqlend + N ')')
-- Delete a temporary table
Drop table #

/* -- Method summary
 
Summarizes the advantages of the first two methods and automatically determines the number of variables to be processed
--*/
--*/