Please help me out with this stored procedure

• biren

  Right there with Babe

  Points: 719

  More actions

  May 5, 2010 at 11:56 am

  #224200

  EXEC msdb.dbo.sp_send_dbmail @profile_name=N'ebayOchkee',

  @recipients = N'adventorworks@gmail.com',

  @subject = N'Hot Items out of Stock',

  DECLARE @query varchar;

  SET @query =N'SELECT categoryname,brandname,productname,colorname,colorcode,psizename FROM p.dbo.B WHERE isnull(available_qty,0) = 0 AND isnull(available_qty_temp,0) = 0 AND hotflag = y

  UPDATE p.dbo.B in (SELECT categoryname,brandname,productname,colorname,colorcode,psizename FROM p.dbo.B WHERE isnull(available_qty,0) = 0 AND isnull(available_qty_temp,0) = 0 AND hotflag = y) SET hotflagemail = 1',

  @body = @query

  END

  I am getting error saying:

  Msg 156, Level 15, State 1, Line 5

  Incorrect syntax near the keyword 'DECLARE'.

  Msg 102, Level 15, State 1, Line 8

  Incorrect syntax near ','.

  what I am doing above is in @query there are two query written where it will select from provide table and in second it will update it after select.

  then I am sending this result set through database mail by scheduling job in sql server agent.

  can anybody in here guide me to do above in correct way please

• Cory E.

  SSCertifiable

  Points: 7076

  More actions

  May 5, 2010 at 11:59 am

  #1162664

  @subject = N'Hot Items out of Stock',

  DECLARE @query varchar;

  should be

  @subject = N'Hot Items out of Stock'--, <== Remove this comma

  DECLARE @query varchar;

  -- Cory

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 5, 2010 at 12:05 pm

  #1162669

  also, isn't your UPDATE statemnt in a non-SQL server format?

  i'd think it should be something like this:

  UPDATE p.dbo.B

  SET hotflagemail = 1

  WHERE isnull(available_qty,0) = 0

  AND isnull(available_qty_temp,0) = 0

  AND hotflag = y

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• adalberto-339588

  SSC Veteran

  Points: 261

  More actions

  May 5, 2010 at 12:13 pm

  #1162678

  Try this one and let me know.

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'ebayOchkee',

  @recipients = 'adventorworks@gmail.com',

  @query = ‘SELECT categoryname,brandname,productname,colorname,colorcode,psizename

  FROM p.dbo.B WHERE isnull(available_qty,0) = 0

  AND isnull(available_qty_temp,0) = 0 AND hotflag = y

  UPDATE p.dbo.B in (SELECT categoryname,brandname,productname,colorname,colorcode,psizename FROM p.dbo.B WHERE isnull(available_qty,0) = 0 AND isnull(available_qty_temp,0) = 0 AND hotflag = y) SET hotflagemail = 1' ,

  @subject = 'Hot Items out of Stock',

  @attach_query_result_as_file = 1;

• biren

  Right there with Babe

  Points: 719

  More actions

  May 5, 2010 at 12:28 pm

  #1162685

  adalberto-339588 (5/5/2010)

  ---

  Try this one and let me know.

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'ebayOchkee',

  @recipients = 'adventorworks@gmail.com',

  @query = ‘SELECT categoryname,brandname,productname,colorname,colorcode,psizename

  FROM p.dbo.B WHERE isnull(available_qty,0) = 0

  AND isnull(available_qty_temp,0) = 0 AND hotflag = y

  UPDATE p.dbo.B in (SELECT categoryname,brandname,productname,colorname,colorcode,psizename FROM p.dbo.B WHERE isnull(available_qty,0) = 0 AND isnull(available_qty_temp,0) = 0 AND hotflag = y) SET hotflagemail = 1' ,

  @subject = 'Hot Items out of Stock',

  @attach_query_result_as_file = 1;

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'ebayOchkee',

  @recipients = 'ptlbiren@gmail.com',

  @query = 'SELECT categoryname,brandname,productname,colorname,colorcode,psizename

  FROM popularglasses.dbo.B WHERE isnull(available_qty,0) = 0

  AND isnull(available_qty_temp,0) = 0 AND hotflag = y

  UPDATE popularglasses.dbo.B in (SELECT categoryname,brandname,productname,colorname,colorcode,psizename FROM popularglasses.dbo.B WHERE isnull(available_qty,0) = 0 AND isnull(available_qty_temp,0) = 0 AND hotflag = y) SET hotflagemail = 1' ,

  @subject = 'Hot Items out of Stock',

  @attach_query_result_as_file = 1;

  above procedure gave this error

  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 504

  Query execution failed: ?Msg 156, Level 15, State 1, Server WTFS938, Line 5

  Incorrect syntax near the keyword 'in'.

  Msg 102, Level 15, State 1, Server WTFS938, Line 5

  Incorrect syntax near '='.

• biren

  Right there with Babe

  Points: 719

  More actions

  May 5, 2010 at 12:28 pm

  #1162686

  I appreciate you all for replying... thank you!!

• biren

  Right there with Babe

  Points: 719

  More actions

  May 6, 2010 at 8:46 am

  #1163285

  i have one problem how i can implement if result set is null in this stored procedure than it wont sent out any email...

  DECLARE @tableHTML NVARCHAR(MAX);

  SET @tableHTML =

  N'<H1>Hot Item(S) out of Stock <H1>' +

  N'<table border="1">' +

  N'<tr><td>Brand Name</td><td>Category Name</td>' +

  N'<td>Product Name</td><td>Color Name</td>' +

  N'<td>Color Code</td><td>Sizes</td></tr>' +

  CAST ( ( SELECT td = categoryname, '',

  td = brandname, '',

  td = productname, '',

  td = colorname, '',

  td = colorcode, '',

  td = psizename, ''

  FROM B WHERE ISNULL(available_qty,0) = 0 AND ISNULL(available_qty_temp,0) = 0 AND hotflag ='y' AND hotflagemail = 0 FOR XML PATH('tr'), TYPE) AS VARCHAR(MAX)) +

  N'</table>';

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ebay',

  @recipients='adventorworks@gmail.com',

  @subject = 'HOT ITEM(s) OUT OF STOCK',

  @body = @tableHTML,

  @body_format = 'HTML' ;

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 6, 2010 at 8:56 am

  #1163297

  i suspect you need to use ISNULL for each of your columns, right?

  concatenating NULL to anything is null, so i think that's the problem:

  DECLARE @tableHTML NVARCHAR(MAX);

  SET @tableHTML =

  N'<H1>Hot Item(S) out of Stock <H1>' +

  N'<table border="1">' +

  N'<tr><td>Brand Name</td><td>Category Name</td>' +

  N'<td>Product Name</td><td>Color Name</td>' +

  N'<td>Color Code</td><td>Sizes</td></tr>' +

  CAST ( ( SELECT td = ISNULL(categoryname,''), '',

  td = ISNULL(brandname,''), '',

  td = ISNULL(productname,''), '',

  td = ISNULL(colorname,''), '',

  td = ISNULL(colorcode,''), '',

  td = ISNULL(psizename,''), ''

  FROM B

  WHERE ISNULL(available_qty,0) = 0

  AND ISNULL(available_qty_temp,0) = 0

  AND hotflag ='y'

  AND hotflagemail = 0

  FOR XML PATH('tr'), TYPE) AS VARCHAR(MAX)) +

  N'</table>';

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ebay',

  @recipients='adventorworks@gmail.com',

  @subject = 'HOT ITEM(s) OUT OF STOCK',

  @body = @tableHTML,

  @body_format = 'HTML' ;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Dan.Humphries

  SSChampion

  Points: 12125

  More actions

  May 6, 2010 at 8:56 am

  #1163298

  Declare a varialbe as int

  set the variable to count of records with your where clause so something like this.

  Select count(*) from yourtable where column=cluase

  then add an if statement before the email section to check if the count is greater than 1

  Dan

  If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

