动态sql问题

[EnnoMiao]

CREATE PROC procGetEntity(
  @topicid NVARCHAR(100),
  @filter NVARCHAR(400)
 )
 AS
	DECLARE @ROWS INT
  DECLARE @sqlGetRowNumber NVARCHAR(1000)  
  SET @sqlGetRowNumber=N' select @rows=rownumber from  (select rownumber=row_number()over(order by createtime),* from topic) k  where TopicID='''+@topicid+'''and' +@filter  

--接下来用@rows做其他操作
...

如果我把 DECLARE @ROWS INT 放到

 SET @sqlGetRowNumber=N'DECLARE @ROWS INT  select @rows=rownumb...

外面就取不到rows

请问这个怎么做，才能取到rows 我是mssql2008

[shinger126]

CREATE PROC procGetEntity( @topicid NVARCHAR(100), @filter NVARCHAR(400) ) AS create table #t (rows int) DECLARE @ROWS INT DECLARE @sqlGetRowNumber NVARCHAR(1000) SET @sqlGetRowNumber=N' insert into #t select rownumber from (select rownumber=row_number()over(order by createtime),* from topic) k where TopicID='''+@topicid+'''and' +@filter exec executesql @sqlGetRowNumber select @rows=rows from #t

[spiritofdragon]

用EXECUTE sp_executesql ，里面带个输出参数，见下面SQL，F1的帮助示例

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);
SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
   FROM AdventureWorks2008R2.HumanResources.Employee
   WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

[shinger126]

应该还有更简单的方法的，我只会这种

[习惯性蹭分]

alter PROC procGetEntity(
  @topicid NVARCHAR(100),
  @filter NVARCHAR(400),
  @ROWS int output
 )
 AS
	--DECLARE @ROWS INT
  DECLARE @sqlGetRowNumber NVARCHAR(1000)  
  SET @sqlGetRowNumber=N' select @rows=rownumber from  (select rownumber=row_number()over(order by createtime),* 
  from topic) k  where TopicID='''+@topicid+'''and ' +@filter  
  exec sp_executesql @sqlGetRowNumber,N'@rows int output',@rows output
  
  go
  declare @rows int
  exec procGetEntity  5,' 1=1 ',@rows output

[唐诗三百首]

-- 建测试表
create table topic(id int)

-- 产生40条记录
insert into topic(id)
 select number 
 from master.dbo.spt_values
 where type='P' and number between 1 and 40


declare @ROWS int
declare @sqlGetRowNumber nvarchar(1000)  

set @sqlGetRowNumber=N'select @ROWS=count(1) from topic'

exec sp_executesql @sqlGetRowNumber,N'@ROWS int output',@ROWS=@ROWS output

select @ROWS 'ROWS_Value'

/*
ROWS_Value
-----------
40

(1 row(s) affected)
*/