nvarchar(max) truncated

Question

Post reply

nvarchar(max) truncated

stefano.tieri

SSC Enthusiast

Points: 135
More actions
June 4, 2010 at 11:36 am

#139353

CREATE PROCEDURE [dbo].[spuGetGlobale]
@Anno integer
AS
BEGIN
DECLARE @SQLString nvarchar(Max);
SET @SQLString =
N'CREATE VIEW VGlobale
AS
SELECT
CAST(''Gennaio'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 1
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Febbraio'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 2
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Marzo'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 3
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Aprile'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 4
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Maggio'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 5
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Giugno'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 6
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Luglio'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 7
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Agosto'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 8
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Settembre'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 9
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Ottobre'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 10
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Novembre'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 11
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari
UNION ALL
SELECT
CAST(''Dicembre'' AS VARCHAR(20)) As Mese,
(Select CAST(AVG(CAST(ArrivoAusiliari AS float) - CAST(Chiamata_Ausiliari AS float) ) AS datetime) ) AS MediaTempiIntervento
from Sinistro
WHERE
MONTH(DATASINISTRO) = 12
AND YEAR(DataSinistro) = ' + LTRIM(STR(@Anno)) +
N'AND AUSILIARI = ''S''
AND ArrivoAusiliari is not null
AND ArrivoAusiliari > 0
AND Chiamata_Ausiliari is not null
AND Chiamata_Ausiliari > 0
AND ArrivoAusiliari > Chiamata_Ausiliari';
EXECUTE sp_executesql @SQLString
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You should rethink the general structure of your query.

Instead of using repeating queries with all but one parameter being equal you should look into CASE WHEN instead.

Example:

--instead of

SELECT

CAST(''Novembre'' AS VARCHAR(20)) As Mese

from Sinistro

WHERE

MONTH(DATASINISTRO) = 11

UNION ALL

SELECT

CAST(''Dicembre'' AS VARCHAR(20)) As Mese

from Sinistro

WHERE

MONTH(DATASINISTRO) = 12

-- use

SELECT

CASE WHEN MONTH(DATASINISTRO) = 11 THEN CAST(''Novembre'' AS VARCHAR(20))

WHEN MONTH(DATASINISTRO) = 12 THEN CAST(''Dicembre'' AS VARCHAR(20)) END As Mese

from Sinistro

It's not only easier to read but also will it perform better.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

stefano.tieri SSC Enthusiast Points: 135 More actions · Answer 2

Hello,

don't work, because there is AVG...

I have resolved in other way.

If I use nvarchar(max) AND every block of text is lower then 4000 char, the building string is not truncated.

This problem seems to be not present in sql server 2005.

Is possible that no one have this problem?

stefano.tieri SSC Enthusiast Points: 135 More actions · Answer 3

I foget...

AND I CAST to varchar(max) every block of text added

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

Lutz is 100% correct. Using case statements will have superior performance and readability. If you don't understand how to implement and you need to see a fully coded solution, follow the instructions in the link in my signature (also the first link in Lutz' signature.)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]