Insert Query in SQL Function

Insert/Update/Delete with function in SQL Server

No, you cannot.

From SQL Server Books Online:

User-defined functions cannot be used
to perform actions that modify the
database state.

Ref.

How to put insert statement in SQL function?

Insert will not work in SQL function, Your function will give below error.

Server: Msg 443, Level 16, State 2, Procedure CreateNewRow, Line 7
Invalid use of 'INSERT' within a function.

Alternative
use user-defined function that uses the INSERT command but the modified table is a table variable local to the function:

CREATE FUNCTION [dbo].[Ins_GetMaxValue]
( @pInt1 INT, @pInt2 INT, @pInt3 INT, @pInt4 INT )
RETURNS INT
AS
BEGIN
DECLARE @IntTable TABLE ( [IntValue] INT )
DECLARE @MaxValue INT

INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt1 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt2 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt3 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt4 )

SELECT @MaxValue = MAX( [IntValue] )
FROM @IntTable

RETURN @MaxValue
END
GO

Insert query in SQL Function

Quote from here:

User Defined Functions cannot be used
to modify base table information. The
DML statements INSERT, UPDATE, and
DELETE cannot be used on base tables.

So you can't do an INSERT in a function.

You might want to explain WHY you don't want to use a procedure.

SQL insert into SELECT from function returns same value

I'm not sure if this is exactly an answer to your question and I don't know if this is the best possible approach, but I think it may solve your problem. Just generate value for each selected row (using ROW_NUMBER()) and add this value to the result of your function. The function is changed and returns integer value.

CREATE FUNCTION [dbo].[NewCode] (
    @Date date
)
RETURNS numeric(4, 0) AS
BEGIN
    DECLARE @LastCode numeric(4, 0);
    SET @LastCode = (SELECT MAX(Code) FROM TABLE1 WHERE MONTH(TDate) = MONTH(@Date))
    IF @LastCode IS NULL 
        SET @LastCode = 1;
    ELSE
        SET @LastCode = CONVERT(int, @LastCode) + 1;
    RETURN @LastCode;
END

INSERT INTO Table1 
SELECT 
    T1, T2, T3, 
    RIGHT('0000' + CAST(dbo.NewCode(Table2.TDate) + ROW_NUMBER() OVER (ORDER BY T1) - 1 AS varchar(4)), 4)
    FROM Table2

Writing SQL Insert Function

In short, Functions are not allowed to make any changes to ANY SQL Server object. Stored procedures can. With minor changes, your code is now an SP.

CREATE PROC [dbo].[CreateUser]
    (
    @Username varchar(20),
    @Password varchar(20),
    @Email varchar(50),
    @PasswordQuestion varchar(30),
    @PasswordAnswer varchar(30)

    )
--RETURNS bit/* datatype */
AS
    BEGIN
        if  (Exists(Select Username from Users where Username=@Username and Password=@Password))
            return 1;
        else
            begin
                INSERT INTO dbo.Users (Username, Password, 
                                       Email, UserId, 
                                       IsApproved, IsLockedOut, 
                                       IsOnline, CreationDate, 
                                       PasswordQuestion, PasswordAnswer) VALUES (@Username, @Password, @Email, 
                                                                                 1, 0, 0, 0, GetDate(), 
                                                                                 @PasswordQuestion, @PasswordAnswer);
                return 0;
            end

    END
GO

And you can call it thus:

exec dbo.CreateUser 'Jim', 'Teddy', 'jim@do.not.email', 'Where', 'Here';

How a function/procedure can be called inside a insert statement

You can also use INSERT INTO... SELECT

INSERT INTO myTable (col1, col2, col3....)
SELECT 1, 2, mySP_or_mySDF(), 3, 4

If you want to INSERT data from a stored procedure, then you will need to create a temp table and insert the result into the temp table first, then you can use the result to insert into the final table.

Insert query in while loop in a function

You have WHILE @counter < 24, but never initialise the counter to any value.

The line DECLARE @counter INT; doesn't initialise the variable to 0, it initialises it to NULL. Which means your first loop is checking NULL < 24 which isn't TRUE.

Try this...

Declare @counter int = 0;
...
WHILE @counter < 24
BEGIN
    ...
    SET @counter = @counter + 1;
END 

You also seem to have other issues, such as select ' + @Size + ','+@Shp + ' from ... making no apparent sense.

EDIT:

I recommend starting with making a simple case work and building it up from there. If it ever stops working, the problem is what ever you last changed.

CREATE FUNCTION dbo.fn_GetSubTree1(
   @type AS NVARCHAR(50)
) 
RETURNS @tree TABLE (
   sizename  NVARCHAR(9) NOT NULL,
   shiptotal INT
)
AS
BEGIN
   DECLARE @counter INT = 0;
   WHILE (@counter < 24)
   BEGIN
      INSERT INTO
         @tree(
            sizename,
            shiptotal
         )
      VALUES (
         'Test',
         @counter
      )

      SET @counter = @counter + 1;
   END

   RETURN
END

---

Related Topics