blocks|key|1414364|text|不是的。如果必须使用用户定义的函数，则可以将参数传递到该函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414365|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

no.
if you must then use a user defined function to which you can pass parameters into.

blocks|key|1414438|text|不，你不能，就像Mladen+Prajdic说的那样。将视图看作是表或表的组合上的“静态过滤器”。例如:视图可以组合表Order和Customer，因此您可以从Order获得一个新的“表”，其中包含行以及包含客户名称和客户编号的新列(表的组合)。或者，您可以创建一个仅从Order表中选择未处理订单的视图(静态筛选器)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1414439|然后，您可以像从任何其他“普通”表中选择一样从视图中进行选择-所有“非静态”过滤都必须在视图之外进行(比如“获取名为Miller的客户的所有订单”或“获取12月24日收到的未处理订单”)。|1414440|entityMap^0|1N|5|1T|8|28|5|3R|5|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

No you can't, as Mladen Prajdic said. Think of a view as a "static filter" on a table or a combination of tables. For example: a view may combine tables <code>Order</code> and <code>Customer</code> so you get a new "table" of rows from <code>Order</code> along with new columns containing the customer's name and the customer number (combination of tables). Or you might create a view that selects only unprocessed orders from the <code>Order</code> table (static filter).

You'd then select from the view like you would select from any other "normal" table - all "non-static" filtering must be done outside the view (like "Get all the orders for customers called Miller" or "Get unprocessed orders that came in on Dec 24th").

blocks|key|1199358|text|不是，视图的查询方式与表的SELECTing没有区别。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1199359|要执行所需操作，请使用带有一个或多个参数的table-valued+user-defined+function|offset|length|1199360|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms191165.aspx^0|0|L|Y|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

No, a view is queried no differently to SELECTing from a table.

To do what you want, use a <a href="http://msdn.microsoft.com/en-us/library/ms191165.aspx" rel="noreferrer">table-valued user-defined function</a> with one or more parameters

blocks|key|1414513|text|有两种方法可以实现你想要的。不幸的是，这两种方法都不能使用视图来完成。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414514|您可以创建一个表值用户定义函数，该函数接受所需的参数并返回查询结果|1414515|或者，您可以做几乎相同的事情，但创建一个存储过程，而不是用户定义的函数。|1414516|例如：|1414517|存储过程将如下所示|1414518|CREATE+PROCEDURE+s_emp
(
++++@enoNumber+INT
)+
AS+
SELECT
++++*+
FROM
++++emp+
WHERE+
++++emp_id=@enoNumber|code-block|syntax|javascript|1414519|否则，用户定义的函数将如下所示|1414520|CREATE+FUNCTION+u_emp
(+++
++++@enoNumber+INT
)
RETURNS+TABLE+
AS
RETURN+
(
++++SELECT++++
++++++++*+
++++FROM++++
++++++++emp+
++++WHERE+++++
++++++++emp_id=@enoNumber
)|1414521|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

There are two ways to achieve what you want. Unfortunately, neither can be done using a view.
You can either create a table valued user defined function that takes the parameter you want and returns a query result
Or you can do pretty much the same thing but create a stored procedure instead of a user defined function.
For example:
the stored procedure would look like
<pre><code>CREATE PROCEDURE s_emp
(
 @enoNumber INT
) 
AS 
SELECT
 * 
FROM
 emp 
WHERE 
 emp_id=@enoNumber
</code></pre>
Or the user defined function would look like
<pre><code>CREATE FUNCTION u_emp
( 
 @enoNumber INT
)
RETURNS TABLE 
AS
RETURN 
(
 SELECT 
 * 
 FROM 
 emp 
 WHERE 
 emp_id=@enoNumber
)
</code></pre>

blocks|key|1414600|text|不可以，您可以将参数传递给视图中的过程|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414601|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

no 
you can pass the parameter to the procedure in view

blocks|key|1199538|text|如前所述，您不能这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1199539|一种可能的解决方案是实现一个存储函数，例如：|1199540|CREATE+FUNCTION+v_emp+(@pintEno+INT)
RETURNS+TABLE
AS
RETURN
+++SELECT+*+FROM+emp+WHERE+emp_id=@pintEno;|code-block|syntax|javascript|1199541|这使您可以将其用作普通视图，包括：|1199542|SELECT+*+FROM+v_emp(10)|1199543|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

As already stated you can't. 

A possible solution would be to implement a stored function, like: 

<pre><code>CREATE FUNCTION v_emp (@pintEno INT)
RETURNS TABLE
AS
RETURN
 SELECT * FROM emp WHERE emp_id=@pintEno;
</code></pre>

This allows you to use it as a normal view, with:

<pre><code>SELECT * FROM v_emp(10)
</code></pre>

blocks|key|1414750|text|视图只不过是一个预定义的'SELECT‘语句。因此，唯一真正的答案是:不，你不能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414751|我认为您真正想要做的是创建一个存储过程，原则上您可以使用任何有效的SQL来做任何您想做的事情，包括接受参数和选择数据。|1414752|但是，您似乎真的只需要在从视图中进行选择时添加where子句，但是您并没有提供足够的细节来确保这一点。|1414753|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

A view is nothing more than a predifined 'SELECT' statement. So the only real answer would be: No, you cannot.

I think what you really want to do is create a stored procedure, where in principle you can use any valid SQL to do whatever you want, including accept parameters and select data.

It seems likely that you really only need to add a where clause when you select from your view though, but you didn't really provide enough details to be sure.

blocks|key|1414872|text|不，视图是静态的。您可以做的一件事(取决于SQl服务器的版本)是为视图建立索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414873|在您的示例(仅查询一个表)中，索引视图对于简单地查询具有索引的表没有任何好处，但是如果您在具有联接条件的表上执行大量联接，则索引视图可以极大地提高性能。|1414874|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

No, a view is static. One thing you can do (depending on the version of SQl server) is index a view.

In your example (querying only one table), an indexed view has no benefit to simply querying the table with an index on it, but if you are doing a lot of joins on tables with join conditions, an indexed view can greatly improve performance.

blocks|key|1199806|text|我们可以编写一个带有输入参数的存储过程，然后使用该存储过程从视图中获取结果集。请参见下面的示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1199807|存储过程是|1199808|CREATE+PROCEDURE+[dbo].[sp_Report_LoginSuccess]+--+[sp_Report_LoginSuccess]+'01/01/2010','01/30/2010'
@fromDate+datetime,
@toDate+datetime,
@RoleName+varchar(50),
@Success+int
as
If+@RoleName+!=+'All'
Begin
+++If+@Success!=2
+++Begin
+++--fetch+based+on+true+or+false
++Select+*+from+vw_Report_LoginSuccess
++where+logindatetime+between++dbo.DateFloor(@fromDate)+and+dbo.DateSieling(@toDate)
++And+RTrim(Upper(RoleName))+=+RTrim(Upper(@RoleName))+and+Success=@Success
+++End
+++Else
+++Begin
++++--+fetch+all
++Select+*+from+vw_Report_LoginSuccess
++where+logindatetime+between++dbo.DateFloor(@fromDate)+and+dbo.DateSieling(@toDate)
++And+RTrim(Upper(RoleName))+=+RTrim(Upper(@RoleName))
+++End

End
Else
Begin
+++If+@Success!=2
+++Begin
++Select+*+from+vw_Report_LoginSuccess
++where+logindatetime+between++dbo.DateFloor(@fromDate)+and+dbo.DateSieling(@toDate)
++and+Success=@Success
+End
+Else
+Begin
++Select+*+from+vw_Report_LoginSuccess
++where+logindatetime+between++dbo.DateFloor(@fromDate)+and+dbo.DateSieling(@toDate)
+End

End|code-block|syntax|javascript|1199809|我们可以从中获得结果集的视图是|1199810|CREATE+VIEW+[dbo].[vw_Report_LoginSuccess]
AS
SELECT+++++'3'+AS+UserDetailID,+dbo.tblLoginStatusDetail.Success,+CONVERT(varchar,+dbo.tblLoginStatusDetail.LoginDateTime,+101)+AS+LoginDateTime,
++++++++++++++++++++++CONVERT(varchar,+dbo.tblLoginStatusDetail.LogoutDateTime,+101)+AS+LogoutDateTime,+dbo.tblLoginStatusDetail.TokenID,
++++++++++++++++++++++dbo.tblUserDetail.SubscriberID,+dbo.aspnet_Roles.RoleId,+dbo.aspnet_Roles.RoleName
FROM+++++++++dbo.tblLoginStatusDetail+INNER+JOIN
++++++++++++++++++++++dbo.tblUserDetail+ON+dbo.tblLoginStatusDetail.UserDetailID+=+dbo.tblUserDetail.UserDetailID+INNER+JOIN
++++++++++++++++++++++dbo.aspnet_UsersInRoles+ON+dbo.tblUserDetail.UserID+=+dbo.aspnet_UsersInRoles.UserId+INNER+JOIN
++++++++++++++++++++++dbo.aspnet_Roles+ON+dbo.aspnet_UsersInRoles.RoleId+=+dbo.aspnet_Roles.RoleId
WHERE+++++(dbo.tblLoginStatusDetail.Success+=+0)
UNION+all
SELECT+++++dbo.tblLoginStatusDetail.UserDetailID,+dbo.tblLoginStatusDetail.Success,+CONVERT(varchar,+dbo.tblLoginStatusDetail.LoginDateTime,+101)
++++++++++++++++++++++AS+LoginDateTime,+CONVERT(varchar,+dbo.tblLoginStatusDetail.LogoutDateTime,+101)+AS+LogoutDateTime,+dbo.tblLoginStatusDetail.TokenID,
++++++++++++++++++++++dbo.tblUserDetail.SubscriberID,+dbo.aspnet_Roles.RoleId,+dbo.aspnet_Roles.RoleName
FROM+++++++++dbo.tblLoginStatusDetail+INNER+JOIN
++++++++++++++++++++++dbo.tblUserDetail+ON+dbo.tblLoginStatusDetail.UserDetailID+=+dbo.tblUserDetail.UserDetailID+INNER+JOIN
++++++++++++++++++++++dbo.aspnet_UsersInRoles+ON+dbo.tblUserDetail.UserID+=+dbo.aspnet_UsersInRoles.UserId+INNER+JOIN
++++++++++++++++++++++dbo.aspnet_Roles+ON+dbo.aspnet_UsersInRoles.RoleId+=+dbo.aspnet_Roles.RoleId
WHERE+++++(dbo.tblLoginStatusDetail.Success+=+1)+AND+(dbo.tblUserDetail.SubscriberID+LIKE+N'P%25')++|1199811|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

we can write a stored procedure with input parameters and then use that stored procedure to get a result set from the view.
see example below.

the stored procedure is

<pre><code>CREATE PROCEDURE [dbo].[sp_Report_LoginSuccess] -- [sp_Report_LoginSuccess] '01/01/2010','01/30/2010'
@fromDate datetime,
@toDate datetime,
@RoleName varchar(50),
@Success int
as
If @RoleName != 'All'
Begin
 If @Success!=2
 Begin
 --fetch based on true or false
 Select * from vw_Report_LoginSuccess
 where logindatetime between dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
 And RTrim(Upper(RoleName)) = RTrim(Upper(@RoleName)) and Success=@Success
 End
 Else
 Begin
 -- fetch all
 Select * from vw_Report_LoginSuccess
 where logindatetime between dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
 And RTrim(Upper(RoleName)) = RTrim(Upper(@RoleName))
 End

