如何在MS SQL中的子查询中获取输入参数值 [英] How to get input parameter value in sub query in MS SQL

问题描述

你好，



我无法在子查询中获取输入参数值。下面是我的商店程序中的代码块

 SELECT * FROM（SELECT ROW_NUMBER（）OVER（ORDER BY Name asc）为MyRowNumber，* 
 
 FROM 
 
 tblContactList WHERE friendOfAutoId = @ UserAutoId）tblContactList 
 
，其中
 
 MyRowNumber BETWEEN（（（@pageNo  -  1）* @pageSize）+ 1） AND @ pageNo * @ pageSize 

我的尝试：



我试图从数据库获取有限的记录

解决方案

你需要的是命名子查询：

  SELECT  *  FROM （ SELECT  ROW_NUMBER（） OVER （ ORDER   BY 名称 asc ） as  MyRowNumber，* 
 ˚F ROM  tblContactList 
 <跨度类= 代码关键字> WHERE  friendOfAutoId = @ UserAutoId）<跨度类= 代码关键字> AS  MyDerivedTable 
 <跨度类= 代码关键字> WHERE  MyDerivedTable.MyRowNumber <跨度类= 代码关键字> BETWEEN （（（<跨度类= 代码sdkkeyword> @您做生意  -   1 ）*  @ pageSize ）+  1 ） AND  @ pageNo * @ pageSize 

此查询没有错误。



根据查询，您要根据页码和页面大小检索记录。



因此，请检查您传递的输入参数值是否包含该表中的数据（ tblContactList ）。

Hello there's,

I cant able to get input parameter value in sub Query. below is code block in my store procedure

SELECT  *  FROM (SELECT  ROW_NUMBER() OVER (ORDER BY Name asc) as MyRowNumber, *
		
		FROM
		
		tblContactList WHERE friendOfAutoId=@UserAutoId) tblContactList
		
		WHERE
		
		MyRowNumber BETWEEN ( ((@pageNo - 1) * @pageSize )+ 1) AND @pageNo*@pageSize 	

What I have tried:

I am trying to get limited record from databse

解决方案

What you need is to name the subquery:

SELECT  *  FROM (SELECT  ROW_NUMBER() OVER (ORDER BY Name asc) as MyRowNumber, *
                 FROM tblContactList 
                 WHERE friendOfAutoId=@UserAutoId) AS MyDerivedTable
           WHERE MyDerivedTable.MyRowNumber BETWEEN ( ((@pageNo - 1) * @pageSize )+ 1) AND @pageNo*@pageSize

There is no wrong in this query.

As per the query you want to retrieve the records as per the page number and page size.

So please check whether the input parameter value which you are passing is having the data in that table(tblContactList) or not.

这篇关于如何在MS SQL中的子查询中获取输入参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！