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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Aneesh RetnakaranDatabase AdministratorCommented:
   EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @query = 'select * from @producttotals',
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'

 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.