SQL server创建带有游标的存储过程 使用表名作参数 执行找不到游标

goodbaby728 2010-06-12 11:19:13
如题，小弟用表名作参数@table，并且传入列名作第二个参数@rst_col_name，然后返回本列中最大值到输出参数max_id



USE iTao

	IF EXISTS (SELECT name FROM sysobjects

	WHERE name ='max_first_column'AND type ='P')

	DROP PROCEDURE max_first_column 

	GO

	create procedure max_first_column

	@table varchar(20),

	@rst_col_name varchar(20),

	@max_id	int output

	as

		declare @sql varchar(500)

		begin

			set @sql='declare cur_max cursor for select max('+@rst_col_name+')+from '+@table

			exec (@sql)

			open cur_max

			fetch next from cur_max

			into @max_id

			while @@FETCH_STATUS=0

			begin

				fetch next from cur_max

				into @max_id

				select @max_id

			end

			close cur_max

			deallocate cur_max

			set @max_id=@max_id+1

			return

		end

	go

	declare @max_id2 int

	execute max_first_column

	"Department","DeptID",@max_id=@max_id2 output

	print 'The max value is '+convert(varchar(10),@max_id2)


如果不执行

declare @max_id2 int
execute max_first_column
"Department","DeptID",@max_id=@max_id2 output
print 'The max value is '+convert(varchar(10),@max_id2)

则完全没有问题，但一执行，就提示“名为 'cur_max' 的游标不存在”

