Must declare the scalar variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Must declare the scalar variable

Author

Topic

chip
Starting Member

14 Posts

Posted - 2009-03-05 : 17:04:39

I am having problems with a stored procedure I am trying to create.

USE [Sales]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Test_Email]
(
@ProcessDate smalldatetime,
@Market char(8)
)
as
begin
--
-- Variable definitions
--
DECLARE @EmailAddress varchar(1024) SET @EmailAddress = 'george.dickson@medfinders.com'
DECLARE @1MessageHeader varchar(50) SET @lMessageHeader = @Market
DECLARE @1MessageBody varchar(1024) SET @lMessageBody = 'The Processing Date passed to the stored procedure is ' + @ProcessDate
--
--
-- Send email
--
-- Use Database Mail
EXEC sp_send_dbmail @recipients = @EmailAddress, @subject = @lMessageHeader, @body = @lMessageBody

end

Error Messages:
Msg 137, Level 15, State 1, Procedure Test_Email, Line 17
Must declare the scalar variable "@lMessageHeader".
Msg 137, Level 15, State 1, Procedure Test_Email, Line 18
Must declare the scalar variable "@lMessageBody".
Msg 137, Level 15, State 2, Procedure Test_Email, Line 29
Must declare the scalar variable "@lMessageHeader".

Chip Dickson
Texas

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-05 : 17:12:28

You have named your variables @1 but you are referring to them as @l, that's the number one in the DECLARE and the letter L in the EXEC to database mail.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-05 : 17:12:49

To avoid this, avoid free typing them, just copy/paste.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-05 : 17:14:41

it must have been a long day.
look at your variable declaration again

DECLARE @1MessageHeader varchar(50) SET @lMessageHeader = @Market
DECLARE @1MessageBody varchar(1024) SET @lMessageBody@ = 'The Processing Date passed to the stored procedure is ' + @ProcessDate

time for some hot chocolate

chip
Starting Member

14 Posts

Posted - 2009-03-05 : 17:55:02

It has been a long day. I almost copied pasted before I tried the forum and got rushed. Working now! Thanks everyone. Sorry it was such a brain dead issue.

Chip Dickson
Texas

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-06 : 00:31:05

You're welcome!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources