Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL syntax Question

Posted on 2008-06-24
10
Medium Priority
?
242 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:grantballantyne
  • 5
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21859055
change:
convert(nvarchar(15),@price)    
into:
convert(nvarchar(15), cast(@price as int))    
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21859071
try this:

convert(nvarchar(15),CAST(@price AS INT))
0
 

Author Comment

by:grantballantyne
ID: 21859333
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 60

Expert Comment

by:chapmandew
ID: 21859420
So, when you print out price before the conversion it contains a comma to seperate the thousands?
0
 

Author Comment

by:grantballantyne
ID: 21859577
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21859631
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
 

Author Comment

by:grantballantyne
ID: 21859676
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21859720
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
 

Author Comment

by:grantballantyne
ID: 21859889
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21859927
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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