不知道什么原因啊，项目紧急需要~希望各路高手帮帮忙啊
注：不知小弟这个东西能不能不用游标实现啊？因为只有一条记录
...全文
525 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
goodbaby728 2010-06-15
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 htl258 的回复:]
SQL code
IF OBJECT_ID('Department') IS NOT NULL
DROP TABLE Department
GO
CREATE TABLE Department
(
DeptID INT
,DeptName VARCHAR(20) NOT NULL
,FatherID INT DEFAULT(0)
……
[/Quote]
不好意思，一时仓促，给错代码了，把修改以前的传上去了~完整的创建代码如下


Create table Department

(

	DeptID		int	check((dbo.RegexMatch(DeptID,'^[1-9]\d*$')=1)),

	DeptName	varchar(20) NOT NULL,

	FatherID	int  check(([dbo].[RegexMatch](FatherID,'^([1-9]\d*)$')=1)),

	SchoolName	varchar(20) NOT NULL,

	primary key	(DeptID), 

foreign key (FatherID) references Department

)

看了楼上朋友的代码很受启发，想想：每次的更新肯定是先删除再插入的，所以EXISTS(SELECT 1 FROM deleted)以及from inserted 一定存在吧？我把自己的触发器稍微修改如下：


use iTao

IF OBJECT_ID('tri_upd_dept') IS NOT NULL

	drop trigger tri_upd_dept

go

create trigger tri_upd_dept

on Department after update

as

SET NOCOUNT ON

if update(DeptID)

	begin

                ALTER TABLE Department 

                NOCHECK CONSTRAINT FK_Department_Department --我的里面的外码约束命名为 

                                                                  --FK_Department_Department 

		declare @n_id int,@o_id int 

		select @n_id=DeptID from inserted

		select @o_id=DeptID from deleted

		update Department set FatherID=@n_id

		where  FatherID=@o_id

                ALTER TABLE Department 

                 CHECK CONSTRAINT FK_Department_Department 

  

	end

go

为什么这样不对呢？可能我的理解还有很大问题，期待会者再次指教~
maomaohr 2010-06-15
  • 打赏
  • 举报
 回复
学习一下..
htl258_Tony 2010-06-15
  • 打赏
  • 举报
 回复 
--需要修改一下：



IF OBJECT_ID('Department') IS NOT NULL

    DROP TABLE Department

GO

CREATE TABLE Department

(

    DeptID      INT

   ,DeptName    VARCHAR(20) NOT NULL

   ,FatherID    INT DEFAULT(0)

   --,SchoolName  VARCHAR(20) NOT NULL

   ,PRIMARY KEY(DeptID)

   ,CONSTRAINT FK__Departmen__Fathe__5E35CF0F

    FOREIGN KEY(FatherID) REFERENCES Department(DeptID) 

)



INSERT INTO Department

SELECT 1,'a',1 UNION ALL

SELECT 2,'b',1 UNION ALL

SELECT 3,'c',2 UNION ALL

SELECT 4,'d',2



IF OBJECT_ID('tr_test') IS NOT NULL

    DROP TRIGGER tr_test

GO

CREATE TRIGGER tr_test ON Department

INSTEAD OF UPDATE

AS

SET XACT_ABORT ON 

BEGIN TRAN

    IF UPDATE(Deptid) -------------这里加个条件

    ALTER TABLE Department 

        NOCHECK CONSTRAINT FK__Departmen__Fathe__5E35CF0F

        

    IF EXISTS(SELECT 1 FROM deleted)

    

    BEGIN

        DECLARE @t TABLE(Deptid INT,Deptname VARCHAR(20),FatherID INT)

        DELETE a

        FROM   Department a, deleted d

        WHERE  a.DeptID = d.DeptID

        

        DELETE Department 

               OUTPUT deleted.* INTO @t

        WHERE  EXISTS(

                   SELECT 1 

                   FROM deleted

                   WHERE Department.FatherID = DeptID

               )

    END

    IF EXISTS(SELECT 1 FROM inserted)

    BEGIN 

        INSERT INTO Department

            SELECT * FROM inserted

        INSERT INTO Department

            SELECT b.DeptID,b.Deptname,a.DeptID 

            FROM   inserted a

                JOIN @t b

                    ON 1=1

    END

    ALTER TABLE Department 

        CHECK CONSTRAINT FK__Departmen__Fathe__5E35CF0F

COMMIT

GO



UPDATE Department

SET    DeptID = 5

WHERE  DeptID = 2



SELECT * FROM Department 

/*

DeptID      DeptName             FatherID

----------- -------------------- -----------

1           a                    1

3           c                    5

4           d                    5

5           b                    1



(4 行受影响)

*/
htl258_Tony 2010-06-15
  • 打赏
  • 举报
 回复 
IF OBJECT_ID('Department') IS NOT NULL

	DROP TABLE Department

GO

CREATE TABLE Department

(

	DeptID      INT

   ,DeptName    VARCHAR(20) NOT NULL

   ,FatherID    INT DEFAULT(0)

   --,SchoolName  VARCHAR(20) NOT NULL

   ,PRIMARY KEY(DeptID)

   ,CONSTRAINT FK__Departmen__Fathe__5E35CF0F

    FOREIGN KEY(FatherID) REFERENCES Department(DeptID) 

)



INSERT INTO Department

SELECT 1,'a',1 UNION ALL

SELECT 2,'b',1 UNION ALL

SELECT 3,'c',2 UNION ALL

SELECT 4,'d',2



IF OBJECT_ID('tr_test') IS NOT NULL

	DROP TRIGGER tr_test

GO

CREATE TRIGGER tr_test ON Department

INSTEAD OF UPDATE

AS

SET XACT_ABORT ON 

BEGIN TRAN

	

	ALTER TABLE Department 

		NOCHECK CONSTRAINT FK__Departmen__Fathe__5E35CF0F

		

	IF EXISTS(SELECT 1 FROM deleted)

	

	BEGIN

		DECLARE @t TABLE(Deptid INT,Deptname VARCHAR(20),FatherID INT)

		DELETE a

		FROM   Department a, deleted d

		WHERE  a.DeptID = d.DeptID

		

		DELETE Department 

		       OUTPUT deleted.* INTO @t

		WHERE  EXISTS(

		           SELECT 1 

		           FROM deleted

		           WHERE Department.FatherID = DeptID

		       )

	END

	IF EXISTS(SELECT 1 FROM inserted)

	BEGIN 

		INSERT INTO Department

			SELECT * FROM inserted

		INSERT INTO Department

			SELECT b.DeptID,b.Deptname,a.DeptID 

			FROM   inserted a

				JOIN @t b

					ON 1=1

	END

	ALTER TABLE Department 

		CHECK CONSTRAINT FK__Departmen__Fathe__5E35CF0F

COMMIT

GO



UPDATE Department

SET    DeptID = 5

WHERE  DeptID = 2



SELECT * FROM Department 

/*

DeptID      DeptName             FatherID

----------- -------------------- -----------

1           a                    1

3           c                    5

4           d                    5

5           b                    1



(4 行受影响)

*/
goodbaby728 2010-06-14
  • 打赏
  • 举报
 回复
小弟又遇到个问题


Create table Department

(

	DeptID		int	check((dbo.RegexMatch(DeptID,'^[1-9]\d*$')=1)),

	DeptName	varchar(20) NOT NULL,

	FatherID	int default(0) check(([dbo].[RegexMatch](FatherID,'^([1-9]\d*)$')=1)),

	SchoolName	varchar(20) NOT NULL,

	primary key	(DeptID), 

foreign key (DeptID) references Department

)

其中两个check就是判定FatherID为正整数，可直接忽视
问题是，我在Department表中，FatherID引用了同一表中的DeptID，这样没法级联更新，说是会引发无限循环之类的，于是我就写了个触发器，每当更新DeptID是会把以原来以该DeptID为FatherID的记录的FatherID更新为新的DeptID（比如有两条条记录为“1，信息学院，1“、“5，计算机系，1”，当我把第一条记录的DeptID 1改为2后，两条记录自动变为“2，信息学院，2“、“5，计算机系，2”~
可是现在只要我一修改被其他记录FatherID引用的DeptID，就会提示如下
Update 语句与SAME TABLE REFERENCE 约束"FK_Department_Department"冲突，该冲突发生于dbo.Department.FatherID
我想应该是外键自身引用的问题，我把外键约束删了就不会有问题的，但在我的数据库中外键是需要的，请问有办法解决吗？我想在修改时能不能先执行我的触发器(把相应的FatherID先修改为新值),再执行外码约束啊~
csw200201 2010-06-13
  • 打赏
  • 举报
 回复
#2楼 and #3楼 have already given you the correct ways of doing what you want.

The reason that your cursor does not work is because when you use
exec (@sql)

Any variables, including cursor variables are local within the exec statement. Hence you will get the cursor invalid error message when you attempt to use the cursor declared inside @sql.
--小F-- 2010-06-13
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 goodbaby728 的回复:]
各位的回答让我大受裨益，都是高手啊，看来小弟在sql server上的路还很长~但是有一点不解，2#的
"set @sql=@sql+N'select @v=max('+cast(@colName as nvarchar(50))+') from '+cast(@table as nvarchar(50))"
conName table不是已经是nvarchar类型的吗？为什么还要cast？
[/Quote]

