About Lesson
/*
Limitations of views
1.View does not accept parameters.
2. Rules and Defaults cannot be associated with views.
3. The ORDER BY clause is invalid in views
4. Views cannot be based on temporary tables.
*/
— Error : Cannot pass Parameters to Views
–alternate create a function which returns table
Create function fn_swdata (@dno int) returns table
as
return Select * from tblEmp where DNo = @dno
–Note begin – end block not allowed in tabled function
create view vw_empdata
as
select * from tblemp order by ename
–The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
— create temp table
/*
Syntax:
create table ##temp_table_name
(
colname datatype,
…
)
insert into ##temp_table_name(col1,col2,…) values();
*/