Sql 存储过程 @strSQl 子句的应用

 

 

SELECT * FROM dbo.YHZH 
--删除外键
ALTER TABLE [dbo].[JHFKDMX1]  drop  FK_YHZH_JHFKDMX1 
ALTER TABLE [dbo].[XHSKDMX1]  drop  FK_YHZH_XHSKDMX1 
ALTER TABLE [dbo].[QTFYDMX1]  drop  FK_YHZH_QTFYDMX1 
ALTER TABLE [dbo].[QTSKDMX1]  drop  FK_YHZH_QTSKDMX1 
--修改数据
UPDATE yhzh SET zhdm = '1001' WHERE zhdm = '101'
UPDATE yhzh SET zhdm = '1002' WHERE zhdm = '201'
UPDATE yhzh SET zhdm = '1002.'+ RIGHT(zhdm,2) WHERE LEN(zhdm) = 5
--添加外键
ALTER TABLE [dbo].[JHFKDMX1] WITH NOCHECK ADD CONSTRAINT [FK_YHZH_JHFKDMX1] FOREIGN KEY ([KMDM]) REFERENCES [dbo].[YHZH] ([ZHDM])
ALTER TABLE [dbo].[XHSKDMX1] WITH NOCHECK ADD CONSTRAINT [FK_YHZH_XHSKDMX1] FOREIGN KEY ([KMDM]) REFERENCES [dbo].[YHZH] ([ZHDM])
ALTER TABLE [dbo].[QTFYDMX1] WITH NOCHECK ADD CONSTRAINT [FK_YHZH_QTFYDMX1] FOREIGN KEY ([KMDM]) REFERENCES [dbo].[YHZH] ([ZHDM])
ALTER TABLE [dbo].[QTSKDMX1] WITH NOCHECK ADD CONSTRAINT [FK_YHZH_QTSKDMX1] FOREIGN KEY ([KMDM]) REFERENCES [dbo].[YHZH] ([ZHDM])



--创建存储过程 修改其他表中的数据

CREATE PROCEDURE UPDATEKMDM
    @tableN VARCHAR(50) ,
    @col VARCHAR(50)
AS 
 BEGIN 
   DECLARE @strSQL VARCHAR(8000)
    SET @strSQL='UPDATE '+ @tableN+
    ' SET '+    @col +'= ''1001'''+
    ' WHERE ' +  @col +'= ''101'''  +
    ' UPDATE '+ @tableN+
    ' SET '+    @col +'= ''1002'''+
    ' WHERE ' +  @col +'=''201'''  +
     ' UPDATE '+ @tableN+
    ' SET '+    @col +'= ''1002.'' + RIGHT('+@col+', 2)'+
    ' WHERE  LEN('+@col+') = 5 ' ;
    
END
PRINT @strSQL;       --打印sql语句便于调试，可省略
EXEC (@strSQL)



--执行存储过程

EXEC UPDATEKMDM @tableN='QTSKDMX1', @col='KMDM' ;
EXEC UPDATEKMDM @tableN='QtSkdMX1', @col='KMDM' ;

EXEC UPDATEKMDM @tableN='XJYHTZD', @col='TZKM' ;
EXEC UPDATEKMDM @tableN='JHFKDMX1', @col='KMDM' ;

EXEC UPDATEKMDM @tableN='QTSKDMX1', @col='KMDM' ;
EXEC UPDATEKMDM @tableN='XHSKDMX1', @col='KMDM' ;

EXEC UPDATEKMDM @tableN='GZHSDMX2', @col='KMDM' ;
EXEC UPDATEKMDM @tableN='DKGZT_SK', @col='KMDM' ;

EXEC UPDATEKMDM @tableN='DBJRD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='QDDBD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='JHFKD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='JHFYD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='XHSKD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='XHFYD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='PHJRD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='GZHSD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='SDJSD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='SDPHD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='JHFKD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='JHFYD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='JORDER', @col='LLR' ;
EXEC UPDATEKMDM @tableN='DKGZT', @col='LLR' ;

--EXEC UPDATEKMDM @tableN='PFFXD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='PFJRD', @col='LLR' ;

EXEC UPDATEKMDM @tableN='PFTHD', @col='LLR' ;
EXEC UPDATEKMDM @tableN='PFXHD', @col='LLR' ;


DROP PROCEDURE UPDATEKMDM