Solved

sql 2005 email store procedure

Posted on 2009-07-01
5
178 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:romeiovasu
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 24755754
just do
select * from @productTotals

if @@rowcount > 0
              exec . . .
else
select 'No Rows to Email' as Status
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24755786
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
 

Author Comment

by:romeiovasu
ID: 24755899
but in my email i am gettign this one

select * from @producttotals

i am not getting the result.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24755922
   EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @query = 'select * from @producttotals',
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'

 
0
 

Author Comment

by:romeiovasu
ID: 24755948
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now