Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql 2005 email store procedure

Posted on 2009-07-01
5
Medium Priority
?
190 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 41

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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

772 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