自动生成基于单表CRUD的操作存储过程
/* * 自动生成基于单表CRUD的操作存储过程 * 供Excel，WS调用 * 参数：表明，自增ID名 * 结果：存储过程SQL * * 2013-05-07 20:15:20 * JackChain */ Alter PROC P_AutoCreateXMLWSProc(@TableName VARCHAR(30),@PrimaryKey VARCHAR(20)) AS BEGIN --创建表结构游标 DECLARE MyCur CURSOR FOR Select fieldName=a.name, fieldType=b.name, fieldLength=a.length, decimalLen=COLUMNPROPERTY(a.id,a.name,'PRECISION'), xsw=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on (a.id=d.id)and(d.xtype='U')and(d.name<>'dtproperties') left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on (a.id=g.major_id)and(a.colid=g.minor_id) left join sys.extended_properties f on (d.id=f.major_id)and(f.minor_id=0) where d.name=@TableName order by a.id,a.colorder; --变量 DECLARE @fieldName VARCHAR(20); DECLARE @fieldType VARCHAR(20); DECLARE @fieldLength VARCHAR(4); DECLARE @decimalLen VARCHAR(4); DECLARE @xsw VARCHAR(4); DECLARE @Structure VARCHAR(MAX); DECLARE @Fields VARCHAR(MAX); DECLARE @UpdateFields VARCHAR(MAX); SET @Structure=''; SET @Fields=''; SET @UpdateFields=''; open MyCur; fetch next from MyCur into @fieldName,@fieldType,@fieldLength,@decimalLen,@xsw; WHILE(@@FETCH_STATUS = 0) BEGIN --表结构 SET @Structure=@Structure+@fieldName+' '+ CASE WHEN @fieldType='int' THEN 'int' WHEN @fieldType='decimal' THEN 'decimal('+@decimalLen+','+@xsw+')' ELSE @fieldType+'('+@fieldLength+')' END +','+char(10)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9) ; --字段 set @Fields=@Fields+@fieldName+','+char(10)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9) ; --更新 SET @UpdateFields=@UpdateFields+@TableName+'.'+@fieldName+' = (CASE WHEN #TEMP.'+@fieldName+' IS NOT NULL THEN #TEMP.'+@fieldName+' ELSE '+@TableName+'.'+@fieldName+' END),'+char(10)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9) ; fetch next from MyCur into @fieldName,@fieldType,@fieldLength,@decimalLen,@xsw; END SET @Structure=SUBSTRING(@Structure,1,LEN(@Structure)-2); SET @Fields=SUBSTRING(@Fields,1,LEN(@Fields)-2); SET @UpdateFields=SUBSTRING(@UpdateFields,1,LEN(@UpdateFields)-2); close MyCur; deallocate MyCur; --开始构造存储过程 DECLARE @procSql VARCHAR(MAX); SET @procSql=N' /************************************************************ * JackChain * CRUD操作，供Excel调用 * 参数：XML * 参数格式：<ROOT><OPTYPE>READ/CREATE/UPDATE/DELETE</OPTYPE><PARAMS>....</PRARMS></ROOT> * Time: 2013/5/7 16:58:53 ************************************************************/ CREATE PROC [dbo].[WSXML_'+@TableName +'](@XML NVARCHAR(max)) AS BEGIN DECLARE @XML_XML XML SET @XML_XML = CONVERT(XML,@xml) SET XACT_ABORT ON BEGIN TRAN DECLARE @tran_error INT; SET @tran_error = 0; DECLARE @error_msg VARCHAR(8000); BEGIN TRY DECLARE @TYPE VARCHAR(10) --首先判断操作类型 SELECT @TYPE = @XML_XML.value(N''(/ROOT/OPTYPE)[1]'', ''VARCHAR(10)''); --如果是读取 IF (@TYPE = ''READ'') BEGIN COMMIT TRAN; DECLARE @Start VARCHAR(10); DECLARE @Limit VARCHAR(10); SELECT @Start=@XML_XML.value(N''(/ROOT/PARAMS/START)[1]'', ''VARCHAR(10)''); SELECT @Limit=@XML_XML.value(N''(/ROOT/PARAMS/LIMIT)[1]'', ''VARCHAR(10)''); IF(@Start IS NULL OR @Limit IS NULL) BEGIN SELECT (SELECT * FROM '+@TableName +' ORDER BY '+@PrimaryKey +' DESC FOR XML PATH(''ROW''),ROOT(''LIST'')); RETURN; END ELSE IF(@Start='''' OR @Limit='''') BEGIN SELECT ''<LIST></LIST>''; RETURN; END EXEC WSXML_Pagation '''+@TableName +''','''+@PrimaryKey +''',@Start,@Limit,'''',1 RETURN; END --首先将xml转换为数据集 DECLARE @Pointer INT; EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@XML_XML; SELECT * INTO #TEMP FROM OPENXML(@Pointer, N''/ROOT/PARAMS/ROW'', 3) WITH( '+@Structure+' ); EXEC sp_xml_removedocument @Pointer; IF (@TYPE = ''CREATE'') --新增 BEGIN INSERT INTO PSPINFO ( '+replace(@Fields,@PrimaryKey+',','')+' ) SELECT '+replace(@Fields,@PrimaryKey+',','')+' FROM #TEMP; END ELSE IF (@TYPE = ''DELETE'') --删除,按照主键 BEGIN DELETE FROM '+@TableName +' WHERE '+@PrimaryKey +' IN (SELECT '+@PrimaryKey +' FROM #TEMP); END ELSE IF (@TYPE = ''UPDATE'') --更新 BEGIN UPDATE PSMINF SET '+@UpdateFields+' FROM '+@TableName +', #TEMP WHERE '+@TableName +'.'+@PrimaryKey +' = #TEMP.'+@PrimaryKey +'; END ELSE BEGIN COMMIT TRAN; SELECT ''<RESTULT><STATE>9</STATE><MSG>操作参数错误！</MSG></RESULT>''; RETURN; END END TRY BEGIN CATCH SET @tran_error = 1; SET @error_msg = ERROR_MESSAGE(); END CATCH IF (@tran_error > 0) BEGIN ROLLBACK TRAN; SELECT ''<RESTULT><STATE>9</STATE><MSG>'' + @error_msg + ''</MSG></RESULT>''; END ELSE BEGIN --没有异常，提交事务 COMMIT TRAN; SELECT ''<RESTULT><STATE>0</STATE><MSG>恭喜您，操作成功！</MSG></RESULT>''; END END '; PRINT @procSql; END
自动生成的SQL代码：
/************************************************************ * JackChain * CRUD操作，供Excel调用 * 参数：XML * 参数格式：<ROOT><OPTYPE>READ/CREATE/UPDATE/DELETE</OPTYPE><PARAMS>....</PRARMS></ROOT> * Time: 2013/5/7 16:58:53 ************************************************************/ CREATE PROC [dbo].[WSXML_PSMCINF](@XML NVARCHAR(max)) AS BEGIN DECLARE @XML_XML XML SET @XML_XML = CONVERT(XML,@xml) SET XACT_ABORT ON BEGIN TRAN DECLARE @tran_error INT; SET @tran_error = 0; DECLARE @error_msg VARCHAR(8000); BEGIN TRY DECLARE @TYPE VARCHAR(10) --首先判断操作类型 SELECT @TYPE = @XML_XML.value(N'(/ROOT/OPTYPE)[1]', 'VARCHAR(10)'); --如果是读取 IF (@TYPE = 'READ') BEGIN COMMIT TRAN; DECLARE @Start VARCHAR(10); DECLARE @Limit VARCHAR(10); SELECT @Start=@XML_XML.value(N'(/ROOT/PARAMS/START)[1]', 'VARCHAR(10)'); SELECT @Limit=@XML_XML.value(N'(/ROOT/PARAMS/LIMIT)[1]', 'VARCHAR(10)'); IF(@Start IS NULL OR @Limit IS NULL) BEGIN SELECT (SELECT * FROM PSMCINF ORDER BY SCRCID DESC FOR XML PATH('ROW'),ROOT('LIST')); RETURN; END ELSE IF(@Start='' OR @Limit='') BEGIN SELECT '<LIST></LIST>'; RETURN; END EXEC WSXML_Pagation 'PSMCINF','SCRCID',@Start,@Limit,'',1 RETURN; END --首先将xml转换为数据集 DECLARE @Pointer INT; EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@XML_XML; SELECT * INTO #TEMP FROM OPENXML(@Pointer, N'/ROOT/PARAMS/ROW', 3) WITH( SCRCID int, SCITNO varchar(15), SCCLID varchar(15), SCM3NO varchar(20), SCCLNM varchar(15), SCCRPE varchar(20), SCCRDT varchar(8), SCCRTM varchar(8), SCPURS int, SCTLQT decimal(18,6), SCTLNM int, SCJPEG varchar(100), ); EXEC sp_xml_removedocument @Pointer; IF (@TYPE = 'CREATE') --新增 BEGIN INSERT INTO PSPINFO ( SCITNO, SCCLID, SCM3NO, SCCLNM, SCCRPE, SCCRDT, SCCRTM, SCPURS, SCTLQT, SCTLNM, SCJPEG, ) SELECT SCITNO, SCCLID, SCM3NO, SCCLNM, SCCRPE, SCCRDT, SCCRTM, SCPURS, SCTLQT, SCTLNM, SCJPEG, FROM #TEMP; END ELSE IF (@TYPE = 'DELETE') --删除,按照主键 BEGIN DELETE FROM PSMCINF WHERE SCRCID IN (SELECT SCRCID FROM #TEMP); END ELSE IF (@TYPE = 'UPDATE') --更新 BEGIN UPDATE PSMINF SET PSMCINF.SCRCID = (CASE WHEN #TEMP.SCRCID IS NOT NULL THEN #TEMP.SCRCID ELSE PSMCINF.SCRCID END), PSMCINF.SCITNO = (CASE WHEN #TEMP.SCITNO IS NOT NULL THEN #TEMP.SCITNO ELSE PSMCINF.SCITNO END), PSMCINF.SCCLID = (CASE WHEN #TEMP.SCCLID IS NOT NULL THEN #TEMP.SCCLID ELSE PSMCINF.SCCLID END), PSMCINF.SCM3NO = (CASE WHEN #TEMP.SCM3NO IS NOT NULL THEN #TEMP.SCM3NO ELSE PSMCINF.SCM3NO END), PSMCINF.SCCLNM = (CASE WHEN #TEMP.SCCLNM IS NOT NULL THEN #TEMP.SCCLNM ELSE PSMCINF.SCCLNM END), PSMCINF.SCCRPE = (CASE WHEN #TEMP.SCCRPE IS NOT NULL THEN #TEMP.SCCRPE ELSE PSMCINF.SCCRPE END), PSMCINF.SCCRDT = (CASE WHEN #TEMP.SCCRDT IS NOT NULL THEN #TEMP.SCCRDT ELSE PSMCINF.SCCRDT END), PSMCINF.SCCRTM = (CASE WHEN #TEMP.SCCRTM IS NOT NULL THEN #TEMP.SCCRTM ELSE PSMCINF.SCCRTM END), PSMCINF.SCPURS = (CASE WHEN #TEMP.SCPURS IS NOT NULL THEN #TEMP.SCPURS ELSE PSMCINF.SCPURS END), PSMCINF.SCTLQT = (CASE WHEN #TEMP.SCTLQT IS NOT NULL THEN #TEMP.SCTLQT ELSE PSMCINF.SCTLQT END), PSMCINF.SCTLNM = (CASE WHEN #TEMP.SCTLNM IS NOT NULL THEN #TEMP.SCTLNM ELSE PSMCINF.SCTLNM END), PSMCINF.SCJPEG = (CASE WHEN #TEMP.SCJPEG IS NOT NULL THEN #TEMP.SCJPEG ELSE PSMCINF.SCJPEG END), FROM PSMCINF, #TEMP WHERE PSMCINF.SCRCID = #TEMP.SCRCID; END ELSE BEGIN COMMIT TRAN; SELECT '<RESTULT><STATE>9</STATE><MSG>操作参数错误！</MSG></RESULT>'; RETURN; END END TRY BEGIN CATCH SET @tran_error = 1; SET @error_msg = ERROR_MESSAGE(); END CATCH IF (@tran_error > 0) BEGIN ROLLBACK TRAN; SELECT '<RESTULT><STATE>9</STATE><MSG>' + @error_msg + '</MSG></RESULT>'; END ELSE BEGIN --没有异常，提交事务 COMMIT TRAN; SELECT '<RESTULT><STATE>0</STATE><MSG>恭喜您，操作成功！</MSG></RESULT>'; END END
