INSERT INTO in usde defined function

Question

INSERT INTO in usde defined function

Thomas Vanting

SSC Rookie

Points: 48
More actions
August 3, 2004 at 12:47 am

#180985

Hallo
A problem with functions. I need a function to insert default values into a table, therefor I tryed to use a user defined function, like this
CREATE FUNCTION [dbo].[ufn_AddStdHeader] (@iPrintVareNr int,@iProducLoc int,@iRunNr int,@uctTime int)
RETURNS INTEGER
AS
BEGIN
DECLARE @iN int
INSERT INTO dbo.TestHeaderTabel
(StartTime,TestTime,TestStatus,Operator,Login,Variant,DUT_id,Tester_id,PrintID,FixturID,Executerversion,SeqName,Rep_Count,PrintVareNr,ProductionLoc,RunNr,Fejlkode,Blok,Aar)
VALUES(@uctTime,0,128,'',2,'Basic',0,'',0,'',0,0,0,@iPrintVareNr,@iProducLoc,@iRunNr,128,0,0)
SET @iN = (SELECT N FROM TestHeaderTabel)
RETURN @iN
END
But when I run it, an error saying "Invalid use of 'INSERT' within a function." is displayed. What is the problem here?
Thanks
Thomas Vanting

Viewing 6 posts - 1 through 5 (of 5 total)

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

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

A user defined function cannot have side effects. Quote from BOL:

"The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function."

Razvan

Thomas Vanting SSC Rookie Points: 48 More actions · Answer 2

Thomas Vanting

SSC Rookie

Points: 48

August 3, 2004 at 4:30 am

#517407

Hi Razvan

Thanks,

Thomas

Arif Agovic SSC Veteran Points: 296 More actions · Answer 3

You have 2 options (at least) here:

1. Rewrite your function as SP so you can use INSERT INTO

2. Rewrite function to return table (single row in this case) so you can use INSERT INTO in the calling SP

Thomas Vanting SSC Rookie Points: 48 More actions · Answer 4

Hi Arif

Yes, this was also the options I found. So I have made an SP to do the job. And this works correct.

Thanks for all answers

Thomas Vanting

paras_98 SSCrazy Points: 2994 More actions · Answer 5

I already have a SP which generates unique IDs. But i cannot use that SP in INSERT INTO ... SELECT statement as SP cannot be used in selects. So I tried creating a function with the same logic, but it is giving me an error "Invalid use of 'INSERT' within a function."

Can anyone pls suggest on how such a problem needs to be tacked.

Thankx

Paras Shah

Paras Shah
Evision Technologies
Mumbai, India