sql 2005 email store procedure

i am trying to create a store procedure. where the count is more than zero it should send an email with all records. can some one help me out with this.
declare @maxcount as varchar(10)
 
select @maxcount = count(id_number)from dbo.iqm_newemployees
 
select @maxcount
 
if @maxcount >= 3 
	Select @maxcount
 
DECLARE @ProductTotals TABLE 
(
[id_number] [int] NOT NULL,
	[employee_name] [varchar](51) NOT NULL,
	[account_disabled] [bit] NOT NULL,
	[startdate] [datetime] NULL,
	[defaultlocation] [int] NULL
 
)
insert into @ProductTotals(id_number,employee_name,account_disabled,startdate,defaultlocation)
select id_number,employee_name,account_disabled,startdate,defaultlocation from dbo.iqm_newemployees
where status = 0 
 
select * from @ProductTotals
 
 
 
 
	EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @body= 'select * from @producttotals', 
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'
 
 
else
 select 'test'

Open in new window

romeiovasuAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
   EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @query = 'select * from @producttotals',
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'

 
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
just do
select * from @productTotals

if @@rowcount > 0
              exec . . .
else
select 'No Rows to Email' as Status
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
insert into @ProductTotals(id_number,employee_name,account_disabled,startdate,defaultlocation)
select id_number,employee_name,account_disabled,startdate,defaultlocation from dbo.iqm_newemployees
where status = 0

IF @@ROWCOUNT > 0
 
      EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @body= 'select * from @producttotals',
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'
 
0
 
romeiovasuAuthor Commented:
but in my email i am gettign this one

select * from @producttotals

i am not getting the result.
0
 
romeiovasuAuthor Commented:
this is the error i am getting now

(3 row(s) affected)
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 1087, Level 15, State 2, Server ARJAYDATA, Line 1
Must declare the table variable "@producttotals".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.