关键字'FOR‘XML附近的不正确语法
关键字'FOR‘XML附近的不正确语法
提问于 2017-07-21 06:33:26
我知道错误了
关键字'FOR‘附近的不正确语法
当我把'+@ SirketNo+'写到点上时。
我找不到错误。
错误：
Msg 156，15级，状态1,110线 关键字'FOR‘附近的不正确语法 Msg 102，15级，状态1,125线 “，”不正确的语法 Msg 102，15级，状态1,137线 “，”不正确的语法
我的代码：
DECLARE @SirketNo AS NVARCHAR(3)= '427',
@AliciAdreslerinDepartmani AS NVARCHAR(MAX) = 'MuhasebeMuduru',
@BilgiAliciAdreslerinDepartmani AS NVARCHAR(MAX) = 'Mudur',
@GizliAliciAdreslerinDepartmani AS NVARCHAR(MAX) = 'FinansKoordinatoru'', ''FinansSorumlusu'', ''Developer';
DECLARE @SqlQuery AS NVARCHAR(max) = N'
BEGIN
DECLARE @KacGunOnce INT= 13, @xml NVARCHAR(MAX), @KasaBakiye NVARCHAR(MAX), @AliciAdresler VARCHAR(MAX), @BilgiAliciAdresler VARCHAR(MAX), @GizliBilgiAliciAdresler VARCHAR(MAX), @vucut NVARCHAR(MAX), @Baslik VARCHAR(100)= '''', @CreatedBy VARCHAR(100)= '''', @Mesaj VARCHAR(250)= '''', @IsYeriNo SMALLINT;
DECLARE @mad TABLE
(logicalref INT IDENTITY(1, 1),
IsYeriNo SMALLINT,
KasaKodu VARCHAR(17),
KasaAdi VARCHAR(51),
AlıcıAdresler NVARCHAR(MAX),
BilgiAlıcıAdresler NVARCHAR(MAX),
GizliBilgiAlıcıAdresler NVARCHAR(MAX)
);
END; --Değişkenleri ve değişken tabloyu oluştur
BEGIN
INSERT INTO @mad
(IsYeriNo,
KasaKodu,
KasaAdi
)
SELECT DISTINCT
ksl.BRANCH,
lk.CODE,
lk.NAME
FROM LG_'+@SirketNo+'_01_KSLINES AS KSL WITH (NOLOCK)
JOIN L_CAPIUSER AS U WITH (NOLOCK) ON U.NR LIKE KSL.CAPIBLOCK_CREATEDBY
JOIN LG_'+@SirketNo+'_KSCARD AS lk ON lk.LOGICALREF = ksl.CARDREF
WHERE ksl.SIGN = 1
AND ksl.AMOUNT >= 300
AND CONVERT(VARCHAR(10), ksl.DATE_, 104) = CONVERT(VARCHAR(10), GETDATE() - @KacGunOnce, 104);
END; --Değişken tabloya verileri insert et
BEGIN
--Döngü için değişken atamaları
DECLARE @s INT= 1, @d INT=
(
SELECT COUNT(logicalref)
FROM @mad
);
--Döngü
WHILE @d >= @s
BEGIN
/**** DÖNGÜ BAŞLANGIÇ ****/
BEGIN
SELECT @KasaBakiye =
(
SELECT KasaKodu
FROM @mad
WHERE logicalref = @s
)+'' kodlu kasanın güncel bakiyesi: ''+FORMAT(SUM(CASHTOT.DEBIT - CASHTOT.CREDIT), ''c2'', ''tr-TR'')
FROM LG_'+@SirketNo+'_KSCARD AS CASHC WITH (NOLOCK),
LG_'+@SirketNo+'_01_CSHTOTS AS CASHTOT WITH (NOLOCK)
WHERE CASHC.CODE LIKE
(
SELECT KasaKodu
FROM @mad
WHERE logicalref = @s
)
AND CASHTOT.CARDREF = CASHC.LOGICALREF
AND CASHTOT.TOTTYPE = 1
AND CASHTOT.DAY_ >= 0
AND CASHTOT.DAY_ <= 365;
END; --Kasa Bakiyesini değişkene ata
BEGIN
-- İş yerini değişkene ata
SELECT @IsYeriNo = IsYeriNo
FROM @mad
WHERE logicalref = @s;
END; -- İş yerini değişkene ata;
BEGIN --Kasa hareketlerini HTML formatında XMLe dönüştür
SET @xml = CAST(
(
SELECT f.DATE_ AS ''td'',
'''',
f.FICHENO AS ''td'',
'''',
f.TRCODE AS ''td'',
'''',
f.CUSTTITLE AS ''td'',
'''',
f.LINEEXP AS ''td'',
'''',
f.AMOUNT AS ''td'',
'''',
f.REPORTRATE AS ''td'',
'''',
f.REPORTNET AS ''td'',
'''',
f.SPECODE AS ''td'',
'''',
f.CYPHCODE AS ''td'',
'''',
f.BRANCH AS ''td'',
'''',
f.NAME AS ''td'',
''''
FROM
(
SELECT ksl.DATE_,
KSL.FICHENO,
CASE TRCODE
WHEN 11
THEN ''CARİ HESAP TAHSİLAT''
WHEN 12
THEN ''CARİ İŞLEM''
WHEN 21
THEN ''BANKA İŞLEMİ''
WHEN 22
THEN ''BANKA İŞLEMİ''
WHEN 31
THEN ''FATURA İŞLEMİ''
WHEN 32
THEN ''FATURA İŞLEMİ''
WHEN 33
THEN ''FATURA İŞLEMİ''
WHEN 34
THEN ''FATURA İŞLEMİ''
WHEN 35
THEN ''FATURA İŞLEMİ''
WHEN 36
THEN ''FATURA İŞLEMİ''
WHEN 37
THEN ''FATURA İŞLEMİ''
WHEN 38
THEN ''FATURA İŞLEMİ''
WHEN 39
THEN ''FATURA İŞLEMİ''
WHEN 61
THEN ''ÇEK-SENET İŞLEMİ''
WHEN 62
THEN ''ÇEK-SENET İŞLEMİ''
WHEN 63
THEN ''ÇEK-SENET İŞLEMİ''
WHEN 64
THEN ''ÇEK-SENET İŞLEMİ''
WHEN 71
THEN ''KASA İŞLEMİ''
WHEN 72
THEN ''KASA İŞLEMİ''
WHEN 73
THEN ''KASA İŞLEMİ''
WHEN 74
THEN ''KASA İŞLEMİ''
ELSE ''TANIMSIZ İŞLEM''
END AS ''TRCODE'',
KSL.CUSTTITLE,
KSL.LINEEXP,
FORMAT(AMOUNT, ''c2'', ''tr-TR'') AS ''AMOUNT'',
CAST(REPORTRATE AS MONEY) AS ''REPORTRATE'',
CAST(REPORTNET AS MONEY) AS ''REPORTNET'',
KSL.SPECODE,
KSL.CYPHCODE,
KSL.BRANCH,
U.NAME
FROM LG_'+@SirketNo+'_01_KSLINES AS KSL WITH (NOLOCK) /**************************************/
JOIN L_CAPIUSER AS U WITH (NOLOCK) ON U.NR LIKE KSL.CAPIBLOCK_CREATEDBY
JOIN LG_427_KSCARD AS lk ON lk.LOGICALREF = ksl.CARDREF
WHERE ksl.SIGN = 1
AND ksl.AMOUNT >= 300
AND CONVERT(VARCHAR(10), ksl.DATE_, 104) = CONVERT(VARCHAR(10), GETDATE() - @KacGunOnce, 104)
AND ksl.BRANCH =
(
SELECT IsYeriNo
FROM @mad
WHERE logicalref = @s
)
AND lk.CODE =
(
SELECT KasaKodu
FROM @mad
WHERE logicalref = @s
)
) AS f
FOR XML PATH(''tr''), ELEMENTS
) AS NVARCHAR(max));
END; --Kasa hareketlerini HTML formatında XML''e dönüştür
BEGIN
UPDATE @mad
SET
[AlıcıAdresler] = ISNULL(
(
SELECT TOP 1 REPLACE(
(
SELECT RTRIM(MAIL) [data()]
FROM mad.dbo.Kullanicilar k
WHERE k.SIRKET = ''427''
AND IS_YERI = @IsYeriNo
AND LEN(MAIL) > 0
AND DEPERTMAN IN('''+@AliciAdreslerinDepartmani+''')
FOR XML PATH('''')
), '' '', ''; '') AS BIRLESIK
), ''''),
[BilgiAlıcıAdresler] = ISNULL(
(
SELECT TOP 1 REPLACE(
(
SELECT RTRIM(MAIL) [data()]
FROM mad.dbo.Kullanicilar k
WHERE k.SIRKET = ''427''
AND IS_YERI = @IsYeriNo
AND LEN(MAIL) > 0
AND DEPERTMAN IN('''+@BilgiAliciAdreslerinDepartmani+''')
FOR XML PATH('''')
), '' '', ''; '') AS BIRLESIK
), ''''),
[GizliBilgiAlıcıAdresler] = ISNULL(
(
SELECT TOP 1 REPLACE(
(
SELECT RTRIM(MAIL) [data()]
FROM mad.dbo.Kullanicilar k
WHERE k.SIRKET = ''427''
AND LEN(MAIL) > 0
AND DEPERTMAN IN('''+@GizliAliciAdreslerinDepartmani+''')
FOR XML PATH('''')
), '' '', ''; '') AS BIRLESIK
), '''')
WHERE IsYeriNo = @IsYeriNo;
END; -- Değişken tabloya mail adreslerini update et
BEGIN
UPDATE @mad
SET
[AlıcıAdresler] = [BilgiAlıcıAdresler]
WHERE [AlıcıAdresler] = '''';
END; -- Değişken tabloda alici adresi boş olanlara bilgideki adresleri alici olarak ekle
BEGIN
SET @Baslik = '''';
SET @Mesaj = '''';
SET @vucut = '''';
SELECT @Baslik+=CONVERT( NVARCHAR, @IsYeriNo)+'' nolu işyerinin kasa hareketleridir. [212]'';
SELECT @Mesaj+='''';
SET @vucut = ''<html>''+''<body>''+''<H3 style = "color:blue;"><i>''+@Mesaj+''</i> </H3>''+''<H2 style="text-align:center; color:orange;"> Kasa Hareketleri </H2>''+''<H4> ''+''<ul style = "list-style-type:disc">''+''<p>''+''<li>''+@KasaBakiye+''</li>''+''</ul>''+''</p>''+''<H4> ''+''
<table border = 1>
<tr>
<th> Tarih </th> <th> Fiş No </th> <th> İşlem Türü </th> <th> Cari Başlığı </th> <th> Açıklama </th> <th> Tutar </th> <th> Kur </th> <th> Döviz Tutar </th> <th> Özel Kodu </th> <th> Yetki Kodu </th> <th> İş Yeri </th> <th> Kaydeden Kullanıcı </th>
</tr>'';
SET @vucut = @vucut+@xml+''</table></body></html>''+''[''+CONVERT(NVARCHAR, @IsYeriNo)+'' nolu işyerinin ''+
(
SELECT KasaKodu
FROM @mad
WHERE logicalref = @s
)+'' kodlu kasanın ''+CONVERT(NVARCHAR, @KacGunOnce)+'' gün öncesine ait hareketleridir.] Bu maile cevap vererek bilgilendirme maili ile alakalı tavsiyenizi yazabilirsiniz.'';
END; --Mail verilerini hazırla
BEGIN
SET @AliciAdresler =
(
SELECT m.[AlıcıAdresler]
FROM @mad m
WHERE m.logicalref = @s
);
SET @BilgiAliciAdresler =
(
SELECT m.[BilgiAlıcıAdresler]
FROM @mad m
WHERE m.logicalref = @s
);
SET @GizliBilgiAliciAdresler =
(
SELECT m.[GizliBilgiAlıcıAdresler]
FROM @mad m
WHERE m.logicalref = @s
);
END; -- Mail adreslerini değişkenlere tanımla
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@body = @vucut,
@body_format = ''HTML'',
@subject = @Baslik,
@importance = ''HIGH'',
@reply_to = ''mustafaalperen@fimar.com.tr'',
@profile_name = ''MAD_Mail'',
--@recipients = @AliciAdresler,
--@copy_recipients = @BilgiAliciAdresler,
@blind_copy_recipients = @GizliBilgiAliciAdresler,
@execute_query_database = ''FIMAR_MHB'';
END; --Mail Gönder
BEGIN
DECLARE @GonderilenMailBilgisi NVARCHAR(MAX)= ''Mail (Id: ''+CONVERT(NVARCHAR, @@IDENTITY)+'') queued.'';
EXEC sp_MAD_Loglar_Ins
@FORM = ''AGENT_MAD_08_00_Mailleri'',
@KULLANICI = ''Sql Agent'',
@TERMINAL = ''427'',
@ASISTAN = @GonderilenMailBilgisi,
@DERECE = ''Bal'';
END; --Loglama yap
BEGIN
SET @s = @s + 1;
END; --Döngü için döngü değişkenini +1 arttır
/**** DÖNGÜ BİTİŞ ****/
END;
END; -- Mail gönderme döngüsü
';
EXECUTE sp_executesql @SqlQuery; 回答 1
Stack Overflow用户
回答已采纳
发布于 2017-07-21 06:59:42
考虑更改数据库设计，并尽量减少使用动态SQL。现在，您的动态SQL代码将始终容易出现神秘的错误和SQL注入攻击。
正如@PPP所建议的那样，您必须获取生成的代码并查看它的错误所在--在中调试它。
要做到这一点，您必须使用以下命令：
PRINT CAST(@SqlQuery as ntext)因为动态生成的sql大于8000字符。见this question。
然后将其复制到一个新窗口，查看语法错误并修复它们，然后修复生成它的代码。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45230585
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号