Re: Calling Stored Proc from a Function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 11:32:42 +0200

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:
>
>> 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...
>> > 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
>>
>>
>>

Relevant Pages