End
Else
Begin
 If @Success!=2
 Begin
 Select * from vw_Report_LoginSuccess
 where logindatetime between dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
 and Success=@Success
 End
 Else
 Begin
 Select * from vw_Report_LoginSuccess
 where logindatetime between dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
 End

End
</code></pre>

and the view from which we can get the result set is

<pre><code>CREATE VIEW [dbo].[vw_Report_LoginSuccess]
AS
SELECT '3' AS UserDetailID, dbo.tblLoginStatusDetail.Success, CONVERT(varchar, dbo.tblLoginStatusDetail.LoginDateTime, 101) AS LoginDateTime,
 CONVERT(varchar, dbo.tblLoginStatusDetail.LogoutDateTime, 101) AS LogoutDateTime, dbo.tblLoginStatusDetail.TokenID,
 dbo.tblUserDetail.SubscriberID, dbo.aspnet_Roles.RoleId, dbo.aspnet_Roles.RoleName
FROM dbo.tblLoginStatusDetail INNER JOIN
 dbo.tblUserDetail ON dbo.tblLoginStatusDetail.UserDetailID = dbo.tblUserDetail.UserDetailID INNER JOIN
 dbo.aspnet_UsersInRoles ON dbo.tblUserDetail.UserID = dbo.aspnet_UsersInRoles.UserId INNER JOIN
 dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE (dbo.tblLoginStatusDetail.Success = 0)
UNION all
SELECT dbo.tblLoginStatusDetail.UserDetailID, dbo.tblLoginStatusDetail.Success, CONVERT(varchar, dbo.tblLoginStatusDetail.LoginDateTime, 101)
 AS LoginDateTime, CONVERT(varchar, dbo.tblLoginStatusDetail.LogoutDateTime, 101) AS LogoutDateTime, dbo.tblLoginStatusDetail.TokenID,
 dbo.tblUserDetail.SubscriberID, dbo.aspnet_Roles.RoleId, dbo.aspnet_Roles.RoleName
FROM dbo.tblLoginStatusDetail INNER JOIN
 dbo.tblUserDetail ON dbo.tblLoginStatusDetail.UserDetailID = dbo.tblUserDetail.UserDetailID INNER JOIN
 dbo.aspnet_UsersInRoles ON dbo.tblUserDetail.UserID = dbo.aspnet_UsersInRoles.UserId INNER JOIN
 dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE (dbo.tblLoginStatusDetail.Success = 1) AND (dbo.tblUserDetail.SubscriberID LIKE N'P%') 
</code></pre>

blocks|key|1199867|text|在没有存储过程或函数的情况下执行此操作的一种老套方法是在数据库中创建一个具有列Id、Param1、Param2等的设置表。在该表中插入一行，其中包含值Id=1、Param1=0、Param2=0等。然后，您可以在视图中向该表添加连接，以创建所需的效果，并在运行视图之前更新设置表。如果您有多个用户同时更新设置表并运行视图，则可能会出错，但在其他情况下应该可以正常工作。类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1199868|CREATE+VIEW+v_emp+
AS+
SELECT++++++*+
FROM++++++++emp+E
INNER+JOIN++settings+S
ON++++++++++S.Id+=+1+AND+E.emp_id+=+S.Param1|code-block|syntax|javascript|1199869|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A hacky way to do it without stored procedures or functions would be to create a settings table in your database, with columns Id, Param1, Param2, etc. Insert a row into that table containing the values Id=1,Param1=0,Param2=0, etc. Then you can add a join to that table in your view to create the desired effect, and update the settings table before running the view. If you have multiple users updating the settings table and running the view concurrently things could go wrong, but otherwise it should work OK. Something like:

<pre><code>CREATE VIEW v_emp 
AS 
SELECT * 
FROM emp E
INNER JOIN settings S
ON S.Id = 1 AND E.emp_id = S.Param1
</code></pre>

