必须声明变量 可是我已经声明了 请帮助!!!
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.gbat_getnum
@tablename varchar(40),
@colname varchar(40),
@phonenum varchar(40) output,
@phonepsw varchar(40) output,
@yydz varchar(40) output,
@book_name varchar(40) output,
@book_mail varchar(40) output
as
set nocount on
begin tran
declare @SQL nvarchar(500)
set @sql="select top 1 @phonenum=phonenum ,@phonepsw=phonepsw ,@yydz=yydz, @book_name=book_name, @book_mail=book_mail from "+@tablename+" order by "+@colname+",newid()"
exec sp_executesql @sql,N'@phonenum varchar(40) output',@phonenum output
,N'@phonepsw varchar(40) output',@phonepsw output
,N'@yydz varchar(40) output',@yydz output
,N'@book_name varchar(40) output',@book_name output
,N'@book_mail varchar(40) output',@book_mail output
set @sql="update "+@tablename+" set "+@colname+"="+@colname+"+1 where phonenum="+@phonenum
exec(@sql)
commit tran
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
以上是存储过程 调用的时候提示 必须声明变量 '@phonepsw'。请帮忙,谢谢
------解决方案--------------------这样用sp_executesql:
ALTER PROCEDURE dbo.gbat_getnum
@tablename varchar(40),
@colname varchar(40),
@phonenum varchar(40) output,
@phonepsw varchar(40) output,
@yydz varchar(40) output,
@book_name varchar(40) output,
@book_mail varchar(40) output
as
set nocount on
begin tran
declare @SQL nvarchar(4000)
set @sql='select top 1 @phonenum=phonenum ,@phonepsw=phonepsw ,@yydz=yydz, @book_name=book_name, @book_mail=book_mail from '+@tablename+' order by '+@colname+',newid()'
exec sp_executesql @sql
,N'@phonenum varchar(40) output,@phonepsw varchar(40) output,@yydz varchar(40) output,@book_name varchar(40) output,@book_mail varchar(40) output'
,@phonenum output,@phonepsw output,@yydz output,@book_name output,@book_mail output
set @sql='update '+@tablename+' set '+@colname+'='+@colname+'+1 where phonenum='''+@phonen