?
Solved

Invalid use of 'INSERT' within a function

Posted on 2006-05-08
12
Medium Priority
?
2,581 Views
Last Modified: 2010-08-05
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
Comment
Question by:Cyber-Drugs
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16629271
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16629274
Hence, you have to stick for this job with a stored procedure
0
 
LVL 11

Expert Comment

by:Otana
ID: 16629290
You can't use INSERT in a function. You may have to work with stored procedures with output variables.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16629323
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
 
LVL 11

Expert Comment

by:Otana
ID: 16629341
You can use different stored procedures, which avoids creating them each time you need them, or you can create temporary stored procedures.
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16629359
OK, so my best bet is to make a load of stored procedures at the top of the code and then calling them?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16629397
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16629407
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16629418
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16629431
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16629466
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16629493
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question