logo标签列表

我们能在SQL中向视图传递参数吗？

sqlsql-serverparametersparameter-passingview
172

我们能否在Microsoft SQL Server中向视图传递参数？

我尝试以以下方式创建视图，但它不起作用：

create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;
一个 view 是一个存储了 select 查询的 SQL 文本。参数不在讨论范围内。当你的存储查询返回你想要过滤的列时，你可以在调用查询中进行操作。例如："SELECT * FROM v_emp WHERE emp_id = ?" - Epicurist
2
@ Epicurist“参数不在讨论范围内”这是一个过于大胆的陈述。反例 - Lukasz Szozda
使用存储过程怎么样？ - farid wahyu
22个回答
0

是的，您可以使用SESSION_CONTEXT()函数或临时表。这里是使用同义词的临时表方法的完整指南：

--1. prepare your data 
drop table if exists #temp_table;
select 1 as id, 'magic' as val into #temp_table; --if you change table definition (add/drop columns f.e.) you will need to recompile the view
drop synonym if exists table_synonym;
create synonym table_synonym for #temp_table;
go
--2. create a view
create or alter view some_view
as
select * from table_synonym --It is a hack and I'm not sure that it will work for your server. I test this on version 15.0.2000.5
go
--3. enjoy 
select * from some_view
go

--you can delete the synonym and the temp table without breaking some_view (but you would not be able to select the data)
drop table if exists #temp_table 
drop synonym if exists table_synonym;
go
select * from some_view 
go
0

我根据自己的需求完成了这个任务，具体如下：

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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接