估计是习惯性动作了
hfCoder 2010-06-13
  • 打赏
  • 举报
 回复
mark
goodbaby728 2010-06-13
  • 打赏
  • 举报
 回复
各位的回答让我大受裨益，都是高手啊，看来小弟在sql server上的路还很长~但是有一点不解，2#的
"set @sql=@sql+N'select @v=max('+cast(@colName as nvarchar(50))+') from '+cast(@table as nvarchar(50))"
conName table不是已经是nvarchar类型的吗？为什么还要cast？
永生天地 2010-06-13
  • 打赏
  • 举报
 回复 




USE iTao

go

    IF EXISTS (SELECT name FROM sysobjects

    WHERE name ='max_first_column'AND type ='P')

    DROP PROCEDURE max_first_column 

    GO

    create procedure max_first_column

    @table varchar(20),

    @rst_col_name varchar(20),

    @max_id    int output

    as

        declare @sql nvarchar(500)

        begin

            set @sql='select @max_id=max('+@rst_col_name+')+from '+@table

            exec sp_executesql @sql,N'@max_id int output',@max_id output

            open cur_max

            set @max_id=isnull(@max_id,0)+1

            return

        end

    go

    declare @max_id2 int

    execute max_first_column

    "Department","DeptID",@max_id=@max_id2 output

    print 'The max value is '+convert(varchar(10),@max_id2)
gw6328 2010-06-12
  • 打赏
  • 举报
 回复 


use db

go

create table tbx(

	id int primary key identity(1,1),

	v int 

)

use db

go

insert tbx select 1 union all

select 3 union all 

select 4 union all

select 2 



go



select * from tbx

use db

go

create procedure up_test

	@table nvarchar(100),

	@colName nvarchar(100),

	@value int output

as

	declare @sql nvarchar(1000)

	declare @v int

	set @sql=''

	set @sql=@sql+N'select @v=max('+cast(@colName as nvarchar(50))+') from '+cast(@table as nvarchar(50))



	exec sp_executesql @sql,N'@v int output',@v=@v output 

	set @value=@v



go







declare @v int

exec up_test N'tbx',N'v',@v output

select @v





drop procedure up_test

drop table tb
dawugui 2010-06-12
  • 打赏
  • 举报
 回复
存储过程?游标,还有动态表名?

貌似玩得太高雅了,帮顶.
SQL Server 2014 索引和游标
主讲内容： 第一讲 课程简介 第二讲 索引简介 第三讲 创建索引 第四讲 查看索引 第五讲 索引管理 第六讲 全文索引 等课程
SQL Server存储过程中游标的使用
最近在熟悉公司代码时遇到了一些SQL数据库存储过程的使用，在存储过程中遇到了游标cursor，之前对游标不怎么了解，现整理一些关于游标的知识共享。 1、关于游标： 游标：用来查询数据库，获取记录集合（结果集）的指针，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。 2、游标作用： 就是用于临时存储从数据库中提取的数据块。在某些情况下，需
SqlServer游标的创建与使用
这里写自定义目录标题前言什么是游标1、游标的概念2、游标的组成3、游标的分类API服务器游标Transaction-SQL游标客户端游标怎么使用游标1、声明游标2、打开游标对象3、读取游标数据4、关闭游标5、释放游标6、修改游标数据7、删除游标数据完整实例什么时候使用游标游标使用的建议 前言 大家都对SqlServer视图、存储过程、触发器的创建与使用有一定的了解了，我们来看下什么是游标，怎么使用，什么时候用。 什么是游标 1、游标的概念 游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的
数据库 SQL Server 游标 存储过程 存储函数 触发器
文章目录1 游标2 存储过程3 触发器 1 游标 2 存储过程 3 触发器
SqlServer 总结(1) 游标
在做项目的过程中。我发现有许多地方有用到游标的方式去实现功能效果的。所以，整理了有关常用的实现游标的方式。 一、什么是游标 维基百科中事这样定义游标的。游标是处理结果集的一种机制 ,而结果集就是select查询返回的所有行数据的集合。 对于我而言，用通俗的话来讲，就是把自己需要用到的数据先放到一个容器里面，然后循环遍历这个容器里面的所有行和列的一个操作。 二、游标的用处 1.定位到结果集中...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章