stored procedure required - 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)

stored procedure required

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 04:10:06

hi iam having table as emrids
select * from emrids

property_name ezemrxid
ALCOHOL_ID 1503
ALERT_TRACK_ID 1500
ALLERGIC_ID 1505
ALLERGY_ID 1500
APPOINTMENT_ID 1509
APPOINTMENT_TYPE_ID 1503
AUDIT_QUERY_ID 1500
BILL_UPDATE_TXN_ID 1500
BILLING_TX_ID 1503
CAFFEINE_ID 1512
CC_ID 1500
CHESTXRAYRESULT_ID 1502

hi if i pass property_name as i/p parameter i want the corresponding emrid value to be incremeted by 1.
i.e if i give ALCOHOL_ID as i/p it should return the o/p as 1504.

please help me in givinf stored procedure for this

Kabila
Starting Member

33 Posts

Posted - 2009-09-30 : 04:17:17

create proc Procname(@Param varchar(30))
as
select col2+1 from Tablename where col1=@Param

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-30 : 04:18:51

So for CHESTXRAYRESULT_ID it would return 1503 and 1503 is already in use for ALCOHOL_ID.
What are you trying to do????

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 04:22:28

create procedure Generateids(@Param varchar(30))
as
select ezEMRxID+1 from EMRIDS where PROPERTY_NAME=@Param

exec Generateids('ALCOHOL_ID')

given kile this getting exception as
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ALCOHOL_ID'.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 04:25:35

got it

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 04:35:43

i want to increment the value by 1 updating when ever i call the procedure

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 04:57:52

hi sorry to post again my requirement is like this and need a SP for this.

-- read the value xxx
-- increment by 1 and update the value yyy
--return xxx

rammohan
Posting Yak Master

212 Posts

Posted - 2009-09-30 : 05:04:04

Clear some issues:
1. is ezemrxid a primary or foreign ke constarint or not?
2. r u want to update the ezemrxid value by 1 in the table also or only in display?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 05:05:45

not a primary key or foreign key.
yeah i want to update it by 1 and want to return the old value as well if it is 1503 i want to update it bu 1504 and want to return the old value as 1503.please help me in this regard

rammohan
Posting Yak Master

212 Posts

Posted - 2009-09-30 : 05:15:50

oops jus try this,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[RT_GetData]
@input varchar(100)
as
begin
select Data_ID as OP from Tbl_TestData where Data_Name = @input
update Tbl_TestData set Data_ID = Data_ID+1 where Data_Name = @input
return
end

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources