view problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

view problem

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-27 : 09:05:30

hi i want an input parameter view where if record exists it should return 1 and if not exists it should return zero.

my view is something like this.

Create View [dbo].[emrVBUserInfo]@group_id varchar(50)
as
SELECT FEATURE_REF_ID,GROUP_ID,EMR_FEATURE_ID FROM EMRFEATUREREFERENCE WHERE GROUP_ID=@group_id AND EMR_FETAURE-ID=48
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 09:08:57

You should make use of a stored procedure. View wont do what you want

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 09:10:38

You can't have a parameter in a VIEW


Create View [dbo].[emrVBUserInfo] @group_id varchar(50)
as
SELECT FEATURE_REF_ID,GROUP_ID,EMR_FEATURE_ID FROM EMRFEATUREREFERENCE WHERE GROUP_ID=@group_id AND EMR_FETAURE-ID=48
GO

and then select using


SELECT * FROM [dbo].[emrVBUserInfo]
WHERE GROUP_ID = some ID

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-27 : 09:12:34

then if record exists i should return 1 or else 0 how to handle that

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-27 : 09:14:38

can you give me an example of how to do this with stored procedure

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 09:26:50

Create procedure [dbo].[emrVBUserInfo]
(
@group_id varchar(50)
)
as
If exists(SELECT FEATURE_REF_ID,GROUP_ID,EMR_FEATURE_ID FROM EMRFEATUREREFERENCE WHERE GROUP_ID=@group_id AND EMR_FETAURE-ID=48)
select 1 as status
else
select 0 as status

GO

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 11:34:46

Although you don't really want all those columns selected if its just an EXIST test.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 00:12:07