Geek asks and answers

Creating an SQL View with Parameters

I have an SQL query that takes parameters. Now, when I try to include this request in the view, I encountered an error because the view cannot contain parameters like SPs or functions.

Therefore, if I needed to create a view that should contain parameters, is it possible that this is possible?

Many thanks

+5
source share
5 answers

I do not think you can create a parameter in a view. But you can create a function that takes an input parameter, such as below.

CREATE FUNCTION dbo.Sample (@Parameter varchar(10))
RETURNS TABLE
AS
RETURN
(
 SELECT Field1, Field2,....
 FROM YourTable
 WHERE Field3 = @Parameter
)
+6
source

No, from MSDN

, ( ) . . , :

, , .

, , .

, .

, , , , "" -

+5

. , ( ), , sp .

0

, , , VIEW - , , ...

, ...

:

USE [iepa]
GO
/****** Object:  StoredProcedure [dbo].[get_Batch_Data]    Script Date: 06/30/2015 11:41:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[get_Batch_Data]
@inparm varchar(12)
AS
select *
from batch_data
where COM_Batch_ID=@inparm

:

select from get_batch_data('61404')  <<<< 61404 is the parameter being passed....

, , UNALTERABLE .
, , .

, , / ... 1999 , , ....

0

, context_info,

declare @v varbinary(8)
set @v = cast(cast(floor( current_timestamp as float)) as bigint) as varbinary) --
set context_info @v -- save 
select * from my_viev

create my_view as
with CTE(date) as (
  select cast(cast(substring(context_info(),1,8) as bigint) as datetime) date  -- read
)
select * from filials p
 left join filials rea on rea.number = p.number 
 and (date between         rea.dateopen and '12.12.9999')
where date between p.datestart and p.datestop
0

More articles:


All Articles