Solved

SQL syntax Question

Posted on 2008-06-24
10
203 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]
Comment Utility
change:
convert(nvarchar(15),@price)    
into:
convert(nvarchar(15), cast(@price as int))    
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
try this:

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

Author Comment

by:grantballantyne
Comment Utility
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
Comment Utility
So, when you print out price before the conversion it contains a comma to seperate the thousands?
0
 

Author Comment

by:grantballantyne
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

10 Experts available now in Live!

Get 1:1 Help Now