• biren

  Right there with Babe

  Points: 719

  More actions

  May 6, 2010 at 9:13 am

  #1163331

  Dan.Humphries (5/6/2010)

  ---

  Declare a varialbe as int

  set the variable to count of records with your where clause so something like this.

  Select count(*) from yourtable where column=cluase

  then add an if statement before the email section to check if the count is greater than 1

  this sounds good idea can you be more specific please

• Dan.Humphries

  SSChampion

  Points: 12125

  More actions

  May 6, 2010 at 9:24 am

  #1163349

  Something like this

  DECLARE @tableHTML NVARCHAR(MAX);

  SET @tableHTML =

  N'<H1>Hot Item(S) out of Stock <H1>' +

  N'<table border="1">' +

  N'<tr><td>Brand Name</td><td>Category Name</td>' +

  N'<td>Product Name</td><td>Color Name</td>' +

  N'<td>Color Code</td><td>Sizes</td></tr>' +

  CAST ( ( SELECT td = categoryname, '',

  td = brandname, '',

  td = productname, '',

  td = colorname, '',

  td = colorcode, '',

  td = psizename, ''

  FROM B WHERE ISNULL(available_qty,0) = 0 AND ISNULL(available_qty_temp,0) = 0 AND hotflag ='y' AND hotflagemail = 0 FOR XML PATH('tr'), TYPE) AS VARCHAR(MAX)) +

  N'</table>';

  DECLARE @cnt int

  set @cnt= (

  select count* from B WHERE ISNULL(available_qty,0) = 0 AND ISNULL(available_qty_temp,0) = 0 AND hotflag ='y' AND hotflagemail = 0

  IF @cnt>0

  Begin

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ebay',

  @recipients='adventorworks@gmail.com',

  @subject = 'HOT ITEM(s) OUT OF STOCK',

  @body = @tableHTML,

  @body_format = 'HTML' ;

  End

  Dan

  If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

• biren

  Right there with Babe

  Points: 719

  More actions

  May 6, 2010 at 10:16 am

  #1163416

  Dan.Humphries (5/6/2010)

  ---

  Something like this

  DECLARE @tableHTML NVARCHAR(MAX);

  SET @tableHTML =

  N'<H1>Hot Item(S) out of Stock <H1>' +

  N'<table border="1">' +

  N'<tr><td>Brand Name</td><td>Category Name</td>' +

  N'<td>Product Name</td><td>Color Name</td>' +

  N'<td>Color Code</td><td>Sizes</td></tr>' +

  CAST ( ( SELECT td = categoryname, '',

  td = brandname, '',

  td = productname, '',

  td = colorname, '',

  td = colorcode, '',

  td = psizename, ''

  FROM B WHERE ISNULL(available_qty,0) = 0 AND ISNULL(available_qty_temp,0) = 0 AND hotflag ='y' AND hotflagemail = 0 FOR XML PATH('tr'), TYPE) AS VARCHAR(MAX)) +

  N'</table>';

  DECLARE @cnt int

  set @cnt= (

  select count* from B WHERE ISNULL(available_qty,0) = 0 AND ISNULL(available_qty_temp,0) = 0 AND hotflag ='y' AND hotflagemail = 0

  IF @cnt>0

  Begin

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ebay',

  @recipients='adventorworks@gmail.com',

  @subject = 'HOT ITEM(s) OUT OF STOCK',

  @body = @tableHTML,

  @body_format = 'HTML' ;

  End

  Incorrect syntax near the keyword 'from'.

• biren

  Right there with Babe

  Points: 719

  More actions

  May 6, 2010 at 10:19 am

  #1163420

  it should be like this " select count(*) "

  THANKS IT HELPED AND WORKS NOW

• biren

  Right there with Babe

  Points: 719

  More actions

  May 7, 2010 at 5:16 am

  #1163958

  hey any clue on how to add this query in above procedure

  after select query.... it should update saying:

  UPDATE B SET hotemailflag = 1 where ISNULL(available_qty,0) = 0 and ISNULL(available_qty_temp,0) = 0 and hotflag = 'y' and ISNULL(hotemailflag,0) = 0