November 12, 2019 at 9:09 am
Hi,
I have a large dynamic SQL query, stored in a nvarchar(max) variable. However, string is truncated at 8000 characters and I really don't know how to fix this:
DECLARE @SQLSTRING NVARCHAR(MAX)
DECLARE @ERS INT = 1
DECLARE @FDATO INT = 79627
DECLARE @TDATO INT = 79777
SET @SQLSTRING = N';WITH CTE AS ' +
'( SELECT ' + CAST(1756 AS nVARCHAR(10)) + ' AS RPT_ID, ' +
'DBM.DATO, ' +
'S.KUNDE, ' +
'S.ONR, ' +
'S.MNR, ' +
'replicate(''0'', 4 - LEN(cast(L.MENULINE as varchar(10)))) + cast(L.MENULINE as varchar(10)) as HBS,' +
'case when isnull(s.kundegrp, 0) = 0 then kun.grp else s.kundegrp end AS KUNDEGRP,' +
'0 STUE, ' +
'0 SENG, ' +
'KUN.CPR,' +
'case ' + cast(@ERS as nvarchar(10)) + ' when 0 then kun.sort else kgs.kgs_sort end as sort, ' +
'S.LEVTYPE, ' +
'ISNULL(S.INTERN,0) AS INTERN, ' +
'CASE WHEN OA.VAR1_PREC IS NULL THEN DBM.VNR1 ELSE OA.VAR1_NR END AS VARENR, ' +
'CASE WHEN OA.VAR1_PREC IS NULL THEN var.GRP ELSE OA.VAR1_GRP END AS varegrp,' +
'CASE WHEN OA.VAR1_PREC IS NULL THEN var.TYPE ELSE OA.VAR1_TYPE END AS varetype,' +
'0 FORDRENR,' +
'S.VARENR AS MENUNR, ' +
'VARM.TYPE AS MENUTYPE, ' +
'CASE WHEN OA.MTY_PREC IS NULL THEN L.MADTYPE ELSE ISNULL(OA.MTY_NR, L.MADTYPE) END AS MTY_MADTYPE,' +
'0 AS MADTYPE2, ' +
'0 AS MOS, ' +
'0 AS EPORTION, ' +
'S.TEXT, ' +
'L.PORTION, ' +
'CASE WHEN MEN.PREC IS NULL THEN ' +
' CASE DBM.DP ' +
'WHEN 1 THEN L.D7 ' +
'WHEN 2 THEN L.D1 ' +
'WHEN 3 THEN L.D2 ' +
'WHEN 4 THEN L.D3 ' +
'WHEN 5 THEN L.D4 ' +
'WHEN 6 THEN L.D5 ' +
'WHEN 7 THEN L.D6 ' +
'ELSE '''' ' +
' END ' +
'ELSE MEN.ANTAL ' +
'END AS ANTAL,' +
'0 AS RABAT, ' +
'S.BETALING , ' +
'DBM.VFAKTOR ,' +
'0 AS Hmenuvalg ,' +
'0 AS BMenuvalg ,' +
'S.PRISGRP ,' +
'S.INDSTED ,' +
'0 AS TAG ,' +
'L.MENULINE ,' +
'0 AS levtid ,' +
''''' AS kodeko ,' +
''''' AS kodebu , ' +
''''' AS kodean , ' +
''''' AS kodepr , ' +
'DBM.KATALOGNR ,' +
'0 AS stdcopy , ' +
''''' AS Bruger , ' +
'0 AS Date , ' +
'0 AS Guest , ' +
'MEN.ANTAL_GUEST ,' +
'MEN.TILVALG1 ,' +
'MEN.TILVALG2 ,' +
'MEN.TILVALG3 ,' +
'MEN.TILVALG4 ,' +
'S.ONR AS OrgONr ,' +
'CASE DBM.DP ' +
' WHEN 1 THEN L.D7 ' +
' WHEN 2 THEN L.D1 ' +
' WHEN 3 THEN L.D2 ' +
' WHEN 4 THEN L.D3 ' +
' WHEN 5 THEN L.D4 ' +
' WHEN 6 THEN L.D5 ' +
' WHEN 7 THEN L.D6 ' +
' ELSE '''' ' +
'END AS organtal , ' +
'( CASE WHEN MEN.PREC IS NULL THEN ' +
'CASE DBM.DP ' +
' WHEN 1 THEN L.D7 ' +
' WHEN 2 THEN L.D1 ' +
' WHEN 3 THEN L.D2 ' +
' WHEN 4 THEN L.D3 ' +
' WHEN 5 THEN L.D4 ' +
' WHEN 6 THEN L.D5 ' +
' WHEN 7 THEN L.D6 ' +
' ELSE '''' ' +
'END ' +
' ELSE MEN.ANTAL ' +
' END ) AS LD ' +
'FROM dbo.STDORDRE S ' +
' INNER JOIN dbo.STDORD STO ON STO.DATO = S.DATO AND STO.KUNDE = S.KUNDE ' +
' LEFT JOIN dbo.STDORDML L ON L.ONR = S.ONR ' +
' CROSS APPLY ( SELECT dbo.MCS_ClarionDateToSQL(D.DATO) SQL_DATO , ' +
' DATEPART(dw, dbo.MCS_ClarionDateToSQL(D.DATO)) DP , ' +
' D.VNR1 , ' +
' D.DATO , ' +
' D.LINE , ' +
' D.KATALOGNR , ' +
' D.VFAKTOR , ' +
' D.MADTYPE ' +
' FROM dbo.DBMENU D ' +
' WHERE D.SNR = S.VARENR AND D.LINE = L.MENULINE ) DBM ' +
' INNER JOIN dbo.DBKUNDE kun ON kun.NR = S.KUNDE ' +
' INNER JOIN dbo.DBKUNGRP dbk ON dbk.NR = kun.GRP ' +
' INNER JOIN dbo.DBVARE varm ON varm.NR = S.VARENR ' +
' INNER JOIN dbo.DBVARE var ON var.NR = DBM.VNR1 ' +
' LEFT OUTER JOIN dbo.DBKGSORT kgs ON kgs.KGS_KUN_PREC = kun.PREC AND kgs.KGS_DBK_PREC = dbk.PREC ' +
' LEFT OUTER JOIN dbo.MENORDRE MEN ON MEN.KUNDE = S.KUNDE AND MEN.DATO = DBM.DATO AND MEN.LINIE = DBM.LINE AND MEN.NR = S.MNR ' +
' OUTER APPLY ( SELECT MTY.PREC MTY_PREC , ' +
' MTY.NR MTY_NR , ' +
' VAR1.PREC VAR1_PREC , ' +
' VAR1.VAR_PKG_ID VAR1_VAR_PKG_ID , ' +
' VAR1.NR VAR1_NR , ' +
' VAR1.TYPE VAR1_TYPE , ' +
' VAR1.GRP VAR1_GRP ' +
' FROM dbo.DBVAREKT VKT ' +
' LEFT JOIN dbo.DBVARE VAR1 ON VAR1.PREC = VKT.TO_VARE_PREC AND ( ISNULL( ' +
' VKT.MADTYPE , ' +
' 0) <> 0 ) ' +
' LEFT JOIN dbo.DBMTYPE MTY ON MTY.PREC = VKT.TO_MADTYPE_PREC ' +
' WHERE 1 = 1 ' +
' AND VKT.MADTYPE = L.MADTYPE ' +
' AND VKT.VARENR = DBM.VNR1 ) OA ' +
'WHERE 1 = 1 ' +
'AND ( kun.UDSKREVET = 0 ' +
' OR ( kun.UDSKREVET = 1 ' +
' AND kun.UDSDATO >= ' + Cast(@FDato as nvarchar(10)) + ')) ' +
'AND varm.TYPE = 9 ' +
'AND varm.KPFIX = 0 ' +
'AND S.ML = 1 ' +
'AND DBM.DATO BETWEEN ' + Cast(@FDato as nvarchar(10)) + ' AND ' + Cast(@TDato as nvarchar(10)) +
' AND STO.TYPE = 1 ' +
'AND CASE WHEN MEN.PREC IS NULL THEN CASE DBM.DP ' +
' WHEN 1 THEN L.D7 ' +
' WHEN 2 THEN L.D1 ' +
' WHEN 3 THEN L.D2 ' +
' WHEN 4 THEN L.D3 ' +
' WHEN 5 THEN L.D4 ' +
' WHEN 6 THEN L.D5 ' +
' WHEN 7 THEN L.D6 ' +
' ELSE '''' ' +
' END ' +
' ELSE MEN.ANTAL ' +
' END <> 0 ) ' +
'SELECT CTE.RPT_ID , ' +
' CTE.MADTYPE , ' +
' CTE.PREC , ' +
' CTE.VNR1 , ' +
' CTE.DATO , ' +
' CTE.FTYPE , ' +
' CTE.FNR , ' +
' CTE.KUNDE , ' +
' CTE.S_DATO , ' +
' CTE.HBS , ' +
' CTE.KUNDEGRP , ' +
' CTE.stue , ' +
' CTE.seng , ' +
' CTE.CPR , ' +
' CTE.sort , ' +
' CTE.LEVTYPE , ' +
' CTE.INTERN , ' +
' CTE.VARENR , ' +
' CTE.varegrp , ' +
' CTE.varetype , ' +
' CTE.fordrenr , ' +
' CTE.menunr , ' +
' CTE.menutype , ' +
'CTE.MTY_MADTYPE , ' +
'CTE.madtype2 , ' +
'CTE.MOS , ' +
'CTE.eportion , ' +
'CTE.TEXT , ' +
'CTE.PORTION , ' +
'CTE.ANTAL , ' +
'CTE.Rabat , ' +
'CTE.BETALING , ' +
'CTE.VFAKTOR , ' +
'CTE.Hmenuvalg , ' +
'CTE.BMenuvalg , ' +
'CTE.PRISGRP , ' +
'CTE.INDSTED , ' +
'CTE.TAG , ' +
'CTE.MENULINE , ' +
'CTE.levtid , ' +
'CTE.kodeko , ' +
'CTE.kodebu , ' +
'CTE.kodean , ' +
'CTE.kodepr , ' +
'CTE.KATALOGNR , ' +
'CTE.stdcopy , ' +
'CTE.Bruger , ' +
'CTE.Date , ' +
'CTE.Guest , ' +
'CTE.ANTAL_GUEST , ' +
'CTE.TILVALG1 , ' +
'CTE.TILVALG2 , ' +
'CTE.TILVALG3 , ' +
'CTE.TILVALG4 , ' +
'CTE.OrgONr , ' +
'CTE.organtal , ' +
'CTE.LD * CPRICE.CP_PRIS , ' +
'CTE.LD * GP.PRICE , ' +
'CA.dato ' +
'FROM CTE ' +
'CROSS APPLY ( SELECT MAX(S1.DATO) AS dato ' +
' FROM dbo.STDORD S1 ' +
' WHERE S1.DATO <= CTE.DATO ' +
' AND S1.KUNDE = CTE.KUNDE ' +
' AND ISNULL(S1.ANN, 0) <> 1 ) CA ' +
'OUTER APPLY ( SELECT TOP ( 1 ) CP_PRIS ' +
' FROM #COSTPRICES CP ' +
' WHERE 1 = 1 ' +
' AND CP.CP_VAR_PREC = CTE.PREC ' +
' AND CP.POR_NR = CTE.PORTION ' +
' AND CP.CP_DATE <= CTE.SQL_DATO ' +
' ORDER BY CP.CP_DATE DESC ) CPRICE ' +
'OUTER APPLY ( SELECT TOP ( 1 ) PRICE ' +
' FROM #PRICES SP ' +
' WHERE 1 = 1 ' +
' AND SP.PREC = CTE.PREC ' +
' AND SP.PORTION_NR = CTE.PORTION ' +
' AND SP.SQL_DATO <= CTE.SQL_DATO ' +
' AND SP.GRP_NR = CTE.PRISGRP ' +
' ORDER BY SP.SQL_DATO DESC ) GP ' +
'WHERE 1 = 1 ' +
' AND CTE.S_DATO = CA.dato ' +
' AND ( ( CTE.FTYPE = 1 ' +
' AND ( CTE.DATO - CA.dato ) % CTE.FNR = 0 ) ' +
' OR ( CTE.FTYPE = 2 ' +
' AND DATEDIFF( ' +
' WEEK , ' +
' dbo.MCS_ClarionDateToSQL(CA.dato - 1), ' +
' dbo.MCS_ClarionDateToSQL(CTE.DATO - 1)) % CTE.FNR = 0 ) ' +
' OR ( CTE.FTYPE = 3 ' +
' AND DATEPART(DAY, dbo.MCS_ClarionDateToSQL(CTE.DATO)) = 1 )) '
SELECT datalength (@SQLString);
PRINT @SQLSTRING
How should I write the query?
Thanks in advance.
November 12, 2019 at 9:17 am
I think data type precedence is causing your nvarchar(max) string to be converted to nvarchar(8000). Trying converting those variables to nvarchar(max) instead of nvarchar(10). Alternatively, there's probably a dozen ways you could simplify that query.
John
Edit - you may still find that the string isn't fully displayed when you run the query - that's a limitation of SSMS.
November 12, 2019 at 9:24 am
Hi John,
you're right, converting to nvarchar(max) those variables seems to fix the issue. I thought that, considering that datatype was the same, my @SQLString keeps its type and lenght.
About your statement: "there's probably a dozen ways you could simplify that query.". Can you suggest me something?
Thanks again.
November 12, 2019 at 9:40 am
Have a read about data type precedence - it should help you to understand.
Without being familiar with your data or database structure, it's difficult to advise on the query. Consider whether you need all those conversions to varchar(10) - is what you're converting already varchar? And do you need all those ISNULLs - if the columns in question aren't nullable then you certainly don't. Get rid of WHERE 1 = 1.
John
November 12, 2019 at 9:41 am
Thanks for your suggestions, John!
November 12, 2019 at 11:54 am
Why are you injecting the value of your parameters here as well? You should be parametrising them. I also suggest retaining linebreaks in your dynamic Statement as well; as it means that PRINT @SQLSTRING won't be a complete single line eye sore to look at.
The values you are injected aren't safely injected either, which is a BIG security flaw and you need to stop that immediately. Always parametrise dynamic statement and safely inject object names (using QUOTENAME). Not doing so opens you to SQL Injection, and you don't want Little Bobby Tables to ruin your day.
I covered some of these in an article a while ago; It would likely be a good read: Dos and Don'ts of Dynamic SQL.
The reason that your string is getting truncated, however, is because non of your string literals are declared as an nvarchar(MAX), nor are any of the injected values. As a result the literal string is treated as an nvarchar(4000) and the value is truncated once it reached 4,000 characters.
Changing the first like N';WITH CTE AS to CONVERT(nvarchar(MAX),N'WITH CTE AS ') fixes this. Note I removed the leading ; as well is ; is a terminator not a beginninator. It's not a ;WITH statement, you should be properly terminating your statements, not just starting the ones that need the previous line terminated properly with a ;. You'll find that one day that logic doesn't work and it teaches bad habits.
Looking at your statement, however, it doesn't appear it even need to be dynamic. There's nothing dynamic in it (no object names are injected into the code). Therefore you don't need to be using @SQLSTRING at all. Unfortunately, when I remove all the injected values and turn them into parameters and PRINT the value of @SQLSTRING is contains syntax errors. Therefore I dumped a bunch of line breaks at the end (after putting in the above fix). This results in the query below, however, you'll need to look at line 143 (ending END <> 0)) as you are missing 2 right parenthesis here.
WITH CTE AS
(SELECT 1756 AS RPT_ID,
DBM.DATO,
S.KUNDE,
S.ONR,
S.MNR,
REPLICATE('0', 4 - LEN(CAST(L.MENULINE AS varchar(10)))) + CAST(L.MENULINE AS varchar(10)) AS HBS,
CASE WHEN ISNULL(S.kundegrp, 0) = 0 THEN kun.grp ELSE S.kundegrp END AS KUNDEGRP,
0 AS STUE,
0 AS SENG,
kun.CPR,
CASE @ERS WHEN 0 THEN kun.sort ELSE kgs.kgs_sort END AS sort,
S.LEVTYPE,
ISNULL(S.INTERN, 0) AS INTERN,
CASE WHEN OA.VAR1_PREC IS NULL THEN DBM.VNR1 ELSE OA.VAR1_NR END AS VARENR,
CASE WHEN OA.VAR1_PREC IS NULL THEN VAR.GRP ELSE OA.VAR1_GRP END AS varegrp,
CASE WHEN OA.VAR1_PREC IS NULL THEN VAR.TYPE ELSE OA.VAR1_TYPE END AS varetype,
0 AS FORDRENR,
S.VARENR AS MENUNR,
varm.TYPE AS MENUTYPE,
CASE WHEN OA.MTY_PREC IS NULL THEN L.MADTYPE ELSE ISNULL(OA.MTY_NR, L.MADTYPE)END AS MTY_MADTYPE,
0 AS MADTYPE2,
0 AS MOS,
0 AS EPORTION,
S.TEXT,
L.PORTION,
CASE
WHEN MEN.PREC IS NULL THEN CASE DBM.DP
WHEN 1 THEN L.D7
WHEN 2 THEN L.D1
WHEN 3 THEN L.D2
WHEN 4 THEN L.D3
WHEN 5 THEN L.D4
WHEN 6 THEN L.D5
WHEN 7 THEN L.D6
ELSE ''
END
ELSE MEN.ANTAL
END AS ANTAL,
0 AS RABAT,
S.BETALING,
DBM.VFAKTOR,
0 AS Hmenuvalg,
0 AS BMenuvalg,
S.PRISGRP,
S.INDSTED,
0 AS TAG,
L.MENULINE,
0 AS levtid,
'' AS kodeko,
'' AS kodebu,
'' AS kodean,
'' AS kodepr,
DBM.KATALOGNR,
0 AS stdcopy,
'' AS Bruger,
0 AS Date,
0 AS GUEST,
MEN.ANTAL_GUEST,
MEN.TILVALG1,
MEN.TILVALG2,
MEN.TILVALG3,
MEN.TILVALG4,
S.ONR AS OrgONr,
CASE DBM.DP
WHEN 1 THEN L.D7
WHEN 2 THEN L.D1
WHEN 3 THEN L.D2
WHEN 4 THEN L.D3
WHEN 5 THEN L.D4
WHEN 6 THEN L.D5
WHEN 7 THEN L.D6
ELSE ''
END AS organtal,
(CASE
WHEN MEN.PREC IS NULL THEN CASE DBM.DP
WHEN 1 THEN L.D7
WHEN 2 THEN L.D1
WHEN 3 THEN L.D2
WHEN 4 THEN L.D3
WHEN 5 THEN L.D4
WHEN 6 THEN L.D5
WHEN 7 THEN L.D6
ELSE ''
END
ELSE MEN.ANTAL
END) AS LD
FROM dbo.STDORDRE S
INNER JOIN dbo.STDORD STO ON STO.DATO = S.DATO
AND STO.KUNDE = S.KUNDE
LEFT JOIN dbo.STDORDML L ON L.ONR = S.ONR
CROSS APPLY (SELECT dbo.MCS_ClarionDateToSQL(D.DATO) AS SQL_DATO,
DATEPART(dw, dbo.MCS_ClarionDateToSQL(D.DATO)) AS DP,
D.VNR1,
D.DATO,
D.LINE,
D.KATALOGNR,
D.VFAKTOR,
D.MADTYPE
FROM dbo.DBMENU D
WHERE D.SNR = S.VARENR
AND D.LINE = L.MENULINE) DBM
INNER JOIN dbo.DBKUNDE kun ON kun.NR = S.KUNDE
INNER JOIN dbo.DBKUNGRP dbk ON dbk.NR = kun.GRP
INNER JOIN dbo.DBVARE varm ON varm.NR = S.VARENR
INNER JOIN dbo.DBVARE VAR ON VAR.NR = DBM.VNR1
LEFT OUTER JOIN dbo.DBKGSORT kgs ON kgs.KGS_KUN_PREC = kun.PREC
AND kgs.KGS_DBK_PREC = dbk.PREC
LEFT OUTER JOIN dbo.MENORDRE MEN ON MEN.KUNDE = S.KUNDE
AND MEN.DATO = DBM.DATO
AND MEN.LINIE = DBM.LINE
AND MEN.NR = S.MNR
OUTER APPLY (SELECT MTY.PREC AS MTY_PREC,
MTY.NR AS MTY_NR,
VAR1.PREC AS VAR1_PREC,
VAR1.VAR_PKG_ID AS VAR1_VAR_PKG_ID,
VAR1.NR AS VAR1_NR,
VAR1.TYPE AS VAR1_TYPE,
VAR1.GRP AS VAR1_GRP
FROM dbo.DBVAREKT VKT
LEFT JOIN dbo.DBVARE VAR1 ON VAR1.PREC = VKT.TO_VARE_PREC
AND (ISNULL(VKT.MADTYPE, 0) <> 0)
LEFT JOIN dbo.DBMTYPE MTY ON MTY.PREC = VKT.TO_MADTYPE_PREC
WHERE 1 = 1
AND VKT.MADTYPE = L.MADTYPE
AND VKT.VARENR = DBM.VNR1) OA
WHERE 1 = 1
AND (kun.UDSKREVET = 0
OR (kun.UDSKREVET = 1
AND kun.UDSDATO >= @FDato
AND varm.TYPE = 9
AND varm.KPFIX = 0
AND S.ML = 1
AND DBM.DATO BETWEEN @FDato AND @TDato
AND STO.TYPE = 1
AND CASE
WHEN MEN.PREC IS NULL THEN CASE DBM.DP
WHEN 1 THEN L.D7
WHEN 2 THEN L.D1
WHEN 3 THEN L.D2
WHEN 4 THEN L.D3
WHEN 5 THEN L.D4
WHEN 6 THEN L.D5
WHEN 7 THEN L.D6
ELSE ''
END
ELSE MEN.ANTAL
END <> 0)
SELECT CTE.RPT_ID,
CTE.MADTYPE,
CTE.PREC,
CTE.VNR1,
CTE.dato,
CTE.FTYPE,
CTE.FNR,
CTE.KUNDE,
CTE.S_DATO,
CTE.HBS,
CTE.KUNDEGRP,
CTE.STUE,
CTE.SENG,
CTE.CPR,
CTE.sort,
CTE.LEVTYPE,
CTE.INTERN,
CTE.VARENR,
CTE.varegrp,
CTE.varetype,
CTE.FORDRENR,
CTE.menunr,
CTE.menutype,
CTE.MTY_MADTYPE,
CTE.MADTYPE2,
CTE.MOS,
CTE.EPORTION,
CTE.TEXT,
CTE.PORTION,
CTE.ANTAL,
CTE.RABAT,
CTE.BETALING,
CTE.VFAKTOR,
CTE.Hmenuvalg,
CTE.BMenuvalg,
CTE.PRISGRP,
CTE.INDSTED,
CTE.TAG,
CTE.MENULINE,
CTE.levtid,
CTE.kodeko,
CTE.kodebu,
CTE.kodean,
CTE.kodepr,
CTE.KATALOGNR,
CTE.stdcopy,
CTE.Bruger,
CTE.Date,
CTE.GUEST,
CTE.ANTAL_GUEST,
CTE.TILVALG1,
CTE.TILVALG2,
CTE.TILVALG3,
CTE.TILVALG4,
CTE.OrgONr,
CTE.organtal,
CTE.LD * CPRICE.CP_PRIS,
CTE.LD * GP.PRICE,
CA.dato
FROM CTE
CROSS APPLY (SELECT MAX(S1.DATO) AS dato
FROM dbo.STDORD S1
WHERE S1.DATO <= CTE.DATO
AND S1.KUNDE = CTE.KUNDE
AND ISNULL(S1.ANN, 0) <> 1) CA
OUTER APPLY (SELECT TOP (1)
CP_PRIS
FROM #COSTPRICES CP
WHERE 1 = 1
AND CP.CP_VAR_PREC = CTE.PREC
AND CP.POR_NR = CTE.PORTION
AND CP.CP_DATE <= CTE.SQL_DATO
ORDER BY CP.CP_DATE DESC) CPRICE
OUTER APPLY (SELECT TOP (1)
PRICE
FROM #PRICES SP
WHERE 1 = 1
AND SP.PREC = CTE.PREC
AND SP.PORTION_NR = CTE.PORTION
AND SP.SQL_DATO <= CTE.SQL_DATO
AND SP.GRP_NR = CTE.PRISGRP
ORDER BY SP.SQL_DATO DESC) GP
WHERE 1 = 1
AND CTE.S_DATO = CA.dato
AND ((CTE.FTYPE = 1
AND (CTE.dato - CA.dato) % CTE.FNR = 0)
OR (CTE.FTYPE = 2
AND DATEDIFF(WEEK, dbo.MCS_ClarionDateToSQL(CA.dato - 1), dbo.MCS_ClarionDateToSQL(CTE.dato - 1)) % CTE.FNR = 0)
OR (CTE.FTYPE = 3
AND DATEPART(DAY, dbo.MCS_ClarionDateToSQL(CTE.dato)) = 1));
Like mentioned, also bin the WHERE 1 = 1 clauses (there are many, so really not sure why you have them all), it's really not needed.
The clause below looks like a performance killer as well
AND DATEDIFF(WEEK, dbo.MCS_ClarionDateToSQL(CA.dato - 1), dbo.MCS_ClarionDateToSQL(CTE.dato - 1)) % CTE.FNR = 0).
Those are multi-line scalar function which perform awfully and will ruin the SARGability of your query. I suggest moving things like that to proper Boolean Logic.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 12, 2019 at 12:11 pm
Thanks Tom!!! I will follow your advices. 🙂
November 12, 2019 at 12:49 pm
Just break up the statement so you are adding to @SQLSTRING several times.
SET @SQLSTRING = N';WITH CTE AS ' +
'( SELECT ' + CAST(1756 AS nVARCHAR(10)) + ' AS RPT_ID, ' +
'DBM.DATO, ' +
'S.KUNDE, ' +
'S.ONR, ' +
'S.MNR, ' +
'replicate(''0'', 4 - LEN(cast(L.MENULINE as varchar(10)))) + cast(L.MENULINE as varchar(10)) as HBS,'
SET @SQLSTRING += N' case when isnull(s.kundegrp, 0) = 0 then kun.grp else s.kundegrp end AS KUNDEGRP,' +
'0 STUE, ' +
'0 SENG, ' +
'KUN.CPR,' +
'case ' + cast(@ERS as nvarchar(10)) + ' when 0 then kun.sort else kgs.kgs_sort end as sort, ' +
'S.LEVTYPE, ' +
etc...
November 12, 2019 at 2:30 pm
Just break up the statement so you are adding to @SQLSTRING several times.
But why use a dynamic statement at all for a statement that isn't dynamic? The problem only exists because the OP is using a constructed query when they don't need to. Remove the unrequired element and there is no truncation problem; a single statement isn't limited to 4,000 characters.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 12, 2019 at 2:35 pm
I'm going to use the easy excuse here :). This is a part of a very long stored procedure and I'm just trying to improve a bit (and I did it, so imagine how it was before). In the code there are things like this:
If @KP = 1
Begin
Set @SqlString = @SqlString + ' (' + @AntalString + ') * dbo.MD_GetKostPris(isnull(var1.prec, var.prec), ''V'', dbo.MCS_ClarionDateToSQL(dbm.dato), s.portion, '
+ CAST(@PH as CHAR(1)) + ') as TKPris,'
End
ELSE
SET @SqlString = @SqlString + '0 as TKPris,'
that can be probably solved with a CASE. I was trying to keep everything as it was before. However, you're completely right and I have to rewrite the whole sp and avoid the use of dynamic SQL.
November 12, 2019 at 2:52 pm
That code is wide open to injection. You really need to fix it before you do anything else. And I do literally mean anything. SQL injection is something your code should never be open to; it's 2019 and there is more than enough education out there about it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 12, 2019 at 2:53 pm
Point taken!
November 12, 2019 at 5:20 pm
String manipulation (such as concatenating lots of small strings into a big one) is messy under the covers; avoid when possible.
SQL Server treats carriage returns and line feeds as text characters inside strings. In other words, there's no need to treat T-SQL like C# or other languages that always see CRLF as the end of a line, unless a line-continuation character (such as the back-tick ` character in Powershell) precedes it.
Compare the following two lines of code, which are both valid:
-- this forces lots of string-concat operations
SET @SQLSTRING = N';WITH CTE AS ' +
'( SELECT ' + CAST(1756 AS nVARCHAR(10)) + ' AS RPT_ID, ' +
'DBM.DATO, ' +
'S.KUNDE, ' +
'S.ONR, ' +
'S.MNR, ' +
'replicate(''0'', 4 - LEN(cast(L.MENULINE as varchar(10)))) + cast(L.MENULINE as varchar(10)) as HBS,'
-- this only breaks on a switch from text to function code
SET @SQLSTRING = N'
WITH CTE AS ( SELECT ''' + CAST(1756 AS nVARCHAR(10)) + ''' AS RPT_ID,
DBM.DATO,
S.KUNDE,
S.ONR,
S.MNR,
replicate(''0'', 4 - LEN(cast(L.MENULINE as varchar(10)))) + cast(L.MENULINE as varchar(10)) as HBS,'
Eddie Wuerch
MCM: SQL
November 12, 2019 at 9:45 pm
From my quick look, there is absolutely NOTHING in the code in the original post that requires Dynamic SQL.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply