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_Pars
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_Hand
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,
WHILE @@FETCH_STATUS= 0
BEGIN
SELECT * FROM dbo.fn_HandleData(@FunctLo
FETCH NEXT FROM Cur INTO @FunctLocation,@Equipment,
END
CLOSE Cur
DEALLOCATE Cur
GO
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_Pars
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_Hand
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,
WHILE @@FETCH_STATUS= 0
BEGIN
SELECT * FROM dbo.fn_HandleData(@FunctLo
FETCH NEXT FROM Cur INTO @FunctLocation,@Equipment,
END
CLOSE Cur
DEALLOCATE Cur
GO
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Hence, you have to stick for this job with a stored procedure
You can't use INSERT in a function. You may have to work with stored procedures with output variables.
ASKER
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?
You can use different stored procedures, which avoids creating them each time you need them, or you can create temporary stored procedures.
ASKER
OK, so my best bet is to make a load of stored procedures at the top of the code and then calling them?
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
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
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_Pars
DROP FUNCTION [dbo].[fn_ParseCSVString]
GO
the rest statements will be treated as separated batches, and those won't be included inside this procedure
CREATE Procedure JustinProc
AS
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_Pars
DROP FUNCTION [dbo].[fn_ParseCSVString]
GO
the rest statements will be treated as separated batches, and those won't be included inside this procedure
ASKER
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?
ASKER
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!! :)
Thanks again!! :)
you cannot use CREATE xxx inside a FUNCTION neither
you cannot use dynamic sql inside a function