Stored proc in a function

Question

Stored proc in a function

am-244616

SSCarpal Tunnel

Points: 4936
More actions
April 16, 2007 at 6:36 am

#177352

guys,
I have a stored proc - 'NEXTVAL' which gives me value for the column. I pass the tablename as the
parameter
for example EXEC NEXTVAL 'EMPLOYEE' gives me the value of the column EMPID.
I want to execute this stored proc from a function
CREATE FUNCTION GETNEXTVAL (NAME VARCHAR(30))
RETURNS INT AS
BEGIN
EXEC NEXTVAL @NAME
END
I am unable to figure out a way to do so.
Any suggestions and inputs would be helpful
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You just can't do that (without going 1000 loops and workarounds which I would strongly advise against). Why aren't you using the identity property of sql server? This is by far the simplest way to do this.

Mrugajit Gaekwad SSC Journeyman Points: 85 More actions · Answer 2

Sri,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

I am not sure what you are trying to achieve with the way you are trying to achieve, there are couple of alternatives. <o:p></o:p>

1. Why don't you implement the logic that you have in proc directly in a function. If multiple values are expected than, you can use table-valued user defined function. - Then if you want, you can call a functionwithin a function.<o:p></o:p>

2. Above example is misleading as it looks like you are trying to retrieve columns of a table andyour function return type is INT. You really need table-valued function.<o:p></o:p>

As far as calling a proc in a function is concern, I don't believe this is an option with SQL Server 2000 unless you create a extended stored procedure.I am not sure 2005.<o:p></o:p>

Good luck!<o:p></o:p>

Mrugajit<o:p></o:p>

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

This smacks of the Oracle-like "sequence" number... the others are correct... why aren't you using the auto-numbering capabilities of an IDENTITY column?

Now, I'll also admit that there are requirements like "it's a 3rd party table that cannot be changed" but you still need to write to it using their miserable attempt to duplicate Oracle sequences in SQL Server. I'll also tell you that it becomes a huge hot-spot for deadlocks unless the "get NextID" code is written absolutely perfectly... I'll give you a hint... if the code contains a declared transaction which contains an UPDATE and a SELECT, you're dead meat.

I strongly recommend you convert to IDENTITY columns, if possible, and I strongly recommend you post your "get NextID" code if you cannot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)