SQL--生成Insert,Update存储过程的过程
- CREATE PROC AddUpTable
- @tableName NVARCHAR(50),
- @fileName NVARCHAR(1000),
- @fileCount VARCHAR(8000),
- @addUpYN BIT,
- @whereStr NVARCHAR(1000) = NULL,
- @groupStr NVARCHAR(1000) = NULL,
- @inUpdateNum SmallInt OUTPUT
- AS
- declare @sqlStr VARCHAR(8000)
- declare @startFileName INT
- declare @endFileName INT
- declare @startFileCount INT
- declare @endFileCount INT
- IF @addUpYN = 1
- BEGIN
- SET @sqlStr = 'INSERT INTO ' + @tableName + ' (' + @fileName + ') VALUES (' + @fileCount + ')'
- END
- ELSE
- BEGIN
- SET @fileName = @fileName + ','
- SET @fileCount = @fileCount + '|||'
- SET @sqlStr = 'UPDATE ' + @tableName
- SET @startFileName = 1
- SET @startFileCount = 1
- SET @endFileName = CHARINDEX(',', @fileName, @startFileName)
- SET @endFileCount = CHARINDEX('|||', @fileCount, @startFileCount)
- SET @sqlStr = @sqlStr + ' SET '
- WHILE (@endFileName <> 0)
- BEGIN
- SET @sqlStr = @sqlStr + SUBSTRING(@fileName, @startFileName, @endFileName-@startFileName) + '='
- SET @startFileName = @endFileName + 1
- SET @endFileName = CHARINDEX(',', @fileName, @startFileName)
- SET @sqlStr = @sqlStr + '''' + SUBSTRING(@fileCount, @startFileCount, @endFileCount-@startFileCount) + ''','
- SET @startFileCount = @endFileCount + 3
- SET @endFileCount = CHARINDEX('|||', @fileCount, @startFileCount)
- END
- SET @sqlStr = SUBSTRING(@sqlStr, 1, LEN(@sqlStr)-1)
- IF (@whereStr != '')
- BEGIN
- SET @sqlStr = @sqlStr + ' WHERE ' + @whereStr
- END
- IF (@groupStr != '')
- BEGIN
- SET @sqlStr = @sqlStr + ' GROUP BY ' + @groupStr
- END
- END
- PRINT @sqlStr
- BEGIN TRANSACTION
- EXEC(@sqlStr)
- IF @@error=0
- BEGIN
- COMMIT TRANSACTION
- SET @inUpdateNum = 1
- END
- ELSE
- BEGIN
- ROLLBACK TRANSACTION
- SET @inUpdateNum = 0
- END
- GO