提供ID(主键 int)和表名,插入新纪录到表中,新纪录的ID:@ID int,表名:@TableName varchar(40)
其它字段都是null
ALTER PROCEDURE [dbo].[insert_EmptyValue]
@ID int,
@TableName varchar(40)
AS
BEGIN
declare @sql nvarchar(1000)
declare @count int
declare @value varchar(1000)
set @sql='select @count=count(ID) from syscolumns Where ID=OBJECT_ID('''+@TableName+''')'
print @sql
exec sp_executesql @sql,N'@count as int output)',@count output
set @value='null'
set @count=@count-2
while @count>0
begin
set @value=@value+',null'
set @count=@count-1
end
set @sql='insert into '+@TableName +' values(@ID,@value)'
print @sql
exec sp_executesql @sql,N'@ID as int,@value as varchar(1000)',@ID,@value
ENDgo
exec insert_EmptyValue 5,'tb_user'错误提示
select @count=count(ID) from syscolumns Where ID=OBJECT_ID('tb_user')
消息 102,级别 15,状态 1,第 1 行
')' 附近有语法错误。
insert into tb_user values(@ID,@value)
消息 102,级别 15,状态 1,第 1 行
',' 附近有语法错误。
消息 137,级别 15,状态 2,第 1 行
必须声明标量变量 "@ID"。
其它字段都是null
ALTER PROCEDURE [dbo].[insert_EmptyValue]
@ID int,
@TableName varchar(40)
AS
BEGIN
declare @sql nvarchar(1000)
declare @count int
declare @value varchar(1000)
set @sql='select @count=count(ID) from syscolumns Where ID=OBJECT_ID('''+@TableName+''')'
print @sql
exec sp_executesql @sql,N'@count as int output)',@count output
set @value='null'
set @count=@count-2
while @count>0
begin
set @value=@value+',null'
set @count=@count-1
end
set @sql='insert into '+@TableName +' values(@ID,@value)'
print @sql
exec sp_executesql @sql,N'@ID as int,@value as varchar(1000)',@ID,@value
ENDgo
exec insert_EmptyValue 5,'tb_user'错误提示
select @count=count(ID) from syscolumns Where ID=OBJECT_ID('tb_user')
消息 102,级别 15,状态 1,第 1 行
')' 附近有语法错误。
insert into tb_user values(@ID,@value)
消息 102,级别 15,状态 1,第 1 行
',' 附近有语法错误。
消息 137,级别 15,状态 2,第 1 行
必须声明标量变量 "@ID"。
DECLARE @i INT
EXEC('set @i=1')
不能这样用,错误:Must declare the scalar variable "@i".
set @sql='insert into '+@TableName +' values('+CAST(@ID AS VARCHAR)+','+@value+')'
@ID int,
@TableName varchar(40)
AS
BEGIN
declare @sql nvarchar(1000)
declare @count int
declare @value varchar(1000)
set @sql='select @count=count(ID) from syscolumns Where ID=OBJECT_ID('''+@TableName+''')'
print @sql
exec sp_executesql @sql,N'@count as int output',@count output
set @value='null'
set @count=@count-2
while @count>0
begin
set @value=@value+',null'
set @count=@count-1
end
set @sql='insert into '+@TableName +' values(@ID,'+@value+')'
exec sp_executesql @sql,N'@ID as int',@ID
END
go
exec insert_EmptyValue 5,'tb_user'
print @sql
exec sp_executesql @sql,N'@count as int output)',@count output
不用拼动态的
直接
select @count=count(ID) from syscolumns Where ID=OBJECT_ID(@TableName)