今天看到论坛上有求通用插入存储过程的,主要想通过输入表名变量及值变量插入某表中记录
思路
1.sqlserver中类型名及类型id对应下表
--无长度
ntext 99
real 59
smalldatetime 58
smallint 52
smallmoney 122
sql_variant 98
text 35
timestamp 189
tinyint 48
uniqueidentifier 36
xml 241
int 56
--双位
numeric 108
decimal 106
--其它,单位带长度
2.根据传入表名定义插入字段及变量
CREATE PROCEDURE PRO_INSERT
@tblName Nvarchar(100), -- 表名
@strUpdate Nvarchar(4000), -- 更新内容
AS
DECLARE @EXEC nvarchar(4000) --最终执行语句
DECLARE @parm nvarchar(4000) --参数变量
DECLARE @col nvarchar(4000) --列字段变量
DECLARE @value nvarchar(4000) --值字段变量
SET @parm=''
SET @col=''
SET @value=''
----设置参数变量
SELECT @parm=@parm+N'@'+CAST(name as Nvarchar(20))+' '+cast(TYPE_NAME(xtype) as Nvarchar(20))+
CASE
WHEN
xtype in(35,36,48,52,56,58,59,98,99,122,189)
THEN
','
WHEN
xtype in(106,108)
THEN
'('+cast(length as varchar)+','+cast(xscale as varchar)+'),'
ELSE
'('+cast(length as varchar)+'),'
END
FROM
syscolumns WHERE object_name(id)=@tblName
----设置字段变量
SELECT @col=@col+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblName
----设置值变量
SELECT @value =@value+N'@'+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblName
SET @parm=LEFT(@parm,LEN(@parm)-1)
SET @col=LEFT(@col,LEN(@col)-1)
SET @value=LEFT(@value,LEN(@value)-1)
--设置最终执行语句
SET @EXEC='INSERT INTO '+@tblName+'('+@col+') VALUES ('+@value+')'
EXEC('DECLARE '+@parm+' EXECUTE sp_executesql N'''+@EXEC+''',N'''+@parm+''''+','+@strUpdate)
GO
--建立一表进行测试。
CREATE TABLE TESTTB
(
ID INT,
COL1 VARCHAR(50)
)
DECLARE @return_value int
EXEC @return_value = [dbo].[PRO_INSERT]
@tblName = N'TESTTB',
@strUpdate = N'1,''HELLO'''
SELECT 'Return Value' = @return_value
SELECT * FROM TESTTB
DROP TABLE TESTTB
--结果
ID COL1
----------- --------------------------------------------------
1 HELLO
(1 行受影响)
缺点:
此语句暂不支持太多字段。
此语句对有自增列的暂不支持
还需进一步改正