如何在动态选择查询中使用表变量? [英] How to use a table variable inside a dynamic select query?
本文介绍了如何在动态选择查询中使用表变量?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了一个存储过程
我已经宣布了一张桌子
创建PROCEDURE [dbo]。[sp_search_report]
(
@start_index int,
@pagesize int,
@sort_by varchar(50),
@out_status int out,
@totalcount int out,
@query varchar(2000)
)
AS
SET NOCOUNT ON
声明@ query1 varchar(2000);
声明@finalquery nvarchar(2000);
--declare @lastquery nvarchar(2000);
声明@ q1 int;
声明@ q2 int;
SET @ q1 =((@ start_index -1)* @PageSize + 1)
SET @ q2 =((((@start_index -1)* @PageSize + 1)+ @PageSize) - 1 )
声明@records表
(
rowNumber int not not null identity(1,1)主键,
empid varchar(100),
余额int
)
/ *在@records表中插入一些并从@records中选择*也是在取得结果。问题在于下面的查询:* /
SET @ query1 ='select ROW_NUMBER()OVER(ORDER BY a.empname desc) AS rowNumber,
a.empid,a.empname,t.balance
来自@records t左外连接tbl_emp a on a.empid = t.empid
其中'
SET @finalquery ='SELECT * FROM('+ @ query1 + @query +')as t1 WHERE RowNumber BETWEEN'+ cast(@ q1 as char)+'AND'+ cast(@ q2 as CHAR)
exec(@finalquery)
我尝试过:
错误是
必须声明表变量@records。
我该如何解决?帮帮我
解决方案
之前已经回答了这个问题。
看看
在dymanic Sql语句中使用临时表 [ ^ ]
I have created a stored procedure
I have declared a table
Create PROCEDURE [dbo].[sp_search_report] ( @start_index int , @pagesize int , @sort_by varchar(50), @out_status int out, @totalcount int out, @query varchar(2000) ) AS
SET NOCOUNT ON declare @query1 varchar(2000); declare @finalquery nvarchar(2000); --declare @lastquery nvarchar(2000); declare @q1 int; declare @q2 int; SET @q1 = ((@start_index -1) * @PageSize + 1) SET @q2 = ((((@start_index -1) * @PageSize + 1) + @PageSize) - 1)
declare @records table ( rowNumber int not null identity(1,1) primary key, empid varchar(100), balance int )
/* Did some insert here in @records table and select * from @records is fetching results as well. The problem is with the query below: */
SET @query1='select ROW_NUMBER() OVER (ORDER BY a.empname desc) AS rowNumber, a.empid , a.empname, t.balance from @records t left outer join tbl_emp a on a.empid =t.empid where '
SET @finalquery = 'SELECT * FROM ( '+ @query1 + @query + ' ) as t1 WHERE RowNumber BETWEEN ' + cast(@q1 as char)+' AND '+ cast (@q2 as CHAR) exec (@finalquery)
What I have tried:
The error is
Must declare the table variable "@records".
How can I fix it? Help me out
解决方案
This one has already been answered before.
Have a look at
Use of temp table inside dymanic Sql statement[^]
这篇关于如何在动态选择查询中使用表变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文