Wsrtjtyk

I am having problem with dynamic sql query.

Declare sql query variable with nvarchar(max) and building dynamic query and then running it with using sq_executesql.

Please suggest if you have any solution for this.

Below are my code snippet:

DECLARE @count int, @sql nvarchar(max),@where nvarchar(max)

SELECT @count = 2, @where=' Where 1 = 1 '



set @sql = '

SELECT top 2

' + cast(@count as NVARCHAR) + ' as [Count],

Name,

1 AS Column1,2 AS Column2,3 AS Column3,4 AS Column4,5 AS Column5,6 AS Column6,7 AS Column7,8 AS Column8,9 AS Column9,10 AS Column10,11 AS Column11,12 AS Column12,13 AS Column13,14 AS Column14,15 AS Column15,16 AS Column16,17 AS Column17,18 AS Column18,19 AS Column19,20 AS Column20,21 AS Column21,22 AS Column22,23 AS Column23,24 AS Column24,25 AS Column25,26 AS Column26,27 AS Column27,28 AS Column28,29 AS Column29,30 AS Column30,31 AS Column31,32 AS Column32,33 AS Column33,34 AS Column34,35 AS Column35,36 AS Column36,37 AS Column37,38 AS Column38,39 AS Column39,40 AS Column40,41 AS Column41,42 AS Column42,43 AS Column43,44 AS Column44,45 AS Column45,46 AS Column46,47 AS Column47,48 AS Column48,49 AS Column49,50 AS Column50,51 AS Column51,52 AS Column52,53 AS Column53,54 AS Column54,55 AS Column55,56 AS Column56,57 AS Column57,58 AS Column58,59 AS Column59,60 AS Column60,61 AS Column61,62 AS Column62,63 AS Column63,64 AS Column64,65 AS Column65,66 AS Column66,67 AS Column67,68 AS Column68,69 AS Column69,70 AS Column70,71 AS Column71,72 AS Column72,73 AS Column73,74 AS Column74,75 AS Column75,76 AS Column76,77 AS Column77,78 AS Column78,79 AS Column79,80 AS Column80,81 AS Column81,82 AS Column82,83 AS Column83,84 AS Column84,85 AS Column85,86 AS Column86,87 AS Column87,88 AS Column88,89 AS Column89,90 AS Column90,91 AS Column91,92 AS Column92,93 AS Column93,94 AS Column94,95 AS Column95,96 AS Column96,97 AS Column97,98 AS Column98,99 AS Column99,100 AS Column100,101 AS Column101,102 AS Column102,103 AS Column103,104 AS Column104,105 AS Column105,106 AS Column106,107 AS Column107,108 AS Column108,109 AS Column109,110 AS Column110,111 AS Column111,112 AS Column112,113 AS Column113,114 AS Column114,115 AS Column115,116 AS Column116,117 AS Column117,118 AS Column118,119 AS Column119,120 AS Column120,121 AS Column121,122 AS Column122,123 AS Column123,124 AS Column124,125 AS Column125,126 AS Column126,127 AS Column127,128 AS Column128,129 AS Column129,130 AS Column130,131 AS Column131,132 AS Column132,133 AS Column133,134 AS Column134,135 AS Column135,136 AS Column136,137 AS Column137,138 AS Column138,139 AS Column139,140 AS Column140,141 AS Column141,142 AS Column142,143 AS Column143,144 AS Column144,145 AS Column145,146 AS Column146,147 AS Column147,148 AS Column148,149 AS Column149,150 AS Column150,151 AS Column151,152 AS Column152,153 AS Column153,154 AS Column154,155 AS Column155,156 AS Column156,157 AS Column157,158 AS Column158,159 AS Column159,160 AS Column160,161 AS Column161,162 AS Column162,163 AS Column163,164 AS Column164,165 AS Column165,166 AS Column166,167 AS Column167,168 AS Column168,169 AS Column169,170 AS Column170,171 AS Column171,172 AS Column172,173 AS Column173,174 AS Column174,175 AS Column175,176 AS Column176,177 AS Column177,178 AS Column178,179 AS Column179,180 AS Column180,181 AS Column181,182 AS Column182,183 AS Column183,184 AS Column184,185 AS Column185,186 AS Column186,187 AS Column187,188 AS Column188,189 AS Column189,190 AS Column190,191 AS Column191,192 AS Column192,193 AS Column193,194 AS Column194,195 AS Column195,196 AS Column196,197 AS Column197,198 AS Column198,199 AS Column199,200 AS Column200,201 AS Column201,202 AS Column202,203 AS Column203,204 AS Column204,205 AS Column205,206 AS Column206,207 AS Column207,208 AS Column208,209 AS Column209,210 AS Column210,211 AS Column211,212 AS Column212,213 AS Column213,214 AS Column214,215 AS Column215,216 AS Column216,217 AS Column217,218 AS Column218,219 AS Column219,220 AS Column220,221 AS Column221,222 AS Column222,223 AS Column223,224 AS Column224,225 AS Column225,226 AS Column226,227 AS Column227,228 AS Column228,229 AS Column229,230 AS Column230,231 AS Column231,232 AS Column232,233 AS Column233,234 AS Column234,235 AS Column235,236 AS Column236,237 AS Column237,238 AS Column238,239 AS Column239,240 AS Column240,241 AS Column241,242 AS Column242,243 AS Column243,244 AS Column244,245 AS Column245,246 AS Column246,247 AS Column247,248 AS Column248,249 AS Column249,250 AS Column250,251 AS Column251,252 AS Column252,253 AS Column253,254 AS Column254,255 AS Column255,256 AS Column256,257 AS Column257,258 AS Column258,259 AS Column259,260 AS Column260

