如何用一个存储过程实现多个表的增删改查?
要实现的是:从程序里接收的参数是表名,通过判断接收到的表名在存储过程里判断该表的字段类型和数量并且对该表进行增删改查的判断!(请给出详细解答)谢谢了!在线等,急,解决马上结贴~~
要实现的是:从程序里接收的参数是表名,通过判断接收到的表名在存储过程里判断该表的字段类型和数量并且对该表进行增删改查的判断!(请给出详细解答)谢谢了!在线等,急,解决马上结贴~~
调试欢乐多
select distinct sysobjects.[name] as [tblname], syscolumns.colid, syscolumns.[name] as [colname]
from syscolumns, sysobjects where sysobjects.id=syscolumns.id and sysobjects.[name]='aa' group by sysobjects.[name],syscolumns.colid, syscolumns.[name] /*取出该表的列*/select distinct sysobjects.[name] as [tblname], syscolumns.colid, syscolumns.[name] as [colname]
into #temp7 from syscolumns, sysobjects where sysobjects.id=syscolumns.id and sysobjects.[name]='aa' group by sysobjects.[name],syscolumns.colid, syscolumns.[name]
select count(*)as [count] from #temp7 /*取出该表列的总数*/declare @i int
set @i = 0
while @i < 100
begin
update aa set [count] = @i where ID_column = @i
set @i = @i + 1
end /*循环该表的列*/(没有实现)@DataAction int /*以下是想做增删改查的判断*/(没写完)
AS
begin tran
SET NOCOUNT ON
if @DataAction=0
begin
insert into aa
(
[ID],
[Name],
[Password]
)
values
(
@ID,
@Name,
@Password
)
set
@ID=scope_identity()
end
if @DataAction=1
begin
Update [aa] SET
[ID] = @ID,
[Name] = @Name,
[Password] = @Password
Where
[ID] = @ID
end
if @DataAction=2
begin
delete from [aa] where [ID] = @ID
end
if @DataAction=3
begin
select
[ID],
[Name],
[Password]
from [aa]
where
[ID] = @ID
end
if @@error<>0 goto sqlerr
commit tran
return
sqlerr:
rollback
SET NOCOUNT OFF
GO