• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2585
  • Last Modified:

Invalid use of 'INSERT' within a function

Hi guys,

Once again, I need some help. I've been working on a lovely stored procedure, and I'm busy cleaning it up so that it works correctly, and now I'm getting the error:

Invalid use of 'INSERT' within a function

Can somebody please tell me what I'm doing wrong, or if I maybe need to create an extra function to be able to INSERT?

Here is the code I have:

CREATE Procedure JustinProc
AS
      IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_ParseCSVString]') and xtype in (N'FN', N'IF', N'TF'))
      DROP FUNCTION [dbo].[fn_ParseCSVString]
      GO


      CREATE FUNCTION fn_ParseCSVString
      (
      @CSVString     varchar(8000) ,
      @Delimiter    varchar(10)
      )
      returns @tbl TABLE (s varchar(1000))
      AS
      BEGIN
      DECLARE @i int ,
            @j int
            SELECT     @i = 1
            WHILE @i <= len(@CSVString)
            BEGIN
                  SELECT    @j = charindex(@Delimiter, @CSVString, @i)
                  IF @j = 0
                  BEGIN
                        SELECT    @j = len(@CSVString) + 1
                  END
                  INSERT    @tbl select substring(@CSVString, @i, @j - @i)
                  SELECT    @i = @j + len(@Delimiter)
            END
            RETURN
      END
      GO

      IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_HandleData]') and xtype in (N'FN', N'IF', N'TF'))
      DROP FUNCTION [dbo].[fn_HandleData]
      GO

      CREATE FUNCTION fn_HandleData
      (
            @data as nvarchar(50),
            @equipment as nvarchar(50),
            @serial as nvarchar(50),
            @description as nvarchar(50)
      )
      returns @dump TABLE (Results nvarchar(50))
      AS
      BEGIN
            DECLARE c CURSOR FOR
            SELECT * FROM dbo.fn_ParseCSVString (@data,'-')

            DECLARE @varA nvarchar(50)
            DECLARE @varB nvarchar(50)
            DECLARE @varC nvarchar(50)
            DECLARE @varD nvarchar(50)
            DECLARE @varE nvarchar(50)
            DECLARE @varF nvarchar(50)
            DECLARE @varG nvarchar(50)

            OPEN c
            FETCH NEXT FROM c INTO @varA
            FETCH NEXT FROM c INTO @varB
            FETCH NEXT FROM c INTO @varC
            FETCH NEXT FROM c INTO @varD
            FETCH NEXT FROM c INTO @varE
            FETCH NEXT FROM c INTO @varF
            FETCH NEXT FROM c INTO @varG
            CLOSE c
            DEALLOCATE c

            DECLARE @site nvarchar(50)
            DECLARE @room nvarchar(50)
            DECLARE @cab nvarchar(50)
            DECLARE @device nvarchar(50)
            DECLARE @module nvarchar(50)

            SELECT @site    = @varA + '-' + @varB
            SELECT @room    = @varA + '-' + @varB + '-' + @varC
            SELECT @cab     = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
            SELECT @device  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
            SELECT @module  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG

            IF @varA IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @varA + '` is a blank field.',
                              ''
                        )
                  END
            ELSE IF @varB IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @varB + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varC IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @varC + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varD IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @varD+ '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varE IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @varE + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varF IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @varF + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varG IS NULL
                  BEGIN
                        IF EXISTS(SELECT 1 FROM dbo.tblDevice WHERE Equipment = @Equipment AND FunctLocation = @Data)
                              BEGIN
                                    INSERT INTO dbo.tblLogs
                                    (
                                          Action,
                                          Description,
                                          [Timestamp]
                                    ) VALUES (
                                          'Update',
                                          'Update for `' + @Equipment + '` not required.',
                                          ''
                                    )
                              END
                        ELSE IF EXISTS(SELECT 1 FROM dbo.tblImportData WHERE Equipment = @Equipment)
                              BEGIN
                                    INSERT INTO dbo.tblLogs
                                    (
                                          Action,
                                          Description,
                                          [Timestamp]
                                    ) VALUES (
                                          'Update',
                                          'Updated `' + @Equipment + '`.',
                                          ''
                                    )
                              END
                        ELSE
                              BEGIN
                                    IF EXISTS(SELECT 1 FROM dbo.tblCab WHERE Cab_FL = @cab)
                                          BEGIN
                                                DECLARE @varUID nvarchar(50)
                                                DECLARE c CURSOR FOR
                                                SELECT UID FROM dbo.tblCab WHERE Cab_FL = @Equipment
                                                OPEN c
                                                      FETCH NEXT FROM c INTO @varUID
                                                CLOSE c
                                                DEALLOCATE c
                                                INSERT INTO dbo.tblDevice (Cab_ID, DeviceID, SerialNumber) VALUES (@varUID, @Equipment, @serial)
                                                INSERT INTO dbo.tblLogs
                                                (
                                                      Action,
                                                      Description,
                                                      [Timestamp]
                                                ) VALUES (
                                                      'Insert',
                                                      'Inserted `' + @varUID + '` into Device Table',
                                                      ''
                                                )
                                                IF EXISTS(SELECT 1 FROM dbo.tblDevice_Cat WHERE Model = @description)
                                                      BEGIN
                                                            DECLARE @varCatUID nvarchar(50)
                                                            DECLARE c CURSOR FOR
                                                            SELECT UID FROM dbo.tblDevice_Cat WHERE Model = @description
                                                            OPEN c
                                                                  FETCH NEXT FROM c INTO @varCatUID
                                                            CLOSE c
                                                            INSERT INTO dbo.tblDevice (DeviceCatID) VALUES (@varCatUID)
                                                            INSERT INTO dbo.tblLogs
                                                            (
                                                                  Action,
                                                                  Description,
                                                                  [Timestamp]
                                                            ) VALUES (
                                                                  'Insert',
                                                                  'Inserted `' + @varCatUID + '` into Device Table',
                                                                  ''
                                                            )
                                                      END
                                          END
                                    ELSE
                                          BEGIN
                                                INSERT INTO dbo.tblLogs
                                                (
                                                      Action,
                                                      Description,
                                                      [Timestamp]
                                                ) VALUES (
                                                      'Insert',
                                                      'Inserted `' + @cab + '`.',
                                                      ''
                                                )
                                          END
                              END
                  END
            ELSE
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Message',
                              'Message: `' + @varUID + '` is a Module.',
                              ''
                        )
                  END
            RETURN
      END
      GO

      DECLARE @FunctLocation varchar(30)
      DECLARE @Equipment varchar(30)
      DECLARE @Serial varchar(30)
      DECLARE @Description varchar(30)
      DECLARE Cur Cursor FOR
      SELECT FunctLocation, Equipment, [Serial number], Description FROM dbo.tblImportData
      OPEN Cur
      FETCH NEXT FROM Cur INTO @FunctLocation,@Equipment, @Serial, @Description

      WHILE @@FETCH_STATUS= 0
      BEGIN
        SELECT * FROM dbo.fn_HandleData(@FunctLocation,@Equipment,@Serial,@Description)
        FETCH NEXT FROM Cur INTO @FunctLocation,@Equipment,@Serial,@Description
      END

      CLOSE Cur
      DEALLOCATE Cur
