create SQL-view with parameter

Question

declare @Date DateTime
set @Date='2012-04-16'
select s.sid,'Status'=case a.sid when isnull(a.sid,null)   
then 'absent' else 'present' end from
student s left outer join (select * from absent where date=@Date) as a
on s.sid=a.sid

I have a sql query like this, and I need to create a view with this one.....is it possible..

I created a function for this like

CREATE FUNCTION dbo.Attendance (@Date DateTime)
RETURNS TABLE
AS
RETURN
(
    select s.sid,'Status'=case a.sid when isnull(a.sid,null)   
    then 'absent' else 'present' end from
    student s left outer join (select * from absent where date=@Date) as a
    on s.sid=a.sid

)

the view is created successfully....but when I call the view like

 select * from dbo.Attendance('2012-04-11')

it reports error like "Conversion failed when converting date and/or time from character string."......how can I call this function

not really - views don't have parameters... you could only do the join in the view and use the parameter in the where when you query the view... — Yahia
– Yahia, Commented Apr 16, 2012 at 4:43
You want to assign parameter value at runtime, create Stored Procedure either — Naved
– Naved, Commented Apr 16, 2012 at 4:45
you will need to cast the string to a date,cast(@Date as date) you should also explicitly check the date makes sense and raise an error if it doesn't. — Paddy Carroll
– Paddy Carroll, Commented Apr 16, 2012 at 5:48
BEGIN TRY cast(@Date as date) END TRY BEGIN CATCH RAISERROR(' The supplied date was not recognised as valid!',16,1) END CATCH; N.B. look up raiserror for all options which will depend on your implementation — Paddy Carroll
– Paddy Carroll, Commented Apr 16, 2012 at 5:54
after the AS, declare a new variable of type date, call it \@dDate then do the try from the line above followed by the \@dDate = cast(\@Date as date) & use \@dDate in your query — Paddy Carroll
– Paddy Carroll, Commented Apr 16, 2012 at 5:59

Paddy Carroll · Accepted Answer · 2012-04-16 04:50:54Z

2

paramerterised views are allowed in MS Access but no serious db server will allow you to construct a view that requires a mandatory parameter.

your example describes a stored procedure which should be the implementation of choice for your requirement.

another way of meeting your requirement would be to keep your @Date parameter in another table, provided it's there the view can refer to that and will return a row if the criteria is met

answered Apr 16, 2012 at 4:50

Paddy Carroll

5383 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shanish Over a year ago

thanks Paddy Carroll...I dont understand how to keep the @Date in another table, can u give me the query sample

Paddy Carroll Over a year ago

create table application_parameters( param_name varchar,param_value varchar); insert into application_parameters (param_name,param_value) values ('my_app_date','2012-04-16');

Paddy Carroll Over a year ago

select s.sid,'Status'=case a.sid when isnull(a.sid,null) then 'absent' else 'present' end from student s left outer join (select * from absent ,application_paramaters where param_name = 'my_app_date' and date = cast(my_param_value as Date)) as a on s.sid=a.sid ----not checked but you get the idea

Community · Accepted Answer · 2017-05-23 11:56:28Z

2

You cannot create a parametrized view atleast in SQL server rather use a table function.

Please refer Create parameterized VIEW in SQL Server 2008 for more details.

Hope it will give you an idea.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Apr 16, 2012 at 4:55

Shailesh

1,21812 silver badges12 bronze badges

1 Comment

Shailesh Over a year ago

Try taking '2012-04-11' into a datetime variable then use select * from dbo.Attendance(@myDate). I hope after this modification it should work

Harikrishna · Accepted Answer · 2017-08-04 11:21:20Z

1

View with Parameter is possible as per following info. Please visit following link:

https://msdn.microsoft.com/library/c11kd98s(v=vs.80).aspx

CREATE SQL VIEW Customer_Remote_View ;
   AS SELECT * FROM Customers WHERE Customers.Country = ?cCountry

edited Aug 4, 2017 at 11:21

answered Aug 4, 2017 at 11:07

Harikrishna

112 bronze badges

Collectives™ on Stack Overflow

create SQL-view with parameter

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related