CREATE FUNCTION GetCIshu (@job_no AS VARCHAR (20), @NOID AS DECIMAL (10),@tablename AS VARCHAR(20) )
RETURNS decimal (2) AS
BEGIN
DECLARE @II AS DECIMAL (2)
DECLARE @CISHU AS DECIMAL (2)
DECLARE @NOID2 AS DECIMAL (10)
SET @II=0
DECLARE CURNOID CURSOR FOR SELECT NOID FROM '+@tablename+' WITH (NOLOCK) WHERE JOB_NO=@JOB_NO
OPEN CURNOID
FETCH NEXT FROM CURNOID INTO @ NOID2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @II=@II+1
IF @NOID2=@NOID
SET @CISHU=@II
FETCH NEXT FROM CURNOID INTO @ NOID2
END CLOSE CURNOID
DEALLOCATE CURNOID
return @CISHU
END
RETURNS decimal (2) AS
BEGIN
DECLARE @II AS DECIMAL (2)
DECLARE @CISHU AS DECIMAL (2)
DECLARE @NOID2 AS DECIMAL (10)
SET @II=0
DECLARE CURNOID CURSOR FOR SELECT NOID FROM '+@tablename+' WITH (NOLOCK) WHERE JOB_NO=@JOB_NO
OPEN CURNOID
FETCH NEXT FROM CURNOID INTO @ NOID2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @II=@II+1
IF @NOID2=@NOID
SET @CISHU=@II
FETCH NEXT FROM CURNOID INTO @ NOID2
END CLOSE CURNOID
DEALLOCATE CURNOID
return @CISHU
END
解决方案 »
- 救命啊!为什么我的数据库文件增长到80G?
- MSSQL2000 lock的简单探索
- 存储过程求解
- 求一sql语句,在线等
- 问个sql效率的问题
- 一个设计方案问题,求可行办法
- 求救!sql server 2000新建数据库错误!急!
- SQL Server 2005数据库同步 问题,按以下步骤做了,可是订阅数据库不能同步?请各位指教.先谢谢各位.
- 请问为什么在查询分析器中可以通过,转到存储过程就显示“不能使用空白的对象或列名。如果必要,请使用一个空格。不能使用空白的对象或列名。如果必要,请使用一个空格。”
- 急!装了一个使用SQL server的软件后,启动企业管理器无法联结原来的数据库了,如何恢复啊?
- 两张表字段一样怎么样把一张表备份到另一张表
- 帮忙转换成存储过程格式
这个语句搁哪都通不过,另字符串拼接动态语句不能在函数中用,改用存储过程
这样试试。CREATE FUNCTION GetCIshu (@job_no AS VARCHAR (20), @NOID AS DECIMAL (10),@tablename AS VARCHAR(20) )
RETURNS decimal (2) AS
BEGIN
DECLARE @II AS DECIMAL (2)
DECLARE @CISHU AS DECIMAL (2)
DECLARE @NOID2 AS DECIMAL (10)
SET @II=0
EXEC('SELECT * INTO #TEMP FROM '+@tablename)
DECLARE CURNOID CURSOR FOR SELECT NOID FROM #TEMP WITH (NOLOCK) WHERE JOB_NO=@JOB_NO
OPEN CURNOID
FETCH NEXT FROM CURNOID INTO @NOID2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @II=@II+1
IF @NOID2=@NOID
SET @CISHU=@II
FETCH NEXT FROM CURNOID INTO @NOID2
END CLOSE CURNOID
DEALLOCATE CURNOID
return @CISHU
END
改为 SELECT NOID FROM tablename WITH (NOLOCK) WHERE JOB_NO=@JOB_NO ,其中tablename是常量 还是不行啊
你的这个提示"无法从函数中访问临时表"