FROM Sys.Objects

 ' + @where + ' AND  2 = 2 '





SELECT LEN(@sql),@sql



EXEC sp_executesql @sql

You need to explicit cast your large string to NVARCHAR(MAX) like this:

DECLARE @count int, @sql nvarchar(max),@where nvarchar(max)

SELECT @count = 2, @where=' Where 1 = 1 '



set @sql = '

SELECT top 2

' + cast(@count as NVARCHAR) + CAST(' as [Count],

Name,

1 AS Column1,2 AS Column2,3 AS Column3,4 AS Column4,5 AS Column5,6 AS Column6,7 AS Column7,8 AS Column8,9 AS Column9,10 AS Column10,11 AS Column11,12 AS Column12,13 AS Column13,14 AS Column14,15 AS Column15,16 AS Column16,17 AS Column17,18 AS Column18,19 AS Column19,20 AS Column20,21 AS Column21,22 AS Column22,23 AS Column23,24 AS Column24,25 AS Column25,26 AS Column26,27 AS Column27,28 AS Column28,29 AS Column29,30 AS Column30,31 AS Column31,32 AS Column32,33 AS Column33,34 AS Column34,35 AS Column35,36 AS Column36,37 AS Column37,38 AS Column38,39 AS Column39,40 AS Column40,41 AS Column41,42 AS Column42,43 AS Column43,44 AS Column44,45 AS Column45,46 AS Column46,47 AS Column47,48 AS Column48,49 AS Column49,50 AS Column50,51 AS Column51,52 AS Column52,53 AS Column53,54 AS Column54,55 AS Column55,56 AS Column56,57 AS Column57,58 AS Column58,59 AS Column59,60 AS Column60,61 AS Column61,62 AS Column62,63 AS Column63,64 AS Column64,65 AS Column65,66 AS Column66,67 AS Column67,68 AS Column68,69 AS Column69,70 AS Column70,71 AS Column71,72 AS Column72,73 AS Column73,74 AS Column74,75 AS Column75,76 AS Column76,77 AS Column77,78 AS Column78,79 AS Column79,80 AS Column80,81 AS Column81,82 AS Column82,83 AS Column83,84 AS Column84,85 AS Column85,86 AS Column86,87 AS Column87,88 AS Column88,89 AS Column89,90 AS Column90,91 AS Column91,92 AS Column92,93 AS Column93,94 AS Column94,95 AS Column95,96 AS Column96,97 AS Column97,98 AS Column98,99 AS Column99,100 AS Column100,101 AS Column101,102 AS Column102,103 AS Column103,104 AS Column104,105 AS Column105,106 AS Column106,107 AS Column107,108 AS Column108,109 AS Column109,110 AS Column110,111 AS Column111,112 AS Column112,113 AS Column113,114 AS Column114,115 AS Column115,116 AS Column116,117 AS Column117,118 AS Column118,119 AS Column119,120 AS Column120,121 AS Column121,122 AS Column122,123 AS Column123,124 AS Column124,125 AS Column125,126 AS Column126,127 AS Column127,128 AS Column128,129 AS Column129,130 AS Column130,131 AS Column131,132 AS Column132,133 AS Column133,134 AS Column134,135 AS Column135,136 AS Column136,137 AS Column137,138 AS Column138,139 AS Column139,140 AS Column140,141 AS Column141,142 AS Column142,143 AS Column143,144 AS Column144,145 AS Column145,146 AS Column146,147 AS Column147,148 AS Column148,149 AS Column149,150 AS Column150,151 AS Column151,152 AS Column152,153 AS Column153,154 AS Column154,155 AS Column155,156 AS Column156,157 AS Column157,158 AS Column158,159 AS Column159,160 AS Column160,161 AS Column161,162 AS Column162,163 AS Column163,164 AS Column164,165 AS Column165,166 AS Column166,167 AS Column167,168 AS Column168,169 AS Column169,170 AS Column170,171 AS Column171,172 AS Column172,173 AS Column173,174 AS Column174,175 AS Column175,176 AS Column176,177 AS Column177,178 AS Column178,179 AS Column179,180 AS Column180,181 AS Column181,182 AS Column182,183 AS Column183,184 AS Column184,185 AS Column185,186 AS Column186,187 AS Column187,188 AS Column188,189 AS Column189,190 AS Column190,191 AS Column191,192 AS Column192,193 AS Column193,194 AS Column194,195 AS Column195,196 AS Column196,197 AS Column197,198 AS Column198,199 AS Column199,200 AS Column200,201 AS Column201,202 AS Column202,203 AS Column203,204 AS Column204,205 AS Column205,206 AS Column206,207 AS Column207,208 AS Column208,209 AS Column209,210 AS Column210,211 AS Column211,212 AS Column212,213 AS Column213,214 AS Column214,215 AS Column215,216 AS Column216,217 AS Column217,218 AS Column218,219 AS Column219,220 AS Column220,221 AS Column221,222 AS Column222,223 AS Column223,224 AS Column224,225 AS Column225,226 AS Column226,227 AS Column227,228 AS Column228,229 AS Column229,230 AS Column230,231 AS Column231,232 AS Column232,233 AS Column233,234 AS Column234,235 AS Column235,236 AS Column236,237 AS Column237,238 AS Column238,239 AS Column239,240 AS Column240,241 AS Column241,242 AS Column242,243 AS Column243,244 AS Column244,245 AS Column245,246 AS Column246,247 AS Column247,248 AS Column248,249 AS Column249,250 AS Column250,251 AS Column251,252 AS Column252,253 AS Column253,254 AS Column254,255 AS Column255,256 AS Column256,257 AS Column257,258 AS Column258,259 AS Column259,260 AS Column260

FROM Sys.Objects

 ' AS NVARCHAR(MAX)) + @where + ' AND  2 = 2 '





SELECT LEN(@sql),@sql



EXEC sp_executesql @sql

When you are concatenating nvarchar(1-4000) strings the output string is not converted to max if it is not possible to store all the data. Also, in your example, you are concatenating a nvarchar value with the second large string which is varchar(4251). You can test what is the output parameter type very easy using sp_describe_first_result_set.

So, we have:

EXEC sp_describe_first_result_set @tsql =   N'SELECT CAST(''a'' as nvarchar(50)) + cast(''b'' as varchar(4261)) as x'

and the result is a string as follows:

That's why we need to explicity convert the large string to NVARCHAR(MAX).