SQL syntax Question

Dear Experts

I have the syntax:   convert(nvarchar(15),@price)    as part of a job that runs and emails users.  

I wondered if anyone can offer advise to have the above displayed as a money value without any decimals.

(Please see the full code inthe snippet if this helps)

Thanks

Grant
Declare @MyRecipients nvarchar (255)
Declare @firstname nvarchar (50)
Declare @Subject nvarchar (50)
Declare @ref int
Declare @urlref nvarchar (255)
Declare @postcode nvarchar (50)
Declare @minprice money
Declare @maxprice money
Declare @area char (20)
Declare @minbedrooms char
Declare @price money
Declare @pricetype char (20)
Declare @paddress1 char (60) 
Declare @paddress2 char (60) 
Declare @ptown char (60) 
Declare @bedrooms char
Declare @image_string nvarchar (4000)
Declare @dateadded nvarchar (50) 
Declare MyCursor Cursor For 
Select mClientEmail,mClientName,mRef,pPostCode,mMinprice, mMaxprice, pArea, mMinBeds,pPrice,pPriceType,pAddress1, pAddress2, pTown, pBedRooms, mDateAdded + 30 as dateadded
from todays_property_matches
 
Open MyCursor
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown, @bedrooms, @dateadded
While @@Fetch_Status = 0
BEGIN
 
 
 
 
set @urlref = replace('http://www.bspc.co.uk/propertydetails.asp?pRef='  + rtrim(convert(nvarchar(4),@ref)) + '&' + 'pPostCode=' + rtrim(@postcode) + '' , ' ','%20')
 
 
SET @image_string =
    N'<table width="700" border="0" cellpadding="10" cellspacing="0" bgcolor="#E3EFFF">
  <tr>
    <td height="70" align="center" bgcolor="#E3EFFF"><a href="http://www.cullenkilshaw.com/"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\header.gif" alt="Cullen Kilshaw" width="675" height="70" border="0" /></a></td>
  </tr>
<br>
 
  <tr>
	<td valign="middle"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
	Dear ' + @firstname + ',</font></td> 
	</tr>
 
<tr>
    <td valign="middle"  ><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
      A property which matches your criteria has been added to our property database and website. The property details are given below, click on the link provided to view full   details.<br />
      <br />
    </font></td>
  </tr>
  <tr>
    <td><table width="100%" cellpadding="0" cellspacing="5" bgcolor="#E3EFFF">
      <tr>
 
        <td width="100%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + ' £' + convert(nvarchar(15),@price) +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
          <br>' + rtrim(convert(nvarchar(60), @paddress1 )) + ' ' + rtrim(convert(nvarchar(60), @paddress2 )) + '
 
          <br>'
          + rtrim(convert(nvarchar(60), @ptown))+ '<br>' 
          + rtrim(convert(nvarchar(60), @postcode))+ ' <br>
          <BR>
            Bedrooms: ' +  rtrim(convert(nvarchar(10), @bedrooms))+ '</font><br>
            <br>
            <a href="' + @urlref + '" <font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">View property details on www.bspc.co.uk</font></a><font color="#003366"><br>
            </font>
 
          
                  </td>
      </tr>
    </table>    </td>
  </tr>
  
  <tr>
    <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br />
      Please note that your registration to receive new properties by email will expire on <strong>' + @dateadded + '</strong>. <br />
    Login to <a href="http://www.bspc.co.uk/"><font color="#003366">www.bspc.co.uk</font></a> to renew your registration. The  information contained in this email is time sensitive and may have changed between publish, delivery and reading.<br />
    <br />
    </font></td>
  </tr>
  <tr>
    <td height="1" bgcolor="#003366"><img src="images/spacer.gif" alt="spacer" width="1" height="1" /></td>
  </tr>
  <tr>
    <td height="40" align="center"><strong><font size="2" face="verdana, sans-serif">      The Borders Solicitors Property Centres Property Guide and <a href="http://www.bspc.co.uk/"><font color="#003366">www.bspc.co.uk</font></a> <br />
      are exclusive to clients 
      
      selling   their property with a Solicitor</font></strong></td>
  </tr>
</table>' 
 
 
Print @MyRecipients 
 
EXEC msdb.dbo.sp_send_dbmail
@recipients=@MyRecipients,
@body=@image_string,
@body_format = 'HTML',
@sensitivity ='Personal', 
@importance ='High', 
@subject ='Cullen Kilshaw Property Match',
@profile_name ='Easi Property';
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown,@bedrooms,@dateadded
End
Close MyCursor
Deallocate MyCursor

Open in new window

grantballantyneAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
you mean to take this code and make it a stored procedure?
create procedure myproc
as
Declare @MyRecipients nvarchar (255)
Declare @firstname nvarchar (50)
Declare @Subject nvarchar (50)
Declare @ref int
Declare @urlref nvarchar (255)
Declare @postcode nvarchar (50)
Declare @minprice money
Declare @maxprice money
Declare @area char (20)
Declare @minbedrooms char
Declare @price money
Declare @pricetype char (20)
Declare @paddress1 char (60) 
Declare @paddress2 char (60) 
Declare @ptown char (60) 
Declare @bedrooms char
Declare @image_string nvarchar (4000)
Declare @dateadded nvarchar (50) 
Declare MyCursor Cursor For 
Select mClientEmail,mClientName,pRef,pPostCode,mMinprice, mMaxprice, pArea, mMinBeds,pPrice,pPriceType,pAddress1, pAddress2, pTown, pBedRooms, mDateAdded + 30 as dateadded
from todays_property_matches
 
 
 
 
Open MyCursor
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown, @bedrooms, @dateadded
While @@Fetch_Status = 0
BEGIN
 
 
 
 
 
set @urlref = replace('http://dev.cullenkilshaw.com/site/propertydetails.asp?pRef='  + rtrim(convert(nvarchar(4),@ref)) + '&' + 'pPostCode=' + rtrim(@postcode) + '' , ' ','%20')
 
 
SET @image_string =
    N'<table width="700" border="0" cellpadding="10" cellspacing="0" bgcolor="#E3EFFF">
  <tr>
    <td height="70" align="center" bgcolor="#E3EFFF"><a href="http://www.cullenkilshaw.com/"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\header.gif" alt="Cullen Kilshaw" width="700" height="70" border="0" /></a></td>
  </tr>
<br>
 
  <tr>
	<td valign="middle"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
	Dear ' + @firstname + ',</font></td> 
	</tr>
 
<tr>
    <td valign="middle"  ><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
      Im pleased to advise that a property which matches your criteria has been added to our property database and website. The property details are given below, click on the link provided to view full   details.<br />
      <br />
    </font></td>
  </tr>
  <tr>
    <td><table width="100%" cellpadding="0" cellspacing="5" bgcolor="#E3EFFF">
      <tr>
 
<td width="100%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + ' £' + dbo.pPrice_FormatInteger(@price)   +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
          <br>' + rtrim(convert(nvarchar(60), @paddress1 )) + ' ' + rtrim(convert(nvarchar(60), @paddress2 )) + '
 
          <br>'
          + rtrim(convert(nvarchar(60), @ptown))+ '<br>' 
          + rtrim(convert(nvarchar(60), @postcode))+ ' <br>
          <BR>
            Bedrooms: ' +  rtrim(convert(nvarchar(10), @bedrooms))+ '</font><br>
            <br>
            <a href="' + @urlref + '" <font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">View full property details on  www.cullenkilshaw.com</font></a></font><font color="#003366"><br>
            
 
          
                  </td>
      </tr>
    </table>    </td>
  </tr>
  
  <tr>
    <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br />
      Please note that your registration to receive new properties by email will expire on <strong>' + convert(nvarchar(11), cast(@dateadded as datetime))  + '</strong>. <br />
    If you wish to extend your registration for email matching notification please contact one of our property branches.<br />
    <br />
    </font></td>
  </tr>
  <tr>
    <td height="1" bgcolor="#E3EFFF"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\ckfill.gif" alt="spacer" width="700" height="20" /></td>
  </tr>
  <tr>
   
  </tr>
</table>' 
 
 
Print @MyRecipients 
 
EXEC msdb.dbo.sp_send_dbmail
@recipients=@MyRecipients,
@body=@image_string,
@body_format = 'HTML',
@sensitivity ='Personal', 
@importance ='High', 
@subject ='Cullen Kilshaw Property Match',
@profile_name ='Easi Property';
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown,@bedrooms,@dateadded
End
Close MyCursor
Deallocate MyCursor

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:
convert(nvarchar(15),@price)    
into:
convert(nvarchar(15), cast(@price as int))    
0
 
chapmandewCommented:
try this:

convert(nvarchar(15),CAST(@price AS INT))
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
grantballantyneAuthor Commented:
Thanks to both of you for this.  It has worked in that it has removed the decimal places - however I have lost the comma to separate thousands.

Eg the email now has  £500000 - Is there anyway to amend the syntax to retain the comma seperator to read £500,000

Thanks again

Grant
0
 
chapmandewCommented:
So, when you print out price before the conversion it contains a comma to seperate the thousands?
0
 
grantballantyneAuthor Commented:
Hi

Using the suggestion - convert(nvarchar(15),CAST(@price AS INT))  there is no comma to separate the thousands but I would like to format with the comma.

Grant
0
 
chapmandewCommented:
this function should do it.  create it and wrap it around your statement:

ALTER  Function dbo.fn_FormatInteger (
 @nNumber numeric
)
RETURNS varchar(100)
AS
 
BEGIN
  DECLARE @strOut varchar(100)
 
  SET @strOut =
    SUBSTRING(
      CONVERT(
        varchar,
        CAST(@nNumber AS money),
        1
      ),
      1,
      LEN(
        CONVERT(
          varchar,
          CAST(@nNumber AS money),
          1
        )
      ) - 3
    )
 
  RETURN(@strOut)
END
0
 
grantballantyneAuthor Commented:
Thanks - Can you advise how I fit your suggestion properly into the code below.

Im a novice at this.

Thanks

Grant

 
Declare @MyRecipients nvarchar (255)
Declare @firstname nvarchar (50)
Declare @Subject nvarchar (50)
Declare @ref int
Declare @urlref nvarchar (255)
Declare @postcode nvarchar (50)
Declare @minprice money
Declare @maxprice money
Declare @area char (20)
Declare @minbedrooms char
Declare @price money
Declare @pricetype char (20)
Declare @paddress1 char (60) 
Declare @paddress2 char (60) 
Declare @ptown char (60) 
Declare @bedrooms char
Declare @image_string nvarchar (4000)
Declare @dateadded nvarchar (50) 
Declare MyCursor Cursor For 
Select mClientEmail,mClientName,mRef,pPostCode,mMinprice, mMaxprice, pArea, mMinBeds,pPrice,pPriceType,pAddress1, pAddress2, pTown, pBedRooms, mDateAdded + 30 as dateadded
from todays_property_matches
 
Open MyCursor
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown, @bedrooms, @dateadded
While @@Fetch_Status = 0
BEGIN
 
 
 
 
set @urlref = replace('http://www.cullenkilshaw.com/site/propertydetails.asp?pRef='  + rtrim(convert(nvarchar(4),@ref)) + '&' + 'pPostCode=' + rtrim(@postcode) + '' , ' ','%20')
 
 
SET @image_string =
    N'<table width="700" border="0" cellpadding="10" cellspacing="0" bgcolor="#E3EFFF">
  <tr>
    <td height="70" align="center" bgcolor="#E3EFFF"><a href="http://www.cullenkilshaw.com/"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\header.gif" alt="Cullen Kilshaw" width="700" height="70" border="0" /></a></td>
  </tr>
<br>
 
  <tr>
	<td valign="middle"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
	Dear ' + @firstname + ',</font></td> 
	</tr>
 
<tr>
    <td valign="middle"  ><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
      A property which matches your criteria has been added to our property database and website. The property details are given below, click on the link provided to view full   details.<br />
      <br />
    </font></td>
  </tr>
  <tr>
    <td><table width="100%" cellpadding="0" cellspacing="5" bgcolor="#E3EFFF">
      <tr>
 
        <td width="100%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + ' £' + convert(nvarchar(15), cast(@price as int))   +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
          <br>' + rtrim(convert(nvarchar(60), @paddress1 )) + ' ' + rtrim(convert(nvarchar(60), @paddress2 )) + '
 
          <br>'
          + rtrim(convert(nvarchar(60), @ptown))+ '<br>' 
          + rtrim(convert(nvarchar(60), @postcode))+ ' <br>
          <BR>
            Bedrooms: ' +  rtrim(convert(nvarchar(10), @bedrooms))+ '</font><br>
            <br>
            <a href="' + @urlref + '" <font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">View full property details on  www.cullenkilshaw.com</font></a></font><font color="#003366"><br>
            
 
          
                  </td>
      </tr>
    </table>    </td>
  </tr>
  
  <tr>
    <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br />
      Please note that your registration to receive new properties by email will expire on <strong>' + convert(nvarchar(11), cast(@dateadded as datetime))  + '</strong>. <br />
    If you wish to extend your registration for email matching notification please contact one of our property branches.<br />
    <br />
    </font></td>
  </tr>
  <tr>
    <td height="1" bgcolor="#E3EFFF"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\ckfill.gif" alt="spacer" width="700" height="20" /></td>
  </tr>
  <tr>
   
  </tr>
</table>' 
 
 
Print @MyRecipients 
 
EXEC msdb.dbo.sp_send_dbmail
@recipients=@MyRecipients,
@body=@image_string,
@body_format = 'HTML',
@sensitivity ='Personal', 
@importance ='High', 
@subject ='Cullen Kilshaw Property Match',
@profile_name ='Easi Property';
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown,@bedrooms,@dateadded
End
Close MyCursor
Deallocate MyCursor

Open in new window

0
 
chapmandewCommented:
create the proc i posted above (swap out the ALTER statement w/ the CREATE keyword).  Then swap out this line


        <td width="100%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + ' £' + convert(nvarchar(15), cast(@price as int))   +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>

with this one (which uses the function)

        <td width="100%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + ' £' + dbo.fn_FormatInteger(@price)   +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>


0
 
grantballantyneAuthor Commented:
OK thanks

I have added in the amended HTML (as per below).  Sorry about this but can you advise where I place the proc syntax?

Thanks

Grant

 
Declare @MyRecipients nvarchar (255)
Declare @firstname nvarchar (50)
Declare @Subject nvarchar (50)
Declare @ref int
Declare @urlref nvarchar (255)
Declare @postcode nvarchar (50)
Declare @minprice money
Declare @maxprice money
Declare @area char (20)
Declare @minbedrooms char
Declare @price money
Declare @pricetype char (20)
Declare @paddress1 char (60) 
Declare @paddress2 char (60) 
Declare @ptown char (60) 
Declare @bedrooms char
Declare @image_string nvarchar (4000)
Declare @dateadded nvarchar (50) 
Declare MyCursor Cursor For 
Select mClientEmail,mClientName,pRef,pPostCode,mMinprice, mMaxprice, pArea, mMinBeds,pPrice,pPriceType,pAddress1, pAddress2, pTown, pBedRooms, mDateAdded + 30 as dateadded
from todays_property_matches
 
 
 
 
Open MyCursor
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown, @bedrooms, @dateadded
While @@Fetch_Status = 0
BEGIN
 
 
 
 
 
set @urlref = replace('http://dev.cullenkilshaw.com/site/propertydetails.asp?pRef='  + rtrim(convert(nvarchar(4),@ref)) + '&' + 'pPostCode=' + rtrim(@postcode) + '' , ' ','%20')
 
 
SET @image_string =
    N'<table width="700" border="0" cellpadding="10" cellspacing="0" bgcolor="#E3EFFF">
  <tr>
    <td height="70" align="center" bgcolor="#E3EFFF"><a href="http://www.cullenkilshaw.com/"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\header.gif" alt="Cullen Kilshaw" width="700" height="70" border="0" /></a></td>
  </tr>
<br>
 
  <tr>
	<td valign="middle"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
	Dear ' + @firstname + ',</font></td> 
	</tr>
 
<tr>
    <td valign="middle"  ><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
      Im pleased to advise that a property which matches your criteria has been added to our property database and website. The property details are given below, click on the link provided to view full   details.<br />
      <br />
    </font></td>
  </tr>
  <tr>
    <td><table width="100%" cellpadding="0" cellspacing="5" bgcolor="#E3EFFF">
      <tr>
 
<td width="100%" align="left" valign="top"><strong><font color="#003366" size="3" face="Verdana, Arial, Helvetica, sans-serif">'  + @pricetype + ' £' + dbo.pPrice_FormatInteger(@price)   +  '</font></strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br>
          <br>' + rtrim(convert(nvarchar(60), @paddress1 )) + ' ' + rtrim(convert(nvarchar(60), @paddress2 )) + '
 
          <br>'
          + rtrim(convert(nvarchar(60), @ptown))+ '<br>' 
          + rtrim(convert(nvarchar(60), @postcode))+ ' <br>
          <BR>
            Bedrooms: ' +  rtrim(convert(nvarchar(10), @bedrooms))+ '</font><br>
            <br>
            <a href="' + @urlref + '" <font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">View full property details on  www.cullenkilshaw.com</font></a></font><font color="#003366"><br>
            
 
          
                  </td>
      </tr>
    </table>    </td>
  </tr>
  
  <tr>
    <td><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><br />
      Please note that your registration to receive new properties by email will expire on <strong>' + convert(nvarchar(11), cast(@dateadded as datetime))  + '</strong>. <br />
    If you wish to extend your registration for email matching notification please contact one of our property branches.<br />
    <br />
    </font></td>
  </tr>
  <tr>
    <td height="1" bgcolor="#E3EFFF"><img src="http://dev.cullenkilshaw.com\easi\graphics\matching\ckfill.gif" alt="spacer" width="700" height="20" /></td>
  </tr>
  <tr>
   
  </tr>
</table>' 
 
 
Print @MyRecipients 
 
EXEC msdb.dbo.sp_send_dbmail
@recipients=@MyRecipients,
@body=@image_string,
@body_format = 'HTML',
@sensitivity ='Personal', 
@importance ='High', 
@subject ='Cullen Kilshaw Property Match',
@profile_name ='Easi Property';
 
 
Fetch Next From MyCursor Into @MyRecipients,@firstname,@ref,@postcode,@minprice,@maxprice,@area,@minbedrooms,@price,@pricetype,@paddress1,@paddress2,@ptown,@bedrooms,@dateadded
End
Close MyCursor
Deallocate MyCursor

Open in new window

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.