ALTER PROCEDURE abcd
(
@sqlstr nvarchar(4000)
)
AS
begin
declare @p INT
select @sqlstr = 'select '[email protected] ;
EXEC sp_executesql @sqlstr ,N '@p INT OUTPUT ',@p OUTPUT
print @p
end
----------------------------------
@sqlstr 为 'CASE WHEN 45350> 23 THEN 30 ELSE 10 END '
[email protected] = 'select CASE WHEN 50> 23 THEN 30 ELSE 10 END '
[email protected]
但print @p 为空 无结果
???
------解决方案--------------------
select @sqlstr = 'select @p= '[email protected] ;
------解决方案--------------------
CREATE PROCEDURE abcd
(
@sqlstr nvarchar(4000)
)
AS
begin
declare @p INT
select @sqlstr = 'select @p= '[email protected] ;
EXEC sp_executesql @sqlstr ,N '@p INT OUTPUT ',@p OUTPUT
print @p
end
GO
EXEC abcd 'CASE WHEN 45350> 23 THEN 30 ELSE 10 END '
GO
Drop PROCEDURE abcd
--30
------解决方案--------------------
select @sqlstr = 'select '[email protected] ;
改為
select @sqlstr = 'select @p= '[email protected] ;
即可
[email protected],所以就是null
------解决方案--------------------
--如何将exec执行结果放入变量中?
declare @num int, @sql nvarchar(4000)
set @sql= 'select @a=count(*) from tableName '
exec sp_executesql @sql,N '@a int output ',@num output
select @num
------解决方案--------------------
alter PROCEDURE abcd(@sqlstr nvarchar(4000))
AS
begin
declare @p INT
select @sqlstr = 'select @p= '[email protected]
EXEC sp_executesql @sqlstr ,N '@p INT OUTPUT ',@p OUTPUT
print @p
end
GO
EXEC abcd 'CASE WHEN 45350> 23 THEN 30 ELSE 10 END '