下面这段SQL中的sp_prepexec是什么意思?我直接在SQL Server2005中执行,会报错“找不到句柄为 27 的预定义语句。”
我不知道@p1是怎么在哪里取值的?这段SQL怎么执行呢?declare @p1 int
set @p1=27
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000)',N'
SELECT * FROM
(
SELECT jokt2.totaladdreenum,
jokt2.readaddreenum,
jokt2.eaddreenum,
jokt1.logindaytime,
jokt1.usrid,
jokt1.customerid,
jokt1.title,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.addree END AS addree,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.respdate END AS respdate,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.resptime END AS resptime,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1. END AS ,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.delflag END AS delflag,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.attachedfile2 END AS attachedfile2,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.displaydiff END AS displaydiff,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE usrt.usrnamechnchar END AS usrnamechnchar
FROM mailinfomngtbl jokt1
INNER JOIN
(
SELECT jokt21.logindaytime,
jokt21.usrid, COALESCE(jokt21.totaladdreenum, 0) AS totaladdreenum,
COALESCE(jokt22.readaddreenum, 0) AS readaddreenum,
COALESCE(jokt23.eaddreenum, 0) AS eaddreenum
FROM
(
SELECT logindaytime, usrid, COUNT(*) AS totaladdreenum FROM mailinfomngtbl
WHERE sequenceno = 0 AND usrid = @P0 AND (diff = ''1'' OR diff = ''4'')
AND (delflag = '''' OR delflag = ''1'') AND customerid = @P1
GROUP BY logindaytime, usrid
) jokt21
LEFT JOIN
(
SELECT logindaytime, usrid, COUNT(*) AS readaddreenum FROM mailinfomngtbl
WHERE sequenceno = 0 AND usrid = @P2 AND (diff = ''1'' OR diff = ''4'')
AND (delflag = '''' OR delflag = ''1'') AND <> ''mk_mail_in.gif''
AND displaydiff <> ''E'' AND customerid = @P3
GROUP BY logindaytime, usrid
) jokt22
ON ( jokt21.usrid = jokt22.usrid AND jokt21.logindaytime = jokt22.logindaytime )
LEFT JOIN
(
SELECT logindaytime, usrid, COUNT(*) AS eaddreenum FROM mailinfomngtbl
WHERE sequenceno = 0 AND usrid = @P4 AND (diff = ''1'' OR diff = ''4'')
AND (delflag = '''' OR delflag = ''1'') AND displaydiff = ''E''
AND customerid = @P5
GROUP BY logindaytime, usrid
) jokt23
ON ( jokt21.usrid = jokt23.usrid AND jokt21.logindaytime = jokt23.logindaytime )
) jokt2
ON ( jokt1.logindaytime = jokt2.logindaytime AND jokt1.usrid = jokt2.usrid)
LEFT JOIN usrmngtbl usrt ON ( jokt1.addree = usrt.usrid )
WHERE jokt1.sequenceno = 0 AND jokt1.usrid = @P6 AND (jokt1.diff = ''1'' OR jokt1.diff = ''4'')
AND (jokt1.delflag = '''' OR jokt1.delflag = ''1'') AND jokt1.customerid = @P7
) Tbl
WHERE NOT EXISTS (
SELECT * FROM mailfolderbasictbl mefk
INNER JOIN mailfolderdetailtbl mefs
ON (mefk.customerid = mefs.customerid AND mefk.usrid = mefs.usrid AND mefk.folderid = mefs.folderid)
WHERE Tbl.customerid = mefk.customerid AND mefk.usrid = @P8 AND mefk.folderdiff = ''1''
AND Tbl.logindaytime = mefs.thekey1 AND Tbl.usrid = mefs.thekey2 )
GROUP BY totaladdreenum, readaddreenum, eaddreenum, logindaytime, usrid, customerid, title, addree, respdate, resptime, , delflag, attachedfile2, displaydiff, usrnamechnchar
ORDER BY logindaytime DESC, usrnamechnchar ASC ',N'INTRAdemo77',N'INTRA',N'INTRAdemo77',N'INTRA',N'INTRAdemo77',N'INTRA',N'INTRAdemo77',N'INTRA',N'INTRAdemo77'
select @p1
我不知道@p1是怎么在哪里取值的?这段SQL怎么执行呢?declare @p1 int
set @p1=27
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000)',N'
SELECT * FROM
(
SELECT jokt2.totaladdreenum,
jokt2.readaddreenum,
jokt2.eaddreenum,
jokt1.logindaytime,
jokt1.usrid,
jokt1.customerid,
jokt1.title,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.addree END AS addree,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.respdate END AS respdate,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.resptime END AS resptime,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1. END AS ,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.delflag END AS delflag,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.attachedfile2 END AS attachedfile2,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE jokt1.displaydiff END AS displaydiff,
CASE WHEN jokt2.totaladdreenum > 1 THEN '''' ELSE usrt.usrnamechnchar END AS usrnamechnchar
FROM mailinfomngtbl jokt1
INNER JOIN
(
SELECT jokt21.logindaytime,
jokt21.usrid, COALESCE(jokt21.totaladdreenum, 0) AS totaladdreenum,
COALESCE(jokt22.readaddreenum, 0) AS readaddreenum,
COALESCE(jokt23.eaddreenum, 0) AS eaddreenum
FROM
(
SELECT logindaytime, usrid, COUNT(*) AS totaladdreenum FROM mailinfomngtbl
WHERE sequenceno = 0 AND usrid = @P0 AND (diff = ''1'' OR diff = ''4'')
AND (delflag = '''' OR delflag = ''1'') AND customerid = @P1
GROUP BY logindaytime, usrid
) jokt21
LEFT JOIN
(
SELECT logindaytime, usrid, COUNT(*) AS readaddreenum FROM mailinfomngtbl
WHERE sequenceno = 0 AND usrid = @P2 AND (diff = ''1'' OR diff = ''4'')
AND (delflag = '''' OR delflag = ''1'') AND <> ''mk_mail_in.gif''
AND displaydiff <> ''E'' AND customerid = @P3
GROUP BY logindaytime, usrid
) jokt22
ON ( jokt21.usrid = jokt22.usrid AND jokt21.logindaytime = jokt22.logindaytime )
LEFT JOIN
(
SELECT logindaytime, usrid, COUNT(*) AS eaddreenum FROM mailinfomngtbl
WHERE sequenceno = 0 AND usrid = @P4 AND (diff = ''1'' OR diff = ''4'')
AND (delflag = '''' OR delflag = ''1'') AND displaydiff = ''E''
AND customerid = @P5
GROUP BY logindaytime, usrid
) jokt23
ON ( jokt21.usrid = jokt23.usrid AND jokt21.logindaytime = jokt23.logindaytime )
) jokt2
ON ( jokt1.logindaytime = jokt2.logindaytime AND jokt1.usrid = jokt2.usrid)
LEFT JOIN usrmngtbl usrt ON ( jokt1.addree = usrt.usrid )
WHERE jokt1.sequenceno = 0 AND jokt1.usrid = @P6 AND (jokt1.diff = ''1'' OR jokt1.diff = ''4'')
AND (jokt1.delflag = '''' OR jokt1.delflag = ''1'') AND jokt1.customerid = @P7
) Tbl
WHERE NOT EXISTS (
SELECT * FROM mailfolderbasictbl mefk
INNER JOIN mailfolderdetailtbl mefs
ON (mefk.customerid = mefs.customerid AND mefk.usrid = mefs.usrid AND mefk.folderid = mefs.folderid)
WHERE Tbl.customerid = mefk.customerid AND mefk.usrid = @P8 AND mefk.folderdiff = ''1''
AND Tbl.logindaytime = mefs.thekey1 AND Tbl.usrid = mefs.thekey2 )
GROUP BY totaladdreenum, readaddreenum, eaddreenum, logindaytime, usrid, customerid, title, addree, respdate, resptime, , delflag, attachedfile2, displaydiff, usrnamechnchar
ORDER BY logindaytime DESC, usrnamechnchar ASC ',N'INTRAdemo77',N'INTRA',N'INTRAdemo77',N'INTRA',N'INTRAdemo77',N'INTRA',N'INTRAdemo77',N'INTRA',N'INTRAdemo77'
select @p1
没玩过
顺带学习