Solved

SQL syntax Question

Posted on 2008-06-24
10
218 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query 7 49
Need multiple Group By's 8 55
Trying to understand why my Index is so large 12 52
How do i delete the last node in an xml in T-SQL 7 26
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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