blocks|key|1415026|text|据我所知，视图可以类似于select命令。您还可以向此select添加参数，例如，在where语句中，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415027|+WHERE++(exam_id+=+@var)|code-block|syntax|javascript|1415028|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

As I know view can be something just like select command.
You also can add parameters to this select for example in where statements like this:

<pre><code> WHERE (exam_id = @var)
</code></pre>

blocks|key|1199934|text|为什么在视图中需要一个参数？你可以直接使用WHERE子句。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1199935|create+view+v_emp+as+select+*+from+emp+;|code-block|syntax|javascript|1199936|您的查询应该可以完成这项工作：|1199937|select+*+from+v_emp+where+emp_id=&eno;|1199938|entityMap^0|L|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

Why do you need a parameter in view? You might just use <code>WHERE</code> clause.

<pre><code>create view v_emp as select * from emp ;
</code></pre>

and your query should do the job:

<pre><code>select * from v_emp where emp_id=&amp;eno;
</code></pre>

blocks|key|1199962|text|如果您不想使用某个函数，可以使用如下所示的方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1199963|--+VIEW
CREATE+VIEW+[dbo].[vwPharmacyProducts]
AS
SELECT+++++PharmacyId,+ProductId
FROM+++++++++dbo.Stock
WHERE+++++(TotalQty+>+0)

--+Use+of+view+inside+a+stored+procedure
CREATE+PROCEDURE+[dbo].[usp_GetProductByFilter]
(+++@pPharmacyId+int+)+AS

IF+@pPharmacyId+=+0+BEGIN+SET+@pPharmacyId+=+NULL+END

SELECT++P.[ProductId],+P.[strDisplayAs]+FROM+[Product]+P
WHERE+(P.[bDeleted]+=+0)
++++AND+(P.[ProductId]+IN+(Select+vPP.ProductId+From+vwPharmacyProducts+vPP
+++++++++++++++++++++++++++Where+vPP.PharmacyId+=+@pPharmacyId)
+++++++++++++++++++++++OR+@pPharmacyId+IS+NULL
++++++++)|code-block|syntax|javascript|1199964|希望能有所帮助|1199965|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you don't want to use a function, you can use something like this

<pre><code>-- VIEW
CREATE VIEW [dbo].[vwPharmacyProducts]
AS
SELECT PharmacyId, ProductId
FROM dbo.Stock
WHERE (TotalQty &gt; 0)

-- Use of view inside a stored procedure
CREATE PROCEDURE [dbo].[usp_GetProductByFilter]
( @pPharmacyId int ) AS

IF @pPharmacyId = 0 BEGIN SET @pPharmacyId = NULL END

SELECT P.[ProductId], P.[strDisplayAs] FROM [Product] P
WHERE (P.[bDeleted] = 0)
 AND (P.[ProductId] IN (Select vPP.ProductId From vwPharmacyProducts vPP
 Where vPP.PharmacyId = @pPharmacyId)
 OR @pPharmacyId IS NULL
 )
</code></pre>

Hope it will help

blocks|key|1200011|text|您可以绕过运行视图，SQL会因此而哭笑不得，但这样做并运行它就可以了！你不能保存。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1200012|create+or+replace+view+v_emp(eno+number)+as+select+*+from+emp+where+(emp_id+=+@Parameter1);|code-block|syntax|javascript|1200013|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can bypass just to run the view, SQL will wine and cry but just do this and run it! You can't save.

<pre><code>create or replace view v_emp(eno number) as select * from emp where (emp_id = @Parameter1);
</code></pre>

blocks|key|1415155|text|这是一个我到目前为止还没有看到的选择：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415156|只需将您想要限制的列添加到视图：|1415157|create+view+emp_v+as+(
select+emp_name,+emp_id+from+emp;
)

select+emp_v.emp_name+from+emp_v
where+emp_v.emp_id+=+(id+to+restrict+by)|code-block|syntax|javascript|1415158|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is an option I have not seen so far:

Just add the column you want to restrict on to the view:

<pre><code>create view emp_v as (
select emp_name, emp_id from emp;
)

select emp_v.emp_name from emp_v
where emp_v.emp_id = (id to restrict by)
</code></pre>

blocks|key|1200090|text|您的视图可以引用一些包含参数的外部表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1200091|正如其他人所提到的，SQL+Server中的视图不能有外部输入参数。但是，您可以很容易地使用CTE在视图中伪造变量。您可以在您的SQL+Server版本中测试运行它。|1200092|CREATE+VIEW+vwImportant_Users+AS
WITH+params+AS+(
++++SELECT+
++++varType='%25Admin%25',+
++++varMinStatus=1)
SELECT+status,+name+
++++FROM+sys.sysusers,+params
++++WHERE+status+>+varMinStatus+OR+name+LIKE+varType

SELECT+*+FROM+vwImportant_Users|code-block|syntax|javascript|1200093|产生的输出：|1200094|status++name
12++++++dbo
0+++++++db_accessadmin
0+++++++db_securityadmin
0+++++++db_ddladmin|1200095|也是通过JOIN|offset|length|style|CODE|1200096|WITH+params+AS+(+SELECT+varType='%25Admin%25',+varMinStatus=1)
SELECT+status,+name+
++++FROM+sys.sysusers+INNER+JOIN+params+ON+1=1
++++WHERE+status+>+varMinStatus+OR+name+LIKE+varType|1200097|也是通过CROSS+APPLY|1200098|WITH+params+AS+(+SELECT+varType='%25Admin%25',+varMinStatus=1)
SELECT+status,+name+
++++FROM+sys.sysusers+CROSS+APPLY+params
++++WHERE+status+>+varMinStatus+OR+name+LIKE+varType|1200099|entityMap^0|0|0|0|0|0|4|4|0|0|4|B|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|15|8|@$O|16|P|17|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|19|8|@$O|1A|P|1B|Q|R]]|9|@]|A|$]]|$1|W|3|X|5|F|7|1C|8|@]|9|@]|A|$G|H]]|$1|Y|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Z|$]]

Your view can reference some external table containing your parameters. 

As others mentioned, the view in SQL Server cannot have external input parameters. However, you can easily fake a variable in your view using CTE. You can test-run it in your version of SQL Server.

<pre><code>CREATE VIEW vwImportant_Users AS
WITH params AS (
 SELECT 
 varType='%Admin%', 
 varMinStatus=1)
SELECT status, name 
 FROM sys.sysusers, params
 WHERE status &gt; varMinStatus OR name LIKE varType

SELECT * FROM vwImportant_Users
</code></pre>

yielding output:

<pre><code>status name
12 dbo
0 db_accessadmin
0 db_securityadmin
0 db_ddladmin
</code></pre>

also via <code>JOIN</code>

<pre><code>WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
 FROM sys.sysusers INNER JOIN params ON 1=1
 WHERE status &gt; varMinStatus OR name LIKE varType
</code></pre>

also via <code>CROSS APPLY</code>

<pre><code>WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
 FROM sys.sysusers CROSS APPLY params
 WHERE status &gt; varMinStatus OR name LIKE varType
</code></pre>

blocks|key|1415227|text|我有个想法，我还没试过。您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415228|CREATE+VIEW+updated_customers+AS
SELECT+*+FROM+customer+as+aa
LEFT+JOIN+customer_rec+as+bb
ON+aa.id+=+bb.customer_id
WHERE+aa.updated_at+between+(SELECT+start_date+FROM+config+WHERE+active+=+1)+
and+(SELECT+end_date+FROM+config+WHERE+active+=+1)|code-block|syntax|javascript|1415229|您的参数将在Config表中保存和更改。|1415230|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I have an idea that I haven't tried yet. You can do:

