The stored procedure is as follows:
Alter procedure d_sel_bj_xcl
@filter varchar (2000), @ Sortstr Varchar (2000), @ iskw bit
AS
Set @ Sortstr = 'Spzyflid, SPKW, SPID'
Declare @SQL VARCHAR (8000)
Set @ SQL = '
SELECT SPID, CKID, SPMC, SPGG, CZMC, BZMC, CDMC, ZLMC, SPJLDW, SPKW, '
IF @ iSKW = 1
Set @ SQL = @ SQL 'KWH,'
Set @ SQL = @ SQL '
SUM (Case When a.crklbfx = 1 THEN SL ELSE -SL END) AS SPXCL FROM
Select a. *, b.spzyflid, b.spmc, b.spgg, cz.czmc, bz.bzmc, cd.cdmc, zl.zlmc, b.spjldw,
SPKW = Case When Patindex ('% ([0-9] [0-9] [0-9] [0-9])' ', b.SPMC)> 0
Then Right (SPMC, 6) Else '' '' end,
C.crklbfx from bjlsb a
JOIN CRKLB C on A.crlb = C.crklbid
Left join sp b on a.spid = B.SPID
Left join cz on b.spczid = CZ.czid
Left join bz on b.spbzid = bz.bzid
Left join zl on b.spzlid = zl.zlid
Left join cd on b.spcdid = cd.cdid
WHERE A.ISZF is NULL '
IF LEN (@filter)> 0
Set @ SQL = @ SQL 'and
' @filter
Set @ SQL = @ SQL ') a
GROUP BY SPID, Spzyflid, CKID, SPMC, SPGG, CZMC, BZMC, CDMC, ZLMC, SPJLDW, SPKW '
IF @ iSKW = 1
Set @ SQL = @ SQL ', KWH'
IF LEN (@sortstr)> 0
Set @ SQL = @ SQL 'Order By
' @ Sortstr
EXEC (@SQL)
The process of call is as follows:
SELECT * FROM
Openrowset ('sqloledb ",
'JSZG'; 'SA'; '6162900',
'execute zgerp..d_sel_bj_xcl' 'ckid =' '' '' 45 '' '', '' '' ', 0') AS A
The result is:
Server: Message 7355, Level 16, State 1, Row 1
The OLE DB provider 'sqloledb' is inconsistent with the metadata provided by the column. Changed the name when executed.
OLE DB error trace [Non-interface error: OLE DB provider returned different names for a column: ProviderName = 'sqloledb', CompileTimeColumnName = 'kwh', RunTimeColumnName = 'spxcl', Rowset = execute zgerp..d_sel_bj_xcl 'ckid =' ' 45 '' ',' ', 0]. Take a closer look, you can see the existence of the KWH column. If you use the library management, then the library bit number is a summary basis, but the query is set to 0, which is different from the column, compiles all columns, So there is this error, I don't think of a good way, set 0 to 1 when the query is set, anyway, do not use the library level to manage, and the number is not affected :)
