Calling Stored Proc from a Function

I know that the rule is that you can only call an extended Stored Proc from
a
Function - NOT a normal Stored Proc which gives the following error.
"Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
Only functions and extended stored procedures can be executed from within a
function."
So, I tried to call it using sp_executesql() which IS an extended stored
proc with no luck'
Any help much appreciated. THanksYou can't use dynamic SQL in a function... What are you trying to do?
Explain your problem and perhaps we can figure out a better way to
accomplish whatever it is you need...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:BDC0F216-2E7E-4489-A6BE-076DD5BD47D8@.microsoft.com...
> I know that the rule is that you can only call an extended Stored Proc
from a
> Function - NOT a normal Stored Proc which gives the following error.
> "Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
> Only functions and extended stored procedures can be executed from within
a
> function."
> So, I tried to call it using sp_executesql() which IS an extended stored
> proc with no luck'
> Any help much appreciated. THanks|||Thanks for the reply Adam,
I am actually trying to emulate an ORACLE sequence.
I need to do a bulk insert and create "ID's" at the same time. We do not
have IDENTITY columns in our DB. Ifigured out how to do this but because I
need to do an update statement, I have to use a stored proc as you can only
do select's in a scalar function. So, to get around this, I wanted to call m
y
stored proc from a function. I need a function so I can call it in my select
.
Here are some code details. Thanks. The details are long as I was asking
about methods to achieve this in another thread but had no real resolution s
o
was just asking about the generic calling procs from functions here. Much
Thanks :0)
-- Fake Sequence to hold a number stream
CREATE TABLE sequences
(
seq varchar(100) primary key,
sequence_id int
);
ALTER PROCEDURE nextval
@.sequence varchar(100),
@.sequence_id INT OUTPUT
AS
BEGIN
set @.sequence_id = -1
UPDATE sequences
SET @.sequence_id = sequence_id = sequence_id + 1
WHERE seq = @.sequence
RETURN @.sequence_id
END
-- Function to call Stored Proc
ALTER function nextval2
( @.sequence varchar(100)) returns int
AS
BEGIN
declare @.sequence_id int
DECLARE @.sequence_id int
--EXEC dbo.nextval 'TestSeq', @.sequence_id OUTPUT
-- OR
-- exec sp_executesql N'dbo.nextval ''TestSeq'', @.sequence_id OUTPUT '
from sequences
RETURN @.sequence_id
END
go
-- Insert statement using Function
insert into glp.rf_contact
select dbo.nextval2('TestSeq'), name
from glp.contact
"Adam Machanic" wrote:

> You can't use dynamic SQL in a function... What are you trying to do?
> Explain your problem and perhaps we can figure out a better way to
> accomplish whatever it is you need...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:BDC0F216-2E7E-4489-A6BE-076DD5BD47D8@.microsoft.com...
> from a
> a
>
>|||"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
> I am actually trying to emulate an ORACLE sequence.
> I need to do a bulk insert and create "ID's" at the same time. We do not
> have IDENTITY columns in our DB. Ifigured out how to do this but because I
Don't have doesn't mean can't have :)
I highly recommend that you use an IDENTITY if you need that
functionality -- rolling your own will not work well for a variety of
reasons. The primary issue is that it will force all transactions inserting
into or updating the table to be serialized, which will totally destroy
concurrency. In addition, you really don't want a UDF called for every row
of a BULK INSERT, unless you want it to take 3 days to finish...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Amelia,

> -- Insert statement using Function
> insert into glp.rf_contact
> select dbo.nextval2('TestSeq'), name
> from glp.contact
As an alternative, you can use the following:
DECLARE @.rc AS INT, @.seqid AS INT;
SELECT IDENTITY(INT, 1, 1) AS id, name INTO #T FROM glp.contact;
SET @.rc = @.@.rowcount;
UPDATE sequences
SET @.seqid = sequence_id, sequence_id = sequence_id + @.rc;
INSERT INTO t1 SELECT @.seqid + id, name FROM #T;
DROP TABLE #T;
You can even encapsulate the whole process in a trigger and allow the users
to simply invoke the INSERTs.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
> Thanks for the reply Adam,
> I am actually trying to emulate an ORACLE sequence.
> I need to do a bulk insert and create "ID's" at the same time. We do not
> have IDENTITY columns in our DB. Ifigured out how to do this but because I
> need to do an update statement, I have to use a stored proc as you can
> only
> do select's in a scalar function. So, to get around this, I wanted to call
> my
> stored proc from a function. I need a function so I can call it in my
> select.
> Here are some code details. Thanks. The details are long as I was asking
> about methods to achieve this in another thread but had no real resolution
> so
> was just asking about the generic calling procs from functions here. Much
> Thanks :0)
> -- Fake Sequence to hold a number stream
> CREATE TABLE sequences
> (
> seq varchar(100) primary key,
> sequence_id int
> );
> ALTER PROCEDURE nextval
> @.sequence varchar(100),
> @.sequence_id INT OUTPUT
> AS
> BEGIN
> set @.sequence_id = -1
> UPDATE sequences
> SET @.sequence_id = sequence_id = sequence_id + 1
> WHERE seq = @.sequence
> RETURN @.sequence_id
> END
>
> -- Function to call Stored Proc
> ALTER function nextval2
> ( @.sequence varchar(100)) returns int
> AS
> BEGIN
> declare @.sequence_id int
> DECLARE @.sequence_id int
>
> --EXEC dbo.nextval 'TestSeq', @.sequence_id OUTPUT
> -- OR
> -- exec sp_executesql N'dbo.nextval ''TestSeq'', @.sequence_id OUTPUT '
> from sequences
> RETURN @.sequence_id
> END
> go
>
> -- Insert statement using Function
> insert into glp.rf_contact
> select dbo.nextval2('TestSeq'), name
> from glp.contact
>
>
> "Adam Machanic" wrote:
>|||Thanks so much for the Responses Adam and Itzik,
I cannot make changes to the table so adding and Identity Column is out but
your suggestion Itzik is great and may well just work! I was working along
the same lines as this but this is better than mine as it is still using a
sequence table instead of doing it all manually! That's up there for thinkin
g.
Thanks again :0)
"Itzik Ben-Gan" wrote:

> Amelia,
>
> As an alternative, you can use the following:
> DECLARE @.rc AS INT, @.seqid AS INT;
> SELECT IDENTITY(INT, 1, 1) AS id, name INTO #T FROM glp.contact;
> SET @.rc = @.@.rowcount;
> UPDATE sequences
> SET @.seqid = sequence_id, sequence_id = sequence_id + @.rc;
> INSERT INTO t1 SELECT @.seqid + id, name FROM #T;
> DROP TABLE #T;
> You can even encapsulate the whole process in a trigger and allow the user
s
> to simply invoke the INSERTs.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
>
>