Solved

SQL syntax Question

Posted on 2008-06-24
10
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

707 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