I have a SQL Query that accepts parameters. Now When I'm trying to include that query into a view, I'm facing an error because a view cannot hold a parameters just like an SP or a function can.
Hence if I had to create the view that had to contain the parameters, Is there someway that it is possible?
Many Thanks
I don't think so you can create a parameter in a View .But you can create a function which takes input parameter like the one below .
CREATE FUNCTION dbo.Sample (@Parameter varchar(10))
RETURNS TABLE
AS
RETURN
(
SELECT Field1, Field2,....
FROM YourTable
WHERE Field3 = @Parameter
)
No, from MSDN
Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:
To focus, simplify, and customize the perception each user has of the database.
As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
To provide a backward compatible interface to emulate a table whose schema has changed.
So, basically, this acts just like a table, and the only way that you can add "parameters" to a table is via filter statements when accessing the view
Definitely not. Consider View as a table which contains a compiled set of data(although in actual terms view is different from table) and hence it can never have any input parameter like an sp or a function.
View can not pass paramets directly, but you can use context_info, for example
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
You guys have never done this as a VIEW is a whole other table created by a query and you can include parameters in that query....it is freaking simple as well
build your view like you would any other with parameters....
Example:
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
now call that view like this:
select from get_batch_data('61404') <<<< 61404 is the parameter being passed....
Pretty simple and very powerful as you can use a view to provide UNALTERABLE information to a subset of a table or a union of tables.
A view is NOT the table and so there are things you sometimes cannot do.
But these are the same things you can not do when you form a very complicated join/union in a query......been using the one above since 1999 so I know it works....
