Trying to create a view with parameters.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to create a view with parameters.

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-09-13 : 09:51:19

Trying to create a view with parameters. I want to use view inside the SP. But I'm getting an error. Why?

-- 1st
-- drop table funkete
create table funkete (funketeid int, funketeFname varchar(20))
insert funkete
select 1, 'funkete' union all
select 5, 'amanda' union all
select 6, 'funkete'
go

-- 2nd
-- drop proc a
create proc a (@funketeid int)
as
select @funketeid, funketefname from funkete
go

-- 3rd
exec a @funketeid = 4
go

-- 4th
create view vw_funkete4 as
declare @funketeid int
select @funketeid, funketefname from funkete
go

=============================
http://www.sqlserverstudy.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-13 : 09:54:04

What is wrong in option 2?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 09:54:58

Or option #5, write a table-valued function?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-13 : 10:04:08

you can't create a view with parameters because of a very simple reason:
"you can't create a view with parameters"

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 10:04:43

quote:
Originally posted by funketekun
But I'm getting an error. Why?

Why?

Because this is wrong


-- 3rd
exec a @funketeid = 4
go

s/b


exec a @funketeid 4

And this


-- 4th
create view vw_funkete4 as
declare @funketeid int
select @funketeid, funketefname  from funkete
go

Can not be done

That's why

=============================
http://www.sqlserverstudy.com

[/quote]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-09-13 : 10:25:57

listen to Peso: a parameterized view is called a table-valued function in SQL Server.

elsasoft.org

onlyanshul4u
Starting Member

6 Posts

Posted - 2007-09-13 : 10:44:40

Yeah batter to write table-valued function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources