Solved

SQL syntax Question

Posted on 2008-06-24
10
204 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 142

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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 to summarize items per month 5 53
Script to randomly create characters in MS SQL 6 32
Unable to save view in SSMS 21 59
SQL Help - SELECT Statement 6 40
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now