GO
0
Cyber-Drugs
Asked:
Cyber-Drugs
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot use INSERT, DELETE or UPDATE inside a function.
you cannot use CREATE xxx inside a FUNCTION neither
you cannot use dynamic sql inside a function
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hence, you have to stick for this job with a stored procedure
0
 
OtanaCommented:
You can't use INSERT in a function. You may have to work with stored procedures with output variables.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Cyber-DrugsAuthor Commented:
So I need to create loads of different stored procedures, or can I make a Dynamic Update Procedure which I can call like a Function?
0
 
OtanaCommented:
You can use different stored procedures, which avoids creating them each time you need them, or you can create temporary stored procedures.
0
 
Cyber-DrugsAuthor Commented:
OK, so my best bet is to make a load of stored procedures at the top of the code and then calling them?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
One more problem with your code
If you use a 'GO', inside an sp , it will indicate the end of that sp, and the following lines wont be the part of the SP
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
So, in your above procedure the actual code you will get from the procedure is

CREATE Procedure JustinProc
AS
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_ParseCSVString]') and xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[fn_ParseCSVString]
GO


the rest statements will be treated as  separated batches, and those won't be included inside this procedure
0
 
Cyber-DrugsAuthor Commented:
There's a problem with me using static stored procedures, I need to be able to store the ID of record which is being looped through in the INSERT to tblLog functions, is there some kind of work-a-round I can use to fix this?
0
 
Cyber-DrugsAuthor Commented:
also, thanks aneeshattingal for pointing that out, that's one less error for me to worry about now. I just need to sort out this INSERT problem out...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Convert that Function to a Stored Procedure ..

I hope you are planning to call that function inside some Stored Procedure, the same thing can be achived if you convert this as a procedure.  You need to create a temp table and can call.
So instead of

select * from dbo.fn_HandleData (...)

you can use

create table #dump (results nvarchar(50))
INSERT INTO #dump
EXEC usp_HandleData ....

SELECT * FROM #dump

Hope this will help  you
0
 
Cyber-DrugsAuthor Commented:
That worked great!! I have a few more errors to get rid of, but since this question has been solved, I will put them in another question.

Thanks again!! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now