有关诸存过程,在传表名与字段的过程之中老是通不过(100)

CREATE PROCEDURE getnumber @outnumber char(4) out AS declare @count char(4), @mytablename varchar(20) , @myfieldName varchar(20) set @count='0001'while exists(exec ( ' select * from '+@mytablename+' where '+ @myfieldName +'=@count'))beginset @count=@count+1if @count<10set @count='000'+@countELSE IF @count<100SET @count='00'+@countELSE IF @count<1000SET @count='0'+@countENDSET @outnumber =@countGO提示while exists(exec ( ' select * from '+@mytablename+' where '+ @myfieldName +'=@count'))有错误如何解决

alter PROCEDURE getnumber @outnumber char(4) out AS declare @count char(4), @mytablename varchar(20) , @myfieldName varchar(20) declare @tmp integerset @count='0001'Select @tmp = count(*) from syscolumnswhere id in (Select ID from sysobjects where name=@mytablename ) and name=@countwhile @tmp>0begin set @count=@count+1 if @count<10 set @count='000'+@count ELSE IF @count<100 SET @count='00'+@count ELSE IF @count<1000 SET @count='0'+@count Select @tmp = count(*) from syscolumns where id in (Select ID from sysobjects where name=@mytablename ) and name=@countENDSET @outnumber =@count

按你所说的znxia方法,却发现@myfieldName这参数(字段参数)没有进行处理,所以你的方法还是不行,请你再看看我的语句 ' select * from '+@mytablename+' where '+ @myfieldName +'=@count'

嗯，我理解错了。http://www.cnblogs.com/iger/archive/2008/07/28/1254967.html里面有动态sql的资料

看过了,但是while exists与exec联合使用方法还是无法解决(在传递字表参数与字段参数前题下) 按你以上的方法我试过以下ADOStPrdr.Close; ADOStPrdr.Parameters.Clear; ADOStPrdr.ProcedureName:='getnumber' ; ADOStPrdr.Parameters.CreateParameter('@mytablename',ftString,pdinput,20,0); ADOStPrdr.Parameters.CreateParameter('@myfieldName',ftString,pdinput,20,0); ADOStPrdr.Parameters.ParamByName('@mytablename').Value:='doctors'; ADOStPrdr.Parameters.ParamByName('@myfieldName').Value:='DR_myid'; ADOStPrdr.execproc; mydrid:=ADOStPrdr.Parameters.paramvalues['@outnumber']; with ADOQsearchdr do提示参数有误错!

CREATE PROCEDURE getnumber @outnumber char(4) out AS declare @count char(4), @mySql varchar(200) set @count='0001'if exists ( select @mySql)beginset @count=@count+1if @count<10set @count='000'+@countELSE IF @count<100SET @count='00'+@countELSE IF @count<1000SET @count='0'+@countENDSET @outnumber =@countGO把sql语句组织好动态传入就行了三

按浪人情哥说法则少了while exists循环语句还是不行

看过了,但是while exists与exec联合使用方法还是无法解决(在传递字表参数与字段参数前题下) .--------------你最开始的存储过程里面，好像就1个传出参数，没有传入参数。你可以先用 查询分析器 来测试存储过程。

全改为CREATE PROCEDURE getnumber1 @mytablename varchar(20) , @myfieldName varchar(20) , @outdrnumber char(4) out AS declare @count char(4)set @count='0001'while exists(exec ( ' select * from '+@mytablename+' where '+ @myfieldName +'=@count'))beginset @count=@count+1if @count<10set @count='000'+@countELSE IF @count<100SET @count='00'+@countELSE IF @count<1000SET @count='0'+@countENDSET @outdrnumber =@countGO还是提示错误156:在关键字’exec’附近有语法错误码。第4行:’)’附近有语法错误。我想可能是如何打''的问题了,各位大侠如何解决

终于测试通过了，100分真不好拿。alter PROCEDURE getnumber1 @mytablename varchar(20) , @myfieldName varchar(20) , @outdrnumber char(4) out AS declare @count char(4) declare @num int declare @sqls nvarchar(4000) set @count='0001'set @sqls='select @a=count(*) from '+@mytablename+' where '+ @myfieldName +'='''+@count+''''exec sp_executesql @sqls,N'@a int output',@num output while @num>0begin set @count=@count+1 if @count<10 set @count='000'+@count ELSE IF @count<100 SET @count='00'+@count ELSE IF @count<1000 SET @count='0'+@count set @sqls='select @a=count(*) from '+@mytablename+' where '+ @myfieldName +'='''+@count+'''' exec sp_executesql @sqls,N'@a int output',@num output ENDSET @outdrnumber =@countGO

谢谢znxia,好好学习,结题