CODEWITHSUNDEEP
sql-serverstored-procedures
Rahul Kishore
Rahul Kishore

Reputation: 380

Stored procedure multiple inserts error

I'm writing a simple stored procedure in SQL Server 2016 but when I have 2 insert statements I keep getting an error that says

Incorrect syntax near 'End'

Is there something wrong with my syntax or is this not possible?

Note that it is a table-valued parameter that I am sending into the stored procedure as arguments

SQL statement: 

-- This is the stored procedure
CREATE PROCEDURE [dbo].[SampleProcedure]
    ( -- which accepts 2 table value parameters
      -- It should be noted that the parameter is readonly
     @Sample As [dbo].[SampleDataType] Readonly, 
     @Rec As [dbo].[SampleRecType] ReadOnly
    )
AS
BEGIN
    -- We simply insert values into the DB table from the parameter
    -- The table value parameter can be used like a table with only read rights

    -- INSERT INTO SampleTable(SampleString, SampleDate)
    --     SELECT SampleString, SampleDate 
    --     FROM @Sample

    INSERT INTO tbl1Recipients(strEmailSendIDx, strEmailAddress, strDisplayName, strRecipientType)
    INSERT INTO tbl1SentEmails(strEmailSendID, dtmSent, strSubject, strBody, strConversationID, strConversationTopic, strConversationIndex, dtmReplied, dtmOpened, dtmClicked, blnTrackOpens, blnTrackClicks, blnTrackReplies, lngMergeID, blnHide, lngLatestEventID, strClickResponse, dtmClickResponse)
        SELECT * 
        FROM @Sample 
END

Edited, after reading the comments and the suggested answer this is what solved the issue: 

-- This is the stored procedure
CREATE PROCEDURE [dbo].[SampleProcedure]
    (
    -- which accepts one table value parameter. 
    -- It should be noted that the parameter is readonly
    @Sample As [dbo].[SampleDataType] Readonly, 
    @Rec As [dbo].[SampleRecType] ReadOnly
    )
AS
BEGIN
    -- We simply insert values into the DB table from the parameter
    -- The table value parameter can be used like a table with only read rights

    INSERT INTO tbl1Recipients(strEmailSendID, strEmailAddress, strDisplayName, strRecipientType)
        SELECT * 
        FROM @Rec

    INSERT INTO tbl1SentEmails(strEmailSendID, dtmSent, strSubject, strBody, strConversationID, strConversationTopic, strConversationIndex, dtmReplied, dtmOpened, dtmClicked, blnTrackOpens, blnTrackClicks, blnTrackReplies, lngMergeID, blnHide, lngLatestEventID, strClickResponse, dtmClickResponse)
        SELECT * 
        FROM @Sample 
END

All it needed was another SELECT after I entered the data into tbl1Recipients

Upvotes: 0

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

This is your first insert:

Insert Into tbl1Recipients(strEmailSendIDx, strEmailAddress, strDisplayName, strRecipientType)
Select * From @Rec

What is expected next is VALUES or SELECT. The next token is INSERT, and that just doesn't make sense.

You need to include the data you want to insert.

Upvotes: 1

Related Questions