CREATE PROCEDURE community_down
(
@id INT
)
AS
--
DECLARE @Dnid INT
--
DECLARE @Dnorder_index INT
--
DECLARE @Thisorder_index INT
--
BEGIN TRAN
--
SELECT @Thisorder_index = ISNULL([order], 1)
FROM community
WHERE [id] = @id
--
IF EXISTS( SELECT [id]
FROM community
WHERE [order] > @Thisorder_index)
BEGIN
--
SELECT TOP 1 @Dnid = [id] , @Dnorder_index = [order]
FROM community
WHERE [order] > @Thisorder_index
ORDER BY [order] ASC
--
UPDATE community
SET [order] = @Thisorder_index
WHERE [id] = @Dnid
IF @@ERROR > 0
GOTO NeedRollback
--
UPDATE community
SET [order] = @Dnorder_index
WHERE [id] = @id
IF @@ERROR > 0
GOTO NeedRollback
--
--SELECT 1
END
ELSE IF EXISTS(SELECT [id]
FROM community
WHERE [order] = @Thisorder_index AND [id] <> @id )
BEGIN
--
UPDATE community
SET [order] = [order] + 1
WHERE [id] = @id
--
--
END
NeedRollback:
IF @@ERROR > 0 --OR @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRAN --
SELECT 0
END
ELSE
BEGIN
COMMIT TRAN --
SELECT 1
END
GO
===========================
问题:想把表名 community 也弄成参数,该如何做??
(
@id INT
)
AS
--
DECLARE @Dnid INT
--
DECLARE @Dnorder_index INT
--
DECLARE @Thisorder_index INT
--
BEGIN TRAN
--
SELECT @Thisorder_index = ISNULL([order], 1)
FROM community
WHERE [id] = @id
--
IF EXISTS( SELECT [id]
FROM community
WHERE [order] > @Thisorder_index)
BEGIN
--
SELECT TOP 1 @Dnid = [id] , @Dnorder_index = [order]
FROM community
WHERE [order] > @Thisorder_index
ORDER BY [order] ASC
--
UPDATE community
SET [order] = @Thisorder_index
WHERE [id] = @Dnid
IF @@ERROR > 0
GOTO NeedRollback
--
UPDATE community
SET [order] = @Dnorder_index
WHERE [id] = @id
IF @@ERROR > 0
GOTO NeedRollback
--
--SELECT 1
END
ELSE IF EXISTS(SELECT [id]
FROM community
WHERE [order] = @Thisorder_index AND [id] <> @id )
BEGIN
--
UPDATE community
SET [order] = [order] + 1
WHERE [id] = @id
--
--
END
NeedRollback:
IF @@ERROR > 0 --OR @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRAN --
SELECT 0
END
ELSE
BEGIN
COMMIT TRAN --
SELECT 1
END
GO
===========================
问题:想把表名 community 也弄成参数,该如何做??
set @TBName='CR_Before_'+cast(convert(varchar(100),getdate(),112) as nvarchar)--named as "CR_Before_20080321"
set @cmd='Select * into BackupDB02..' + @TBName + ' from CR_ClickDetail'
exec sp_executesql @cmd
TO:milo4210,igelf
==
你们说的办法我都用了,老是报一大堆问题。。
(
@id INT,
@TBName varchar(20)
)
AS
--
DECLARE @Dnid INT
--
DECLARE @Dnorder_index INT
--
DECLARE @Thisorder_index INT
--
BEGIN TRAN
--
SELECT @Thisorder_index = ISNULL([order], 1)
FROM @TBName
WHERE [id] = @id
--
IF EXISTS( SELECT [id]
FROM @TBName
WHERE [order] > @Thisorder_index)
BEGIN
--
SELECT TOP 1 @Dnid = [id] , @Dnorder_index = [order]
FROM @TBName
WHERE [order] > @Thisorder_index
ORDER BY [order] ASC
--
UPDATE @TBName
SET [order] = @Thisorder_index
WHERE [id] = @Dnid
IF @@ERROR > 0
GOTO NeedRollback
--
UPDATE @TBName
SET [order] = @Dnorder_index
WHERE [id] = @id
IF @@ERROR > 0
GOTO NeedRollback
--
--SELECT 1
END
ELSE IF EXISTS(SELECT [id]
FROM @TBName
WHERE [order] = @Thisorder_index AND [id] <> @id )
BEGIN
--
UPDATE @TBName
SET [order] = [order] + 1
WHERE [id] = @id
--
--
END
NeedRollback:
IF @@ERROR > 0 --OR @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRAN --
SELECT 0
END
ELSE
BEGIN
COMMIT TRAN --
SELECT 1
END
GO
FROM @TBName
WHERE [id] = @id 你的@TBName 是变量 ,怎么能放在SQL语句一起执行呢??应该是这样:@strSql = 'select ' + @Thisorder_index = ISNULL([order], 1) + ' from ' + @TBName + ' where id =' + str(@id)
大哥帮帮小弟吧。。动态执行Sql语句 搞了一天了老有问题
create table users(userid int,username varchar(100))
insert into users(userid,username) values(1,'bb')
go
Create Procedure Getlist
(
@tablename varchar(100), --表名,
@columnname varchar(100), --要更改的字段名,
@values varchar(100), --要更改的字段对应的新值
@index varchar(100), --要更改的记录对应的唯一标记字段
@indexvalue int --标记的值
)
As
Begin
declare @str nvarchar(1000)
set @str=N'update #1 set #2=''#3'' where #4=#5'
set @str=replace(@str,'#1', @tablename)
set @str=replace(@str,'#2', @columnname)
set @str=replace(@str,'#3', @values)
set @str=replace(@str,'#4', @index)
set @str=replace(@str,'#5', cast(@indexvalue as varchar(100)))
print @str
exec sp_ExecuteSql @str
End
Go
exec Getlist 'users','username','aa','userid',1
这样写是复杂了些,但是可以看的比较清楚,不是不可以实现,一般最好不用拼接字符串的方式,性能上不是很好