1.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'SP_test' AND type = 'P')
DROP PROCEDURE SP_test
GO
--存储过程：SP_test
--功 能：关联 表zhb和相关数据表
-- 动态构造zhb与数据表之间的关联
CREATE PROCEDURE sp_get_zhb_zb
--@ID nvarchar(10), --数据表id
@frdwdm nvarchar(100), --数据表frdwdm
@bbsj nvarchar(50), --数据表bbsj
@Table nvarchar(50) --数据表名
AS
DECLARE @SQL nvarchar(4000),@C_Name nvarchar(50)
set @SQL='SELECT *,(select case z.bh '
--构造游标，从zhb表中提取出数据表的列数和列名，用于动态构造sql
DECLARE C_Cursor CURSOR FOR
SELECT bh
FROM zhb z
WHERE z.ssbh=@Table
OPEN C_Cursor
FETCH NEXT FROM C_Cursor
into @C_Name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = @SQL +'when ' + @C_Name + ' then d.[' + @C_Name + '] '
FETCH NEXT FROM C_Cursor
into @C_Name
END
--关闭游标
CLOSE C_Cursor
DEALLOCATE C_Cursor
set @SQL = @SQL +'end from '+ @Table+' d where d.frdwdm='''+@frdwdm+'''and d.bbsj='''+ @bbsj + ''' and d.bs=''0'') value,
(select case z.bh '
--构造游标，从zhb表中提取出数据表的列数和列名，用于动态构造sql
DECLARE C_Cursor CURSOR FOR
SELECT bh
FROM zhb z
WHERE z.ssbh=@Table
OPEN C_Cursor
FETCH NEXT FROM C_Cursor
into @C_Name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = @SQL +'when ' + @C_Name + ' then d.[' + @C_Name + '] '
FETCH NEXT FROM C_Cursor
into @C_Name
END
--关闭游标
CLOSE C_Cursor
DEALLOCATE C_Cursor
set @SQL = @SQL +'end from '+ @Table+' d where d.frdwdm='''+@frdwdm+'''and d.bbsj='''+ @bbsj + ''' and d.bs=''1'') value1
FROM zhb z WHERE z.ssbh=''' + @Table + ''''
--执行动态sql
execute sp_executesql @SQL
GO
2.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'SP_Find' AND type = 'P')
DROP PROCEDURE SP_Find
GO
CREATE PROCEDURE SP_Find
AS
select * from (
select d0.*,'1' doubleflag from D02_C d0,D02_C d1
where d0.id<>d1.id and d0.frdwdm=d1.frdwdm and d0.bbsj=d1.bbsj and d0.bs=1
union
select d.*,'0' doubleflag from D02_C d
where d.id not in(select d0.id from D02_C d0,D02_C d1
where d0.id<>d1.id and d0.frdwdm=d1.frdwdm and d0.bbsj=d1.bbsj)) d2
order by d2.[id]
GO
3.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'SP_test' AND type = 'P')
DROP PROCEDURE SP_test
GO
--存储过程：SP_test
--功 能：关联 表zhb和相关数据表
-- 动态构造zhb与数据表之间的关联
CREATE PROCEDURE SP_test
@ID nvarchar(10), --数据表id
@Table nvarchar(50) --数据表名
AS
DECLARE @SQL nvarchar(2000),@C_Name nvarchar(50)
set @SQL='SELECT *,(select case z.bh '
--构造游标，从zhb表中提取出数据表的列数和列名，用于动态构造sql
DECLARE C_Cursor CURSOR FOR
SELECT bh
FROM zhb z
WHERE z.ssbh=@Table
OPEN C_Cursor
FETCH NEXT FROM C_Cursor
into @C_Name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = @SQL +'when ' + @C_Name + ' then d.[' + @C_Name + '] '
FETCH NEXT FROM C_Cursor
into @C_Name
END
--关闭游标
CLOSE C_Cursor
DEALLOCATE C_Cursor
set @SQL = @SQL +'end from '+ @Table+' d where d.id= '+@ID+' ) value FROM zhb z WHERE z.ssbh=''' + @Table + ''''
--执行动态sql
execute sp_executesql @SQL
GO
