Stored procedure to user defined function

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 2000 Forums

Transact-SQL (2000)

Stored procedure to user defined function

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2006-12-26 : 02:46:10

Pleas ecan some body help me converting following sp to user defined function
I am using sql server 2000

CREATE PROCEDURE [dbo].[getnextBooknumber]

@CounterType varchar(10)

AS

SET NOCOUNT ON

Declare @CounterValue As Float

Begin Tran

If not exists(Select * From Counter Where CounterType = @CounterType)

Begin

Set @CounterValue = 1

Insert Into Counter (CounterType, CounterValue) Values (@CounterType, @CounterValue)

End

Else

Begin

Select @CounterValue = CounterValue From Counter Where CounterType = @CounterType

Set @CounterValue = @CounterValue + 1

Update Counter Set CounterValue = @CounterValue Where CounterType = @CounterType

End

Commit Tran

SET NOCOUNT OFF

Select CounterValue = @CounterValue

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-26 : 02:59:48

Why do you want to use Function?
Doing this in procedure is correct method

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-26 : 03:00:20

You can't do Insert/Update/Delete inside a function except for a table variable. Why you want to convert it to UDF?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-12-26 : 06:37:50

There are few limitations to user defined function

1) Cannot call any function or stored procedure within a function
2) Cannot execute any DML inside a function.

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources