SQL代码生成器-优快云博客

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--名称：SQL代码生成器

--功能：生成单表的insert,update,语句，变量定义语句；程序默认第一个列为主键;减少开发者输入的时间
--EXEC GetSql 'ord_TdContract',3

ALTER                 PROCEDURE [dbo].[GetSql]
@TableName VARCHAR(255), --表名
@sqltype int ,  --1插入SQL;2更新SQL;3变量定义;4存储过程变量定义,5简单格式的查询语句;6生成初始化数据的语句;7生成赋值语句,8带中文别名的查询
@Para  varchar(100)='',
@strMessage varchar(4000)=NULL output
AS
DECLARE @strSql1 VARCHAR(8000)
DECLARE @strsql2 VARCHAR(8000)
DECLARE @TID INT
DECLARE @I INT
DECLARE @cNUM INT
DECLARE @cName varchar(100)
DECLARE @var VARCHAR(100)
DECLARE @datatype VARCHAR(100)
DECLARE @intRowCount int
DECLARE @strNull varchar(10)
DECLARE @xtype INT

SET @I=1
SET @strSql2=''
SET @strSql1=''
IF @sqltype=1
BEGIN
SET @strSql1='INSERT INTO '+@TableName
SET @strSql2='
SELECT '
END
IF @sqltype=2
SET @strSql1='UPDATE '+@TableName+' SET '
IF @sqltype IN (5,7,8)
SET @strSql1='SELECT '

SET @TID=object_id(@TableName )

SELECT @intRowCount=COUNT(*) FROM syscolumns WHERE ID=@TID

DECLARE authors_cursor CURSOR FOR SELECT Name,dbo.bf_GetBaseType(xtype,prec,xprec,xscale) ,xtype
FROM dbo.syscolumns WHERE ID=@TID ORDER BY colid

OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @cName, @datatype,@xtype
WHILE @@FETCH_STATUS = 0
BEGIN
IF @sqltype=1
BEGIN
  SET @strSql1=@strSql1+CASE @I WHEN 1 THEN '('+@cName WHEN @intRowCount THEN ','+@cName+')' ELSE ','+@cName END
  SET @strSql2=@strSql2+'@'+@cName+CASE @I WHEN @intRowCount THEN '' ELSE ',' END
END
IF @sqltype=2
BEGIN
  IF @I=1
   SET @strSql2=@strSql2+'   WHERE '+@cName+'='+'@'+@cName+'  --'+dbo.bf_GetColProp(@tablename,@cName)
  ELSE
  BEGIN
   SET @strSql1=@strSql1+@cName+'='+'@'+@cName+CASE @I WHEN @intRowCount THEN '' ELSE ',' END
   SET @strSql1=@strSql1+' --'+dbo.bf_GetColProp(@tablename,@cName)+'
    '   --换行
  END
END
IF @sqltype=3
  SET @strSql1=@strSql1+'DECLARE '+'@'+@cName+' '+@datatype+' --'+dbo.bf_GetColProp(@tablename,@cName)+ '
'
IF @sqltype=4
  SET @strSql1=@strSql1+'@'+@cName+' '+@datatype+ ','+'  --'+dbo.bf_GetColProp(@tablename,@cName)+'
'
IF @sqltype=5
  SET @strSql1=@strSql1+CASE @I WHEN 1 THEN @cName WHEN @intRowCount THEN ','+@cName+ ' FROM '+@TableName   ELSE ','+@cName END
IF @sqltype=6
BEGIN
  IF @xtype IN (61)
   SET @strNull='1990-01-01'
  IF @Xtype in (62,56,108)
   SET @strNull='0'
  IF @Xtype IN (175,167)
   SET @strNull=''
  SET @strSql1=@strSql1+CASE @I WHEN 1 THEN 'SELECT '+'''INSERT INTO '+@TableName +' SELECT ''''''+ CONVERT(VARCHAR(1000),ISNULL('+@cName+','''+@strNull+'''))'

WHEN @intRowCount THEN +'+'''''',''''''+CONVERT(VARCHAR(1000),ISNULL('+@cName+','''+@strNull+'''))' +'+'''''''' FROM '+@TableName

ELSE '+'''''',''''''' +'+ CONVERT(VARCHAR(1000),ISNULL('+@cName+','''+@strNull+'''))' END

END
IF @sqltype=7
BEGIN
  SET @strSql1=@strSql1 +'@'+@cName+'='+case when @Xtype in (175,167) then 'RTRIM(ISNULL('+@cName+',''''))' else @cName end +case @i when @intRowCount then ' FROM '+@TableName else ',' end
END
IF @sqltype=8
BEGIN
  SET @strSql1=@strSql1+CASE @I WHEN 1 THEN @cName+' '+dbo.bf_GetColProp(@tablename,@cName)
     WHEN @intRowCount THEN ','+@cName+' '+dbo.bf_GetColProp(@tablename,@cName)+ ' FROM '+@TableName
     ELSE ','+@cName+' '+dbo.bf_GetColProp(@tablename,@cName) END

END

SET @I=@I+1
FETCH NEXT FROM authors_cursor INTO @cName, @datatype,@xtype
END
CLOSE authors_cursor
DEALLOCATE authors_cursor

PRINT @strSql1
PRINT @strSql2

SET @strMessage=@strSql1+'
'+@strSql2

IF @sqltype=6
BEGIN
IF @Para<>''
set @strMessage=@strMessage+' WHERE '+@Para
EXEC (@strMessage)
END
IF @sqltype=9
BEGIN
SELECT 'TRUNCATE TABLE '+NAME+'
'+'UPDATE xs_Systemcoding SET maxcode=0 WHERE codingobject='''+NAME+'''' FROM dbo.sysobjects WHERE name like @TableName+'%' AND TYPE='U'
END