我试过用 set @str=N' select * from '+@tttt+' where CUI=@ffff ' 还是一样的错误 运行时系统总是报 ****************************** 必须声明变量 '@tttt'。 没有行受影响。 (返回 0 行) @RETURN_VALUE = 0 完成 dbo."ebsp_HasChild" 运行。 ******************************
ALTER PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100), @theFirstChildCUI int AS declare @str as nvarchar(4000) set @str=N' select * from tableA where CUI=@ffff ' exec sp_executesql @str, N'@ffff int ', N' @ffff=@theFirstChildCUI' 好象你的存储过程里的确没有声明@ffff啊,要不就是我眼花了 ^_^
--这样试试看呢ALTER PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100), @theFirstChildCUI int AS declare @str as nvarchar(4000) set @str='select * from '+@tableName+' where CUI='+cast(@theFirstChildGUI as varchar) exec sp_executesql @str,N'@tableName varchar(100),@theFirstChildGUI int ',@tableName ,@theFirstChildGUI
谢谢。已搞定。原因是什么我还是有点糊涂。我改成以下两种形式就可以了 ************************************************************ ALTER PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100), @theFirstChildCUI int --@hasChild bit output AS --exec sp_executesql N'select * from ec_n_1' declare @str as nvarchar(4000) set @str=N'select * from ' + @tableName + ' where CUI=@ffff' /* 把表名作为变量有+ 串上*/ select @str exec sp_executesql @str,N'@t nvarchar(100),@ffff int ', @tableName,@theFirstChildCUI /*去掉了前面的N' */ ************************************************************ ************************************************************ ALTER PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100), @theFirstChildCUI int --@hasChild bit output AS --exec sp_executesql N'select * from ec_n_1' declare @str as nvarchar(4000) set @str=N'select * from ' + @tableName + ' where CUI=@theFirstChildCUI ' select @str exec sp_executesql @str,N'@t nvarchar(100),@theFirstChildCUI int ', @tableName,@theFirstChildCUI /*去掉了前面的N' */ ************************************************************
alter PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100), @theFirstChildCUI int as declare @str as nvarchar(4000) set @str = N'select * from ' + @tableName + ' where id = '+ cast(@theFirstChildCUI as nvarchar) --exec (@str) exec sp_executesql @str,N'@tableName varchar(100),@theFirstChildGUI int ',@tableName,@theFirstChildCUI
alter PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100), @theFirstChildCUI int as declare @str as nvarchar(4000) --declare @theFirstChildCUI int set @str = N'select * from ' + @tableName + ' where id = @theFirstChildCUI' exec sp_executesql @str,N'@theFirstChildCUI int',@theFirstChildCUI
这个问题已解决,但是我还要加个参数 @hasChild bit output ,如果 exist(select * from...) then set @hasChild =1 else set @hasChild=0 ,请问这个动态sp 怎么写?
alter PROCEDURE dbo.ebsp_HasChild @tableName nvarchar(100) ,@theFirstChildCUI int ,@hasChild bit out as declare @str as nvarchar(4000) set @hasChild = 0 set @str = N'select @hasChild = 1 from ' + @tableName + ' where CUI = @theFirstChildCUI' exec sp_executesql @str,N'@theFirstChildCUI int,@hasChild bit out',@theFirstChildCUI,@hasChild outgodeclare @ bit exec ebsp_HasChild N'TableName',103,@ out select @
还是一样的错误
运行时系统总是报
******************************
必须声明变量 '@tttt'。
没有行受影响。
(返回 0 行)
@RETURN_VALUE = 0
完成 dbo."ebsp_HasChild" 运行。
******************************
@tableName nvarchar(100),
@theFirstChildCUI int
AS
declare @str as nvarchar(4000)
set @str=N' select * from tableA where CUI=@ffff '
exec sp_executesql @str,
N'@ffff int ',
N' @ffff=@theFirstChildCUI'
好象你的存储过程里的确没有声明@ffff啊,要不就是我眼花了 ^_^
@tableName nvarchar(100),
@theFirstChildCUI int
AS
declare @str as nvarchar(4000)
set @str='select * from '+@tableName+' where CUI='+cast(@theFirstChildGUI as varchar)
exec sp_executesql @str,N'@tableName varchar(100),@theFirstChildGUI int ',@tableName ,@theFirstChildGUI
************************************************************
ALTER PROCEDURE dbo.ebsp_HasChild
@tableName nvarchar(100),
@theFirstChildCUI int
--@hasChild bit output
AS
--exec sp_executesql N'select * from ec_n_1'
declare @str as nvarchar(4000)
set @str=N'select * from ' + @tableName + ' where CUI=@ffff' /* 把表名作为变量有+ 串上*/
select @str
exec sp_executesql @str,N'@t nvarchar(100),@ffff int ',
@tableName,@theFirstChildCUI /*去掉了前面的N' */
************************************************************
************************************************************
ALTER PROCEDURE dbo.ebsp_HasChild
@tableName nvarchar(100),
@theFirstChildCUI int
--@hasChild bit output
AS
--exec sp_executesql N'select * from ec_n_1'
declare @str as nvarchar(4000)
set @str=N'select * from ' + @tableName + ' where CUI=@theFirstChildCUI '
select @str
exec sp_executesql @str,N'@t nvarchar(100),@theFirstChildCUI int ',
@tableName,@theFirstChildCUI /*去掉了前面的N' */
************************************************************
@tableName nvarchar(100),
@theFirstChildCUI int
as
declare @str as nvarchar(4000)
set @str = N'select * from ' + @tableName + ' where id = '+ cast(@theFirstChildCUI as nvarchar)
--exec (@str)
exec sp_executesql @str,N'@tableName varchar(100),@theFirstChildGUI int ',@tableName,@theFirstChildCUI
@tableName nvarchar(100),
@theFirstChildCUI int
as
declare @str as nvarchar(4000)
--declare @theFirstChildCUI int
set @str = N'select * from ' + @tableName + ' where id = @theFirstChildCUI'
exec sp_executesql @str,N'@theFirstChildCUI int',@theFirstChildCUI
@tableName nvarchar(100)
,@theFirstChildCUI int
,@hasChild bit out
as
declare @str as nvarchar(4000)
set @hasChild = 0
set @str = N'select @hasChild = 1 from ' + @tableName + ' where CUI = @theFirstChildCUI'
exec sp_executesql @str,N'@theFirstChildCUI int,@hasChild bit out',@theFirstChildCUI,@hasChild outgodeclare @ bit
exec ebsp_HasChild N'TableName',103,@ out
select @