How to create/drop function within stored procedure in SQL Server

Question

I have created a stored procedure where I an looping through data and inserting records based on the data. The inserts have the same format, but the data changes slightly.

The decision making is handled with IF/ELSE statements, and I have a dozen or so INSERT queries (one for each decision).

I'd like to put the INSERT into a function that I can just call with the variables that change.

I would like to create the function at the beginning of the stored procedure, and then DROP it at the end.

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[DataParser]
AS
    CREATE FUNCTION [dbo].[InsertFunc]
        (@Number VARCHAR(40),
         @Line_No INT,
         @Topic VARCHAR(30),
         @Note VARCHAR(4099),
         @Bool VARCHAR(1))
    RETURNS bit
    AS
    BEGIN
        INSERT INTO Processing (Number, Line_No, Topic, Note, Activation_Date, Mandatory)
        VALUES (@Number, @Line_No, @Topic, @Note,
                CONVERT(VARCHAR(8), GETDATE(), 1), @Mandatory)

        RETURN @@ROWCOUNT
    END
    ...
    ...
    Decision tree
    ...
    ...
    InsertFunc(@oid, @lidCounter, 'StrVal1', @strVar2, 'Y')

Right now it will not let me save the stored procedure, and if I try to create the function outside of the stored procedure, I get the following error:

Msg 443, Level 16, State 15, Procedure DataParser, Line 12 [Batch Start Line 23]
Invalid use of a side-effecting operator 'INSERT' within a function.

Any help is appreciated.

Can you explain what are you trying to do here ? Why do you want to create a function inside a stored procedure ? — Squirrel
– Squirrel, Commented Jan 25, 2019 at 2:56

marc_s · Accepted Answer · 2019-01-25 05:11:04Z

2

The short answer is that in SQL Server you cannot perform INSERT / UPDATE / DELETE actions within a function.

Although I don't recommend an approach where you are creating and removing stored procedures dynamically, if you try your approach creating a stored procedure instead of a function it should work.

edited Jan 25, 2019 at 5:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 25, 2019 at 1:41

Mathew Paxinos

9647 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dizzy49 Over a year ago

I changed it to CREATE PROCEDURE, removed the RETURNS bit, and RETURN @@ROWNUM. I'm getting "Incorrect syntax near the keyword 'PROCEDURE'." and "Must declare the scalar variable "@Import_Set_Number"."

Rasanjana N Over a year ago

Can you please update the question again? With what you have mentioned here

sandeep sharma · Accepted Answer · 2019-01-25 06:30:44Z

1

Below is stored procedure that may help you.

Create Table #Temp ( Number varchar(40), Line_No INT, Topic VARCHAR(30), Note VARCHAR(4099), Bool VARCHAR(1), Activation_Date datetime, Mandatory varchar(10) )

CREATE procedure [dbo].[InsertDataIntoTable]
    (@Number VARCHAR(40),
     @Line_No INT,
     @Topic VARCHAR(30),
     @Note VARCHAR(4099),
     @Bool VARCHAR(1),
     @Mandatory varchar(4))

AS
BEGIN
    INSERT INTO #Temp (Number, Line_No, Topic, Note, Activation_Date, Mandatory)
    VALUES (@Number, @Line_No, @Topic, @Note,
            CONVERT(VARCHAR(8), GETDATE(), 1), @Mandatory)


END

answered Jan 25, 2019 at 6:30

sandeep sharma

605 bronze badges

Collectives™ on Stack Overflow

How to create/drop function within stored procedure in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related