question

I have this huge dynamic sql query on the bottom. If I run it as it is, I get what I want: the full dynamic query (you can test it by yourself) but when trying to create the dynamic query casting the @Code and @Use to any varchar/nvarchar type different from varchar(max) (like nvarchar(max),nvarchar(10),varchar(10),etc) then the dynamic sql mess up. I also tried with STR() statement. Any ideas why the wrong behavior? I'm courious! Thanks :) (By the way this is not the original query, I just changed a little to simplify the example). DECLARE @Code INT , @Use INT ; select @Code = 150 , @Use = 150 DECLARE @cols NVARCHAR(MAX); SET @cols = 'ah.ForLeaseSubleaseAskingAveragePrice AS AverageSubleaseLeaseRate ,ah.PropertyName AS PropertyName ,ah.Address AS Address ,ah.PropertyID AS PropertyID ,ah.TotalAvailableSF AS TotalAvailableSF ,ah.TotalVacantSF AS TotalVacantSF ,ah.OccupiedSF AS TotalOccupiedSF ,ah.NetAbsorption AS TotalQuarterlyAbsorption ,ah.ytdAbsorption AS YTDTotalNetAbsorption ,ah.DirectAvailableSF AS DirectAvailableSF ,ah.DirectVacantSF AS DirectVacantSF ,ah.DirectOccupiedSF AS DirectOccupiedSF ,mkt.Display AS Market ,submkt.Display AS Submarket ,co.Display AS County ,bc.Display AS BuildingClass ,ah.YearBuilt AS YearBuilt ,ah.EstimatedCompletionDate AS EstimatedCompletionDate ,ah.GrossLandAreaSF AS LotSizeSF ,ah.GrossLandAreaAcres AS LotSizeAcres ,ten.Display AS Tenancy ,gi.Display AS GovernmentInterest ,own.Display AS OwnerOccupied ,cdirect.CompanyName AS DirectListingCompany ,adirect.FirstName adirect.LastName AS DirectListingBroker ,csublease.CompanyName AS SubleaseListingCompany ,asublease.firstname asublease.lastname AS SubleaseListingBroker ,sale.Display AS SaleType ,ah.ListingPrice AS ListingPrice ,ah.PricePerSF AS PriceSF ,csale.CompanyName AS SaleListingCompany ,asale.FirstName asale.LastName AS SaleListingBroker ,ah.HistoryComments AS HistoryComments ,ah.HistoryComments AS HistoryComments ,ah.InternalComments AS InternalComments ,ISNULL(ah.DirectVacantSF/NULLIF(CAST(ah.BuildingSize AS FLOAT), 0),0) AS DirectVacancyPercent ,ISNULL(ah.TotalVacantSF/NULLIF(CAST(ah.BuildingSize AS FLOAT), 0),0) AS TotalVacancyPercent ,ISNULL(ah.SubleaseVacantSF/NULLIF(CAST(ah.BuildingSize AS FLOAT), 0),0) AS SubleaseVacancyPercent ,zon.Display AS Zoning ,ah.IsTracked AS Tracked ,con.Display AS ConstructionStatus ,ah.StreetNumber AS StreetNumber ,pre.Display AS Prefix ,ah.StreetName AS StreetName ,suf.Display AS Suffix1 ,suf2.Display AS Suffix2 ,topc.Address AS TrueOwnerAddress ,topc.City AS TrueOwnerCity ,topcstate.Display AS TrueOwnerState ,topc.Phone AS TrueOwnerPhone ,topc.FirstName topc.LastName AS TrueOwnerPrimaryContact ,topc.ContactPhone AS TrueOwnerPrimaryContactPhone ,topc.Email AS TrueOwnerPrimaryContactEmail ,topc.SecondaryContactFirstName topc.SecondaryContactLastName AS TrueOwnerSecondaryContact ,topc.SecondaryContactPhone AS TrueOwnerSecondaryContactPhone ,topc.SecondaryContactEmail AS TrueOwnerSecondaryContactEmail ,ah.Latitude AS Latitude ,ah.Longitude AS Longitude ,att.OptPath AS PhotoURL ,ah.Year AS Year ,ah.Quarter AS Quarter'; DECLARE @Sql NVARCHAR(MAX); SET @Sql = ' WITH AuxHis AS( SELECT p.Address , AuxH.BuildingClassCode , p.BuildingNumber , AuxH.BuildingSize , p.CityCode , p.CountyCode , AuxH.ConstructionStateCode , AuxH.DirectAvailableSF , AuxH.DirectNetAbsorption , AuxH.DirectOccupiedSF , AuxH.DirectVacancyPercent , AuxH.DirectVacantSF , AuxH.DirectYTDAbsorption , p.EastWestStreet , AuxH.EstimatedCompletionDate , AuxH.ForLeaseAskingHighPrice , AuxH.ForLeaseAskingLowPrice , AuxH.ForLeaseSubleaseAskingAveragePrice , AuxH.ForLeaseTotalAskingAveragePrice , p.FullTextZip , AuxH.GeneralisisCode , AuxH.GovtInterestCode , AuxH.GrossLandAreaAcres , AuxH.GrossLandAreaSF , AuxH.HistoryComments , AuxH.InternalComments , AuxH.IsTracked , p.Latitude , AuxH.LeaseTypeCode , AuxH.ListingPrice , p.Longitude , AuxH.MarketAreaCode , AuxH.MarketSectorCode , AuxH.NetAbsorption , p.NorthSouthStreet , AuxH.OccupiedSF , AuxH.OperatingExpensesPerSF , p.OwnerCompanyId , AuxH.OwnerOccupantCode , p.PrefixCode , AuxH.PricePerSF , AuxH.PropertyID , p.PropertyManagerCompanyBranchName , p.PropertyManagerPhone , p.PropertyName , p.QuadrantCode , AuxH.[Quarter] , AuxH.SaleTypeCode , p.SiteId , AuxH.SpecificUseCode , p.StateCode , p.StreetName , p.StreetNumber , AuxH.SubleaseAvailableSF , AuxH.SubleaseLeaseRateHigh , AuxH.SubleaseLeaseRateLow , AuxH.SubleaseLeaseTypeCode , AuxH.SubleaseVacancyPercent , AuxH.SubleaseVacantSF , p.SuffixCode , p.Suffix2Code , AuxH.TenancyCode , AuxH.TotalAvailableSF , AuxH.TotalVacancyPercent , AuxH.TotalVacantSF , AuxH.Year , p.YearBuilt , AuxH.YTDAbsorption FROM ( SELECT sd.BuildingClassCode , sd.BuildingSize , sd.ConstructionStateCode , DirectAvailableSF = CASE WHEN sd.DirectAvailableSF >= 0 THEN sd.DirectAvailableSF ELSE NULL END , sd.DirectNetAbsorption , DirectOccupiedSF = CASE WHEN sd.DirectOccupiedSF >= 0 THEN sd.DirectOccupiedSF ELSE NULL END , DirectVacancyPercent = ISNULL(CASE WHEN sd.DirectVacantSF >= 0 THEN sd.DirectVacantSF ELSE NULL END/NULLIF(CAST(sd.BuildingSize AS FLOAT), 0),0) , DirectVacantSF = CASE WHEN sd.DirectVacantSF >= 0 THEN sd.DirectVacantSF ELSE NULL END , sd.DirectYTDAbsorption , sd.EstimatedCompletionDate , ForLeaseAskingHighPrice = ( CASE WHEN SubleaseLeaseRateHigh > sd.ForLeaseAskingHighPrice THEN COALESCE(NULLIF(sd.SubleaseLeaseRateHigh,0),sd.ForLeaseAskingHighPrice) ELSE COALESCE(NULLIF(sd.ForLeaseAskingHighPrice,0),sd.SubleaseLeaseRateHigh) END) , ForLeaseAskingLowPrice = ( CASE WHEN sd.SubleaseLeaseRateLow < sd.ForLeaseAskingLowPrice THEN COALESCE(NULLIF(sd.SubleaseLeaseRateLow,0),sd.ForLeaseAskingLowPrice) ELSE COALESCE(NULLIF(sd.ForLeaseAskingLowPrice,0),sd.SubleaseLeaseRateLow) END) , ForLeaseSubleaseAskingAveragePrice = (sd.SubleaseLeaseRateLow + sd.SubleaseLeaseRateHigh ) / 2 , ForLeaseTotalAskingAveragePrice = ( CASE WHEN sd.SubleaseLeaseRateLow < sd.ForLeaseAskingLowPrice THEN COALESCE(NULLIF(sd.SubleaseLeaseRateLow,0),sd.ForLeaseAskingLowPrice) ELSE COALESCE(NULLIF(sd.ForLeaseAskingLowPrice,0),sd.SubleaseLeaseRateLow) END + NULLIF( CASE WHEN sd.SubleaseLeaseRateHigh > sd.ForLeaseAskingHighPrice THEN COALESCE(NULLIF(sd.SubleaseLeaseRateHigh,0),sd.ForLeaseAskingHighPrice) ELSE COALESCE(NULLIF(sd.ForLeaseAskingHighPrice,0),sd.SubleaseLeaseRateHigh) END , 0) ) / 2.00 , sd.GeneralisisCode , sd.GovtInterestCode , sd.GrossLandAreaAcres , sd.GrossLandAreaSF , HistoryComments = CAST(sd.HistoryComments AS VARCHAR(MAX)) , InternalComments = CAST(sd.InternalComments AS VARCHAR(MAX)) , sd.IsTracked , sd.LeaseTypeCode , sd.ListingPrice , sd.MarketAreaCode , sd.MarketSectorCode , sd.NetAbsorption , OccupiedSF = CASE WHEN sd.OccupiedSF >= 0 THEN sd.OccupiedSF ELSE NULL END , sd.OperatingExpensesPerSF , sd.OwnerOccupantCode , sd.PricePerSF , sd.PropertyID , si.[Quarter] , sd.SaleTypeCode , sd.SpecificUseCode , SubleaseAvailableSF = CASE WHEN sd.SubleaseAvailableSF >= 0 THEN sd.SubleaseAvailableSF ELSE NULL END , sd.SubleaseLeaseRateHigh , sd.SubleaseLeaseRateLow , sd.SubleaseLeaseTypeCode , SubleaseVacancyPercent = ISNULL(sd.SubleaseVacantSF/NULLIF(CAST(sd.BuildingSize AS FLOAT), 0),0) , SubleaseVacantSF = CASE WHEN SD.SubleaseVacantSF >= 0 THEN SD.SubleaseVacantSF ELSE NULL END , sd.TenancyCode , TotalAvailableSF = CASE WHEN SD.TotalAvailableSF >= 0 THEN SD.TotalAvailableSF ELSE NULL END , TotalVacancyPercent = ISNULL(sd.TotalVacantSF/NULLIF(CAST(sd.BuildingSize AS FLOAT), 0),0) , TotalVacantSF = CASE WHEN SD.TotalVacantSF >= 0 THEN SD.TotalVacantSF ELSE NULL END , si.[Year] , sd.YTDAbsorption FROM [dbo].[SnapshotSetup] ss JOIN [dbo].[SnapshotInfo] si ON ss.SnapshotSetupID = si.SnapshotSetupID JOIN @SelectedTimes stimes ON (si.[Year] * 100 + si.[Quarter]) = stimes.IntegerValue JOIN [dbo].[snapshotdetail] sd ON sd.SnapshotInfoID = si.SnapshotInfoID JOIN @PropertyIDs pids ON sd.PropertyID = pids.IntegerValue WHERE 1=1 AND si.[Year] <> 0 AND ss.Metropolis = ' + cast(@Code as varchar(max)) + ' AND sd.GeneralisisCode = ' + cast(@Use as varchar(max)) + ' UNION ALL SELECT pdc.BuildingClassCode , pdc.BuildingSize , pdc.ConstructionStateCode , DirectAvailableSF = CASE WHEN pdc.DirectAvailableSF >= 0 THEN pdc.DirectAvailableSF ELSE NULL END , pdc.DirectNetAbsorption , DirectOccupiedSF = CASE WHEN pdc.DirectOccupiedSF >= 0 THEN pdc.DirectOccupiedSF ELSE NULL END , DirectVacancyPercent = ISNULL(CASE WHEN pdc.DirectVacantSF >= 0 THEN pdc.DirectVacantSF ELSE NULL END/NULLIF(CAST(pdc.BuildingSize AS FLOAT), 0),0) , DirectVacantSF = CASE WHEN pdc.DirectVacantSF >= 0 THEN pdc.DirectVacantSF ELSE NULL END , pdc.DirectYTDAbsorption , pdc.EstimatedCompletionDate , ForLeaseAskingHighPrice = ( CASE WHEN SubleaseLeaseRateHigh > pdc.ForLeaseAskingHighPrice THEN COALESCE(NULLIF(pdc.SubleaseLeaseRateHigh,0),pdc.ForLeaseAskingHighPrice) ELSE COALESCE(NULLIF(pdc.ForLeaseAskingHighPrice,0),pdc.SubleaseLeaseRateHigh) END) , ForLeaseAskingLowPrice = ( CASE WHEN pdc.SubleaseLeaseRateLow < pdc.ForLeaseAskingLowPrice THEN COALESCE(NULLIF(pdc.SubleaseLeaseRateLow,0),pdc.ForLeaseAskingLowPrice) ELSE COALESCE(NULLIF(pdc.ForLeaseAskingLowPrice,0),pdc.SubleaseLeaseRateLow) END) , ForLeaseSubleaseAskingAveragePrice = (pdc.SubleaseLeaseRateLow + pdc.SubleaseLeaseRateHigh ) / 2 , ForLeaseTotalAskingAveragePrice = ( CASE WHEN pdc.SubleaseLeaseRateLow < pdc.ForLeaseAskingLowPrice THEN COALESCE(NULLIF(pdc.SubleaseLeaseRateLow,0),pdc.ForLeaseAskingLowPrice) ELSE COALESCE(NULLIF(pdc.ForLeaseAskingLowPrice,0),pdc.SubleaseLeaseRateLow) END + NULLIF( CASE WHEN pdc.SubleaseLeaseRateHigh > pdc.ForLeaseAskingHighPrice THEN COALESCE(NULLIF(pdc.SubleaseLeaseRateHigh,0),pdc.ForLeaseAskingHighPrice) ELSE COALESCE(NULLIF(pdc.ForLeaseAskingHighPrice,0),pdc.SubleaseLeaseRateHigh) END , 0) ) / 2.00 , pdc.GeneralisisCode , pdc.GovtInterestCode , pdc.GrossLandAreaAcres , pdc.GrossLandAreaSF , HistoryComments = CAST(pdc.HistoryComments AS VARCHAR(MAX)) , InternalComments = CAST(pdc.InternalComments AS VARCHAR(MAX)) , pdc.IsTracked , pdc.LeaseTypeCode , pdc.ListingPrice , pdc.MarketAreaCode , pdc.MarketSectorCode , pdc.NetAbsorption , OccupiedSF = CASE WHEN pdc.OccupiedSF >= 0 THEN pdc.OccupiedSF ELSE NULL END , pdc.OperatingExpensesPerSF , pdc.OwnerOccupantCode , pdc.PricePerSF , pdc.PropertyID , pdc.[Quarter] , pdc.SaleTypeCode , pdc.SpecificUseCode , SubleaseAvailableSF = CASE WHEN pdc.SubleaseAvailableSF >= 0 THEN pdc.SubleaseAvailableSF ELSE NULL END , pdc.SubleaseLeaseRateHigh , pdc.SubleaseLeaseRateLow , pdc.SubleaseLeaseTypeCode , SubleaseVacancyPercent = ISNULL(pdc.SubleaseVacantSF/NULLIF(CAST(pdc.BuildingSize AS FLOAT), 0),0) , SubleaseVacantSF = CASE WHEN pdc.SubleaseVacantSF >= 0 THEN pdc.SubleaseVacantSF ELSE NULL END , pdc.TenancyCode , TotalAvailableSF = CASE WHEN pdc.TotalAvailableSF >= 0 THEN pdc.TotalAvailableSF ELSE NULL END , TotalVacancyPercent = ISNULL(pdc.TotalVacantSF/NULLIF(CAST(pdc.BuildingSize AS FLOAT), 0),0) , TotalVacantSF = CASE WHEN pdc.TotalVacantSF >= 0 THEN pdc.TotalVacantSF ELSE NULL END , pdc.[Year] , pdc.YTDAbsorption FROM dbo.AuxPDetailCurrent pdc JOIN @PropertyIDs pids ON pdc.PropertyID = pids.IntegerValue JOIN @SelectedTimes stimes ON (pdc.[Year] * 100 + pdc.[Quarter]) = stimes.IntegerValue JOIN dbo.AuxP p ON pdc.PropertyID = p.PropertyID WHERE 1=1 AND DATEFROMPARTS(pdc.[Year],(pdc.[Quarter] * 3)-2,1) <= getdate() AND pdc.[Year] <> 0 AND pdc.Metropolis = '+ cast(@Code as varchar(max)) + ' AND pdc.GeneralisisCode = '+ cast(@Use as varchar(max)) + ' ) AuxH JOIN dbo.AuxP p ON AuxH.PropertyId = p.PropertyId ) SELECT ' + @cols + ' FROM AuxHis ah LEFT JOIN dbo.SomeTable mkt ON mkt.Value = ah.MarketSuperCode LEFT JOIN dbo.SomeTable submkt ON submkt.Value = ah.MarketNotSuperCode LEFT JOIN dbo.SomeTable gu ON gu.Value = ah.GeneralUse LEFT JOIN dbo.SomeTable su ON su.Value = ah.SpecificUse LEFT JOIN dbo.SomeTable bc ON bc.Value = ah.BuildingClass LEFT JOIN dbo.SomeTable ten ON ten.Value = ah.Tenancy LEFT JOIN dbo.SomeTable gi ON gi.Value = ah.InterestCodeGov LEFT JOIN dbo.SomeTable own ON own.Value = ah.OwnerOccupantCode LEFT JOIN dbo.SomeTable sale ON sale.Value = ah.SaleTypeCode LEFT JOIN dbo.SomeTable con ON con.Value = ah.ConstructionStateCode LEFT JOIN dbo.SomeTable pre ON pre.Value = ah.PrefixCode LEFT JOIN dbo.SomeTable suf ON suf.Value = ah.SuffixCode LEFT JOIN dbo.SomeTable suf2 ON suf.Value = ah.Suffix2Code LEFT JOIN dbo.SomeTable quad ON quad.Value = ah.QuadrantCode LEFT JOIN dbo.SomeTable ci ON ci.CityID = ah.CityCode LEFT JOIN dbo.SomeTable st ON st.StateID = ah.StateCode LEFT JOIN dbo.SomeTable ownC ON ownC.CompanyID = ah.OwnerCompanyId LEFT JOIN dbo.SomeTable co ON co.CountyID = ah.CountyCode JOIN dbo.SomeTable sit ON sit.SiteID = ah.SiteID LEFT JOIN dbo.SomeTable zon ON zon.Value = sit.ZoningClassCode LEFT JOIN dbo.SomeTable pa ON ah.PropertyID = pa.PropertyId JOIN dbo.Attachment att ON pa.AttachmentID = att.AttachmentID AND att.IsDefault = 1 LEFT JOIN dbo.SomeTable topc ON topc.propertyid = ah.propertyid AND topc.ContactTypeCode = 9595 LEFT JOIN dbo.SomeTable topcstate ON topcstate.StateID = topc.StateCode LEFT JOIN dbo.SomeTable ropc ON ropc.PropertyID = ah.PropertyID AND ropc.ContactTypeCode = 2606 LEFT JOIN dbo.SomeTable ropcstate ON ropcstate.StateID = ropc.StateCode LEFT JOIN dbo.SomeTable l ON l.PropertyID = ah.PropertyID AND l.statuscode IN (477,479,1655,481) LEFT JOIN dbo.SomeTable lbdirect ON l.ListingId = lbdirect.ListingId AND l.RecordTypeCode = 7685 AND l.ListingTypeCode = 921 AND lbdirect.agentseq = 1 LEFT JOIN dbo.SomeTable adirect ON adirect.AgentID = lbdirect.AgentId LEFT JOIN dbo.SomeTable cdirect ON cdirect.CompanyId = lbdirect.CompanyId LEFT JOIN dbo.SomeTable lbsublease ON l.ListingId = lbsublease.ListingId AND l.RecordTypeCode = 7685 AND l.ListingTypeCode = 922 AND lbsublease.AgentSeq = 1 LEFT JOIN dbo.SomeTable asublease ON asublease.AgentID = lbsublease.AgentId LEFT JOIN dbo.SomeTable csublease ON csublease.CompanyId = lbsublease.CompanyId LEFT JOIN dbo.SomeTable lbsale ON l.ListingId = lbsale.ListingId AND l.RecordTypeCode = 7686 and lbsale.agentseq = 1 LEFT JOIN dbo.SomeTable asale ON asale.AgentID = lbsale.AgentId LEFT JOIN dbo.SomeTable csale ON csale.CompanyId = lbsale.CompanyId' ; DECLARE @String NVARCHAR(MAX); set @string = @sql; DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */ DECLARE @offset tinyint; /*tracks the amount of offset needed */ set @string = replace( replace(@string, char(13) + char(10), char(10)) , char(13), char(10)) WHILE LEN(@String) > 1 BEGIN IF CHARINDEX(CHAR(10), @String) between 1 AND 4000 BEGIN SET @CurrentEnd = CHARINDEX(char(10), @String) -1 set @offset = 2 END ELSE BEGIN SET @CurrentEnd = 4000 set @offset = 1 END PRINT SUBSTRING(@String, 1, @CurrentEnd) set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String)) END