0

I’m trying to adapt this code to send and email to everyone from my specific table but I keep getting this error (Incorrect syntax near the keyword 'BEGIN'.)

CREATE PROC test.send_email_group BEGIN

Use HDO;
CREATE PROC test.send_email_group 

BEGIN
-- variables to hold values for each row
DECLARE @facility_name VARCHAR(50)
DECLARE @email VARCHAR(50)
DECLARE @body VARCHAR(1000);

-- declare the cursor
DECLARE cEmails CURSOR FOR  
SELECT facility_name, email 
FROM [test].[send_email]
--WHERE [finish date] = DATEADD(d,-30,CAST(GETDATE() AS DATE))

-- Open the cursor and fetch first row
OPEN cEmails
FETCH NEXT FROM cEmails
INTO @facility_name, @email

WHILE @@FETCH_STATUS = 0   
BEGIN 

    -- Use database mail SP to send email
    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'QM_Mail',
         @recipients = @email,
         @subject = 'subject',
         @body=@facility_name

    -- Get the next row
    FETCH NEXT FROM cEmails
    INTO @facility_name, @email       
END
CLOSE cEmails
DEALLOCATE cEmails
END

2 Answers 2

2

You need an AS in your create statement.

CREATE PROC test.send_email_group 
AS
BEGIN

/** CODE **/

END
2
  • Thanks I tried that earlier but got this error ('CREATE/ALTER PROCEDURE' must be the first statement in a query batch.) Your response made me look at it closer at that error and noticed I had "Use HDO" before the create proc. I removed "Use HDO" it and its now it works. Thanks!!
    – kawi6rr
    Jan 6, 2023 at 20:19
  • 1
    @kawi6rr Please note CREATE and ALTER statements for procedures and views need to not be preceded by other statements so it's clear where the definition of that object starts (and ends). The SQL Server Engine recognizes the GO keyword as a way to also define that cutoff. So if you placed a GO after the Use HDO statement, then your code would've worked. (You can stack multiple CREATE and ALTER object definitions this way too.) Note the GO keyword is not part of the T-SQL language though and cannot be followed by a semi-colon like other T-SQL statements.
    – J.D.
    Jan 6, 2023 at 23:07
0

You are missing "AS" before BEGIN and You can terminate the batch (and start another) with the GO keyword.

Place use GO right before your Create procedure statement. The GO statement must be on a line by itself.

Please check create stored proc syntax: CREATE PROCEDURE (Transact-SQL)

I've tested below script:

Use [database_name] 
go
 
CREATE PROC test.send_email_group 
AS
BEGIN
-- variables to hold values for each row
DECLARE @facility_name VARCHAR(50)
DECLARE @email VARCHAR(50)
DECLARE @body VARCHAR(1000);

-- declare the cursor
DECLARE cEmails CURSOR FOR  
SELECT facility_name, email 
FROM [test].[send_email]
--WHERE [finish date] = DATEADD(d,-30,CAST(GETDATE() AS DATE))

-- Open the cursor and fetch first row
OPEN cEmails
FETCH NEXT FROM cEmails
INTO @facility_name, @email

WHILE @@FETCH_STATUS = 0   
BEGIN 

    -- Use database mail SP to send email
    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'QM_Mail',
         @recipients = @email,
         @subject = 'subject',
         @body=@facility_name

    -- Get the next row
    FETCH NEXT FROM cEmails
    INTO @facility_name, @email       
END
CLOSE cEmails
DEALLOCATE cEmails
END 
go

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.