动态查询表中某字段存在否的存储过程

忘是亡心i 2022-09-30 03:51 19阅读 0赞

DROP PROCEDURE \[pro\_check\_exists\_item\]  
GO

\-- =============================================  
\-- DESCRIPTION: <>  
\-- AUTHOR:  <>  
\-- CREATE DATE: <>  
\-- =============================================  
CREATE PROCEDURE \[dbo\].\[pro\_check\_exists\_item\]  
(  
 @IN\_ITEM\_CD1 VARCHAR(5),

@IN\_ITEM\_CD2 VARCHAR(10),

@OUT\_RTN VARCHAR(1) ='0' OUTPUT  
)  
AS  
BEGIN  
 DECLARE @ICOUNT INT=0  
    DECLARE @SQL\_RTN\_PARAMS NVARCHAR(2000)  
 DECLARE @TABLE\_NAME VARCHAR(100)  
 DECLARE @ITEM1 VARCHAR(10)  
 DECLARE @ITEM2 VARCHAR(20)  
 DECLARE @FLG VARCHAR(1)  
 DECLARE @SQL NVARCHAR(2000)

SET @SQL\_RTN\_PARAMS = [N'@OUT\_ICOUNT][N_OUT_ICOUNT] INT OUTPUT'  
   
 DECLARE MY\_CUR CURSOR FAST\_FORWARD FOR  
 SELECT FLG,TABLE\_NAME,  
 \[DRAW\_CD1\] AS 'ITEM\_CD1',  
 \[DRAW\_CD2\] AS 'ITEM\_CD2'  
 FROM  
 (  
 SELECT '1' AS FLG,A.NAME AS ITEM\_NAME,B.NAME AS TABLE\_NAME  
 FROM SYSCOLUMNS A INNER JOIN SYSOBJECTS B  
 ON A.ID = B.ID  
 WHERE B.XTYPE = 'U' AND A.NAME LIKE 'DRAW\_CD\_' AND  
 B.NAME NOT LIKE 'Z\_%'  
 ) AS T  
 PIVOT  
 (  
 MAX(T.ITEM\_NAME)  
 FOR T.ITEM\_NAME IN  
 (\[DRAW\_CD1\],\[DRAW\_CD2\])  
 )  
 AS PVT  
 UNION  
 SELECT FLG,TABLE\_NAME,  
 \[ITEM\_CD1\] AS 'ITEM\_CD1',  
 \[ITEM\_CD2\] AS 'ITEM\_CD2'  
 FROM  
 (  
 SELECT '2' AS FLG,  
 A.NAME AS ITEM\_NAME,  
 B.NAME AS TABLE\_NAME  
 FROM SYSCOLUMNS A INNER JOIN SYSOBJECTS B  
 ON A.ID = B.ID  
 WHERE B.XTYPE = 'U' AND A.NAME LIKE 'ITEM\_CD\_' AND  
 B.NAME NOT LIKE 'Z\_%' AND B.NAME <> 'M\_ITEM'  
 ) AS T  
 PIVOT  
 (  
 MAX(T.ITEM\_NAME)  
 FOR T.ITEM\_NAME IN  
 (\[ITEM\_CD1\],\[ITEM\_CD2\])  
 )  
 AS PVT  
 UNION  
 SELECT FLG,TABLE\_NAME,  
 \[MODEL\_CD1\] AS 'ITEM\_CD1',  
 \[MODEL\_CD2\] AS 'ITEM\_CD2'  
 FROM  
 (  
 SELECT '3' AS FLG,  
 A.NAME AS ITEM\_NAME,  
 B.NAME AS TABLE\_NAME  
 FROM SYSCOLUMNS A INNER JOIN SYSOBJECTS B  
 ON A.ID = B.ID  
 WHERE B.XTYPE = 'U' AND A.NAME LIKE 'MODEL\_CD\_' AND  
 B.NAME NOT LIKE 'Z\_%'  
 ) AS T  
 PIVOT  
 (  
 MAX(T.ITEM\_NAME)  
 FOR T.ITEM\_NAME IN  
 (\[MODEL\_CD1\],\[MODEL\_CD2\])  
 )  
 AS PVT  
 ORDER BY TABLE\_NAME

OPEN MY\_CUR  
 FETCH NEXT FROM MY\_CUR INTO  
     @FLG,@TABLE\_NAME,@ITEM1,@ITEM2  
 WHILE @@FETCH\_STATUS = 0  
 BEGIN

SET @SQL = N'SELECT @OUT\_ICOUNT=COUNT(\*) FROM ['+@TABLE\_NAME+'][TABLE_NAME] WHERE 1=1'  
 IF @ITEM1 IS NOT NULL OR RTRIM(@ITEM1)<>''

SET @SQL = @SQL+' AND [  RETURN  
 END  
 FETCH NEXT FROM MY\_CUR INTO  
     @FLG,@TABLE\_NAME,@ITEM1,@ITEM2  
 END][TABLE_NAME]

CLOSE MY\_CUR  
 DEALLOCATE MY\_CUR  
     SET @OUT\_RTN='1'  
  RETURN  
END

[N_OUT_ICOUNT]: mailto:N'%20rel=
[TABLE_NAME]: mailto:'%20rel=