<pre><code>CREATE VIEW updated_customers AS
SELECT * FROM customer as aa
LEFT JOIN customer_rec as bb
ON aa.id = bb.customer_id
WHERE aa.updated_at between (SELECT start_date FROM config WHERE active = 1) 
and (SELECT end_date FROM config WHERE active = 1)
</code></pre>

Your parameters will be saved and changed in the Config table.

blocks|key|1200168|text|我根据自己的需求实现了这项任务，如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1200169|set+nocount+on;

++declare+@ToDate+date+=+dateadd(month,datediff(month,0,getdate())-1,0)

declare+@year+varchar(4)++=+year(@ToDate)
declare+@month+varchar(2)+=+month(@ToDate)

declare+@sql+nvarchar(max)
set+@sql+=+N'
++++create+or+alter+view+dbo.wTempLogs
++++as
++++select+*+from+dbo.y2019
++++where
++++++++year(LogDate)+=+''_year_''
++++++++and+
++++++++month(LogDate)+=+''_month_''++++'

select+@sql+=+replace(replace(@sql,'_year_',@year),'_month_',@month)

execute+sp_executesql+@sql

declare+@errmsg+nvarchar(max)
++++set+@errMsg+=+@sql
++++raiserror+(@errMsg,+0,1)+with+nowait|code-block|syntax|javascript|1200170|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I realized this task for my needs as follows

<pre><code>set nocount on;

 declare @ToDate date = dateadd(month,datediff(month,0,getdate())-1,0)

declare @year varchar(4) = year(@ToDate)
declare @month varchar(2) = month(@ToDate)

declare @sql nvarchar(max)
set @sql = N'
 create or alter view dbo.wTempLogs
 as
 select * from dbo.y2019
 where
 year(LogDate) = ''_year_''
 and 
 month(LogDate) = ''_month_'' '

select @sql = replace(replace(@sql,'_year_',@year),'_month_',@month)

execute sp_executesql @sql

declare @errmsg nvarchar(max)
 set @errMsg = @sql
 raiserror (@errMsg, 0,1) with nowait
</code></pre>

blocks|key|1415281|text|虽然问题已经得到了很好的回答，但我只想补充一点。大多数时候，我们认为视图是一个发送数据的查询，但视图不仅仅是这样……视图也可用于更新基础表中的数据。您可以右键单击SSMS中的一个视图，您将找到“编辑前200行”选项。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415282|我相信要启用这种编辑数据的能力，因为对于如何为View编写查询有一定的限制，它需要是一个静态查询。|1415283|因此，与用户定义的函数或存储过程不同，View可以维护实时连接(例如，在Microsoft+Access链接表/视图中)，并将更新写回数据库。|1415284|因此，在您只想获得具有某些动态标准的一组数据的情况下，您应该使用带有所需参数的UDF/SP。|1415285|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

While the question is well answered, I would like to just add a point. Most of the times, we think of Views as a query that sends out the data, but a View is more than that... Views can be used to update the data in underlying tables as well. You can right-click a View in SSMS and you will find the option &quot;Edit Top 200 rows&quot;.
I believe to enable this ability of editing data as, there are certain limitations on how query is written for View, it needs to be a static query.
So unlike a User Defined Function or Stored Procedure, which sends out the queried data and closes, a View can maintain a live connection (e.g. in Microsoft Access linked tables/views) and write the updates back to the database.
Hence, in the cases where you just want to get a set a data with certain dynamic criteria, you should use UDF/SP with the required parameters.

Can we pass a parameter to a view in Microsoft SQL Server?

I tried to <code>create view</code> in the following way, but it doesn't work: 

<pre><code>create or replace view v_emp(eno number) as select * from emp where emp_id=&amp;eno;
</code></pre>

Can we pass parameters to a view in SQL?

是否可以将参数传递给Microsoft SQL Server中的视图？我试着用下面的方法create view，但它不起作用：create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;

问我们可以在SQL中将参数传递给视图吗？
EN

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我们可以在SQL中将参数传递给视图吗？EN