消息 137,级别 15,状态 2,第 2 行 必须声明标量变量 "@sql2"。
declare @sql2 varchar(8000)
set @sql2 = 'select cp.corpname, dt.deptname,
pbc.pname, pc.duty,pc.pcl, pbc.pk_psnbasdoc '
select @sql2=@sql2+',sum(case cz.corsename when '''+corsename+''' then ge.score else 0 end) [' + corsename + ']'
from (select corsename from psncorse ) as a
set @sql2 = @sql2 +' from psnbasdoc pbc
join psndoc pc on pbc.pk_psnbasdoc=pc.pk_psnbasdoc
join dept dt on pc.pk_dept=dt.pk_dept
join corp cp on dt.pk_corp=cp.pk_corp
join psnscore ge on pc.pk_psndoc=ge.pk_psndoc
join psncorse cz on cz.pk_psncorse=ge.pk_psncorse
group by cp.corpname, dt.deptname,
pbc.pname, pc.duty,pc.pcl ,pbc.pk_psnbasdoc'
exec @sql2
请问是哪里出错了啊 还有如何用这个语句来创建一个试图?
------解决方案--------------------你的exec是有问题,但是那个报错不是这个问题,先把psncorse 表结构给出来看看
------解决方案--------------------SQL code
--先把上面的内容加到存储过程
再
--创建视图:
IF OBJECT_ID('v_test') IS NOT NULL
DROP VIEW v_test
GO
CREATE VIEW v_test
AS
SELECT *
FROM OPENROWSET(
'sqloledb',
'Trusted_Connection=yes', --此处可用'uid=sa;pwd=123' (SQL认证的方式来代替)
'SET FMTONLY OFF; --注意:要加上此选项
EXEC MYDB..p_test --
'
)
GO
select * from v_test