Para birimi türü ve dönüştürme işlevi
Bu örnek, C# kullanarak bir para birimi kullanıcı tanımlı veri türü tanımlar. Bu kullanıcı tanımlı veri türü, bir miktar ve tutar Kültür para değer olarak işlemek için doğru bir şekilde belirlemek için yardımcı olan bir kültür içinde saklar. Bu örnek ayrıca para birimi kullanıcı tanımlı veri türünün bir örneği döndürür bir para birimi dönüştürme işlevi sağlar. AdventureWorks veritabanı u.s. bir dönüşüm oranı varsa belirtilen kültür ile ilişkili para birimi dolar (usd), dönüştürülen oranı ve istenen Kültür eşleşen bir kültür ile para birimi kullanıcı tanımlı veri türüne dönüştürme işlevi. Aksi takdirde, para birimi kullanıcı tanımlı veri türüne usd cinsinden olmalı özgün miktar ile döndürülen en-uskültür. Örnek de kaydını ve ortak dil çalışma zamanı (clr) yöntemleri ve derlemeler Transact-sql kullanarak kayıt gösterilmiştir.
.gif "Dikkat notu") Dikkat |
|---|
Bu örnekte kullanılan döviz kurları hayali ve gerçek mali hareketler için kullanılmamalıdır. |
Ön Koşullar
Oluşturmak ve bu proje aşağıdaki çalıştırmak için aşağıdaki yazılım yüklenmiş olmalıdır:
SQL Serverveya SQL Serverhızlı. Elde edebilirsiniz SQL Serverhızlı şarj ücretsiz SQL ServerExpress belgeleri ve örnekleri Web sitesi
İs available at AdventureWorks veritabanını SQL Servergeliştiricisi Web sitesi
.net Framework sdk 2.0 veya sonraki sürümünü ya da Microsoft Visual Studio 2005 veya sonraki bir sürümü. Elde edebilirsiniz.net Framework sdk ücretsiz.
Ayrıca, aşağıdaki koşulların karşılanması gerekir:
SQL ServerKullanıyorsanız örneğinin clr tümleştirmesi etkinleştirilmiş olması gerekir.
clr tümleştirme etkinleştirmek için aşağıdaki adımları gerçekleştirin:
clr tümleştirme etkinleştirmek
- Aşağıdaki yürütme Transact-SQLkomutları:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
[!NOT]
clr etkinleştirmek için olmalıdır ALTER SETTINGSörtülü üyeleri tarafından düzenlenen sunucu düzeyi izni sysadminve serveradminsabit sunucu rolü.
AdventureWorks veritabanı yüklenmesi gerekir SQL Serverkullanıyorsanız örneği.
Yönetici değilseniz SQL Serverkullandığınız örnek verdiğiniz bir yönetici olması gerekir CreateAssembly yüklemeyi tamamlamak için izni.
Örnek oluşturma
Oluşturun ve aşağıdaki yönergeleri kullanarak örnek çalıştırın:
Visual Studio'nun açın veya.net Framework buyurmak-e sevketmek.
Gerekirse, örnek bir dizin oluşturun. Bu örnekte, C:\MySample kullanacağız.
C:\MySample içinde oluşturmak Currency.csve C# örnek kod (aşağıda) dosyasına kopyalayın.
Örnek kod komut satırı isteminden çalıştırarak derleyin:
- Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library Currency.cs
Kopya Transact-SQLyükleme kodu olarak kaydedin ve dosyayı içine Install.sqlörnek Rehberi.
Örnek bir dizindeki diğer sonra yüklü olup olmadığını C:\MySample\, dosya düzenleme Install.sqlBu konumu göstermesi için belirtildiği gibi.
Yürüterek saklı yordamı ve derleme dağıtma
- sqlcmd -E -I -i install.sql
Kopya Transact-SQLkomut dosyasını bir dosyaya test ve kaydetme gibi test.sqlörnek Rehberi.
Sınama komut dosyası aşağıdaki komutu yürütün
- sqlcmd -E -I -i test.sql
Kopya Transact-SQLTemizleme komut dosyası olarak kaydedin ve dosyayı içine cleanup.sqlörnek Rehberi.
Aşağıdaki komutu komut dosyasını yürütün
- sqlcmd -E -I -i cleanup.sql
Örnek kod
Bu örnek kod listeleri şunlardır:
C#
using System;
using System.Globalization;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.Sql;
using System.IO;
using System.Data.SqlClient;
/// <summary>
///Defines a class for handing particular amounts of money in a
///particular culture's monetary system. This class is exposed as
///a SQL Server UDT.
///
///Note that we are implementing IComparable to affect comparison behavior
///only within the CLR. This does not affect how SQL Server will compare the
/// the types. How SQL Server will compare the type is determined by the Write
///method on IBinarySerialize.
/// </summary>
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 32)]
public struct Currency : INullable, IComparable, IBinarySerialize
{
const string nullMarker = "\0\0\0\0\0\0\0\0\0\0";
const int cultureNameMaxSize = 10;
private string cultureName;//Who issued the money (en-us, for example)
private CultureInfo culture;//The object which represents cultureName
private decimal currencyValue;//The amount of money
// Public properties for private fields
public CultureInfo Culture
{
get
{
//A culture name is required. If not present the entire object is considered null.
if (cultureName == null) return null;
//If we've got a cached copy of the culture return it.
if (culture != null) return culture;
//Otherwise, set the cache and return the culture for the culture name specified.
culture = CultureInfo.CreateSpecificCulture(cultureName);
return culture;
}
}
// Public property for the private field.
public decimal CurrencyValue
{
get
{
return currencyValue;
}
}
// Constructors for when we have the culture or the name of the culture
public Currency(CultureInfo culture, decimal currencyValue)
{
if (culture == null) throw new ArgumentNullException("culture");
this.cultureName = culture.Name;
this.culture = culture;
this.currencyValue = currencyValue;
}
public Currency(string cultureName, decimal currencyValue)
{
this.cultureName = cultureName;
this.culture = null;
this.currencyValue = currencyValue;
}
//Return the string representation for the currency, including the currency symbol.
[SqlMethod(IsDeterministic = true,
IsPrecise = true, DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None)]
public override string ToString()
{
if (this.Culture == null) return "null";
return String.Format(this.Culture, "{0:c}", currencyValue);
}
//The entire value of the currency is considered null if the culture name is null
public bool IsNull
{
get
{
return cultureName == null;
}
}
//The no-argument constructor makes a null currency.
public static Currency Null
{
get
{
Currency h = new Currency((String)null, 0);
return h;
}
}
//Be sure to set the current UI culture before using this method! Even better, provide the culture
//specifically (for the method after this one).
[SqlMethod(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
public static Currency Parse(SqlString sqlString)
{
return ParseWithCulture(sqlString, CultureInfo.CurrentUICulture);
}
public static Currency ParseWithCulture(SqlString sqlString, CultureInfo culture)
{
if (sqlString.IsNull
|| (string.Compare(sqlString.Value, "null", true, CultureInfo.CurrentUICulture) == 0))
return Currency.Null;
int digitPos = -1;
string stringValue = sqlString.Value;
while (digitPos < stringValue.Length
&& !Char.IsDigit(stringValue, ++digitPos))
{
}
if (digitPos < stringValue.Length)
return new Currency(culture, decimal.Parse(
stringValue.Substring(digitPos), culture));
return Currency.Null;
}
public override int GetHashCode()
{
if (this.IsNull)
return 0;
return this.ToString().GetHashCode();
}
//Note: This only affects the behavior of CLR, not SQL Server. Comparisions
//for SQL Server will be determined by the Write method below.
public int CompareTo(object obj)
{
if (obj == null)
return 1; //by definition
if (obj == null || !(obj is Currency))
throw new ArgumentException(
"the argument to compare is not a Currency");
Currency c = (Currency)obj;
if (this.IsNull)
{
if (c.IsNull)
return 0;
return -1;
}
if (c.IsNull)
return 1;
string thisCultureName = this.Culture.Name;
string otherCultureName = c.Culture.Name;
if (!thisCultureName.Equals(otherCultureName))
return thisCultureName.CompareTo(otherCultureName);
return this.CurrencyValue.CompareTo(c.CurrencyValue);
}
// IBinarySerialize methods
// The binary layout is as follow:
// Bytes 0 - 19:Culture name, padded to the right with null characters, UTF-16 encoded
// Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
if (w == null) throw new ArgumentNullException("w");
if (this.IsNull)
{
w.Write(nullMarker);
w.Write((decimal)0);
return;
}
if (cultureName.Length > cultureNameMaxSize)
{
throw new ApplicationException(string.Format(
CultureInfo.InvariantCulture,
"{0} is an invalid culture name for currency as it is too long.",
cultureNameMaxSize));
}
String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
for (int i = 0; i < cultureNameMaxSize; i++)
{
w.Write(paddedName[i]);
}
// Normalize decimal value to two places
currencyValue = Decimal.Floor(currencyValue * 100) / 100;
w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
char[] name = r.ReadChars(cultureNameMaxSize);
int stringEnd = Array.IndexOf(name, '\0');
if (stringEnd == 0)
{
cultureName = null;
return;
}
cultureName = new String(name, 0, stringEnd);
currencyValue = r.ReadDecimal();
}
}
/// <summary>
/// This class is used to compute the value of US money a given region.
/// </summary>
public sealed class CurrencyConverter
{
// Classes with only static members should not be instantiable
private CurrencyConverter()
{
}
private static readonly CultureInfo USCulture = CultureInfo.CreateSpecificCulture("en-us");
/// <summary>
///Computes the value of a certain amount of money in the USA in a different region.
/// </summary>
/// <param name="fromAmount">The quantity of money</param>
/// <param name="toCultureName">A culture which is a member of the region of interest</param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static Currency ConvertCurrency(SqlMoney fromAmount, SqlString toCultureName, SqlDateTime when)
{
CultureInfo toCulture = CultureInfo.CreateSpecificCulture(toCultureName.Value);
if (toCulture.Equals(USCulture))
{
Currency c = new Currency(USCulture, (decimal)fromAmount);
return c;
}
String toCurrencyCode = new RegionInfo(toCulture.LCID).ISOCurrencySymbol;
// Find the rate closest to the specified date
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "usp_LookupConversionRate";
SqlParameter onDateParameter
= new SqlParameter("@OnDate", SqlDbType.DateTime);
onDateParameter.Value = when;
command.Parameters.Add(onDateParameter);
SqlParameter toCurrencyCodeParameter
= new SqlParameter("@ToCurrencyCode", SqlDbType.NChar, 3);
toCurrencyCodeParameter.Value = toCurrencyCode;
command.Parameters.Add(toCurrencyCodeParameter);
SqlParameter resultParameter
= new SqlParameter("@Result", SqlDbType.Decimal);
resultParameter.Precision = 10;
resultParameter.Scale = 4;
resultParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(resultParameter);
conn.Open();
command.ExecuteNonQuery();
decimal conversionFactor;
if (resultParameter.Value is decimal)
{
conversionFactor = (decimal)(resultParameter.Value);
}
else
{
conversionFactor = 1.0M;
toCulture = USCulture;
}
return new Currency(toCulture, ((decimal)fromAmount * conversionFactor));
}
}
}
using System;
using System.Globalization;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.Sql;
using System.IO;
using System.Data.SqlClient;
/// <summary>
///Defines a class for handing particular amounts of money in a
///particular culture's monetary system. This class is exposed as
///a SQL Server UDT.
///
///Note that we are implementing IComparable to affect comparison behavior
///only within the CLR. This does not affect how SQL Server will compare the
/// the types. How SQL Server will compare the type is determined by the Write
///method on IBinarySerialize.
/// </summary>
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 32)]
public struct Currency : INullable, IComparable, IBinarySerialize
{
const string nullMarker = "\0\0\0\0\0\0\0\0\0\0";
const int cultureNameMaxSize = 10;
private string cultureName;//Who issued the money (en-us, for example)
private CultureInfo culture;//The object which represents cultureName
private decimal currencyValue;//The amount of money
// Public properties for private fields
public CultureInfo Culture
{
get
{
//A culture name is required. If not present the entire object is considered null.
if (cultureName == null) return null;
//If we've got a cached copy of the culture return it.
if (culture != null) return culture;
//Otherwise, set the cache and return the culture for the culture name specified.
culture = CultureInfo.CreateSpecificCulture(cultureName);
return culture;
}
}
// Public property for the private field.
public decimal CurrencyValue
{
get
{
return currencyValue;
}
}
// Constructors for when we have the culture or the name of the culture
public Currency(CultureInfo culture, decimal currencyValue)
{
if (culture == null) throw new ArgumentNullException("culture");
this.cultureName = culture.Name;
this.culture = culture;
this.currencyValue = currencyValue;
}
public Currency(string cultureName, decimal currencyValue)
{
this.cultureName = cultureName;
this.culture = null;
this.currencyValue = currencyValue;
}
//Return the string representation for the currency, including the currency symbol.
[SqlMethod(IsDeterministic = true,
IsPrecise = true, DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None)]
public override string ToString()
{
if (this.Culture == null) return "null";
return String.Format(this.Culture, "{0:c}", currencyValue);
}
//The entire value of the currency is considered null if the culture name is null
public bool IsNull
{
get
{
return cultureName == null;
}
}
//The no-argument constructor makes a null currency.
public static Currency Null
{
get
{
Currency h = new Currency((String)null, 0);
return h;
}
}
//Be sure to set the current UI culture before using this method! Even better, provide the culture
//specifically (for the method after this one).
[SqlMethod(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
public static Currency Parse(SqlString sqlString)
{
return ParseWithCulture(sqlString, CultureInfo.CurrentUICulture);
}
public static Currency ParseWithCulture(SqlString sqlString, CultureInfo culture)
{
if (sqlString.IsNull
|| (string.Compare(sqlString.Value, "null", true, CultureInfo.CurrentUICulture) == 0))
return Currency.Null;
int digitPos = -1;
string stringValue = sqlString.Value;
while (digitPos < stringValue.Length
&& !Char.IsDigit(stringValue, ++digitPos))
{
}
if (digitPos < stringValue.Length)
return new Currency(culture, decimal.Parse(
stringValue.Substring(digitPos), culture));
return Currency.Null;
}
public override int GetHashCode()
{
if (this.IsNull)
return 0;
return this.ToString().GetHashCode();
}
//Note: This only affects the behavior of CLR, not SQL Server. Comparisions
//for SQL Server will be determined by the Write method below.
public int CompareTo(object obj)
{
if (obj == null)
return 1; //by definition
if (obj == null || !(obj is Currency))
throw new ArgumentException(
"the argument to compare is not a Currency");
Currency c = (Currency)obj;
if (this.IsNull)
{
if (c.IsNull)
return 0;
return -1;
}
if (c.IsNull)
return 1;
string thisCultureName = this.Culture.Name;
string otherCultureName = c.Culture.Name;
if (!thisCultureName.Equals(otherCultureName))
return thisCultureName.CompareTo(otherCultureName);
return this.CurrencyValue.CompareTo(c.CurrencyValue);
}
// IBinarySerialize methods
// The binary layout is as follow:
// Bytes 0 - 19:Culture name, padded to the right with null characters, UTF-16 encoded
// Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
if (w == null) throw new ArgumentNullException("w");
if (this.IsNull)
{
w.Write(nullMarker);
w.Write((decimal)0);
return;
}
if (cultureName.Length > cultureNameMaxSize)
{
throw new ApplicationException(string.Format(
CultureInfo.InvariantCulture,
"{0} is an invalid culture name for currency as it is too long.",
cultureNameMaxSize));
}
String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
for (int i = 0; i < cultureNameMaxSize; i++)
{
w.Write(paddedName[i]);
}
// Normalize decimal value to two places
currencyValue = Decimal.Floor(currencyValue * 100) / 100;
w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
char[] name = r.ReadChars(cultureNameMaxSize);
int stringEnd = Array.IndexOf(name, '\0');
if (stringEnd == 0)
{
cultureName = null;
return;
}
cultureName = new String(name, 0, stringEnd);
currencyValue = r.ReadDecimal();
}
}
/// <summary>
/// This class is used to compute the value of US money a given region.
/// </summary>
public sealed class CurrencyConverter
{
// Classes with only static members should not be instantiable
private CurrencyConverter()
{
}
private static readonly CultureInfo USCulture = CultureInfo.CreateSpecificCulture("en-us");
/// <summary>
///Computes the value of a certain amount of money in the USA in a different region.
/// </summary>
/// <param name="fromAmount">The quantity of money</param>
/// <param name="toCultureName">A culture which is a member of the region of interest</param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static Currency ConvertCurrency(SqlMoney fromAmount, SqlString toCultureName, SqlDateTime when)
{
CultureInfo toCulture = CultureInfo.CreateSpecificCulture(toCultureName.Value);
if (toCulture.Equals(USCulture))
{
Currency c = new Currency(USCulture, (decimal)fromAmount);
return c;
}
String toCurrencyCode = new RegionInfo(toCulture.LCID).ISOCurrencySymbol;
// Find the rate closest to the specified date
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "usp_LookupConversionRate";
SqlParameter onDateParameter
= new SqlParameter("@OnDate", SqlDbType.DateTime);
onDateParameter.Value = when;
command.Parameters.Add(onDateParameter);
SqlParameter toCurrencyCodeParameter
= new SqlParameter("@ToCurrencyCode", SqlDbType.NChar, 3);
toCurrencyCodeParameter.Value = toCurrencyCode;
command.Parameters.Add(toCurrencyCodeParameter);
SqlParameter resultParameter
= new SqlParameter("@Result", SqlDbType.Decimal);
resultParameter.Precision = 10;
resultParameter.Scale = 4;
resultParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(resultParameter);
conn.Open();
command.ExecuteNonQuery();
decimal conversionFactor;
if (resultParameter.Value is decimal)
{
conversionFactor = (decimal)(resultParameter.Value);
}
else
{
conversionFactor = 1.0M;
toCulture = USCulture;
}
return new Currency(toCulture, ((decimal)fromAmount * conversionFactor));
}
}
}
Bu Transact-SQLyükleme komut dosyasını (Install.sql), veritabanında saklı yordam oluşturur ve derleme dağıtır.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO
IF EXISTS (SELECT * FROM sys.types WHERE [name] = N'Currency')
DROP TYPE Currency;
GO
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Currency')
DROP ASSEMBLY Currency;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ConvertCurrency') AND ([type] = 'FS'))
DROP FUNCTION ConvertCurrency;
GO
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
DECLARE @SamplesPath nvarchar(1024)
set @SamplesPath = 'C:\MySample\'
CREATE ASSEMBLY Currency
FROM @SamplesPath + 'Currency.dll'
with permission_set = safe;
USE AdventureWorks
GO
CREATE TYPE Currency EXTERNAL NAME [Currency].[Currency];
GO
CREATE FUNCTION ConvertCurrency
(
@fromAmount AS money,
@toCultureName AS nvarchar(10),
@when as DateTime
)
RETURNS Currency
AS EXTERNAL NAME [Currency].[CurrencyConverter].ConvertCurrency;
GO
CREATE PROCEDURE usp_LookupConversionRate
(
@OnDate datetime,
@ToCurrencyCode nchar(3),
@Result decimal(10,4) OUTPUT
)
AS
BEGIN
--It is not permitted to perform certain side-effects in functions, and
--SET NOCOUNT is one of them. Since this sproc is called from
--the ConvertCurrency CLR UDF, we must not do that side-effect or
--there will be an error at runtime.
--SET NOCOUNT ON
SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate
WHERE CurrencyRateDate <= @OnDate AND FromCurrencyCode = N'USD'
AND ToCurrencyCode = @ToCurrencyCode
ORDER BY CurrencyRateDate DESC);
IF (@Result IS NULL)
SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate
WHERE CurrencyRateDate > @OnDate AND FromCurrencyCode = N'USD'
AND ToCurrencyCode = @ToCurrencyCode
ORDER BY CurrencyRateDate ASC);
END;
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO
IF EXISTS (SELECT * FROM sys.types WHERE [name] = N'Currency')
DROP TYPE Currency;
GO
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Currency')
DROP ASSEMBLY Currency;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ConvertCurrency') AND ([type] = 'FS'))
DROP FUNCTION ConvertCurrency;
GO
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
DECLARE @SamplesPath nvarchar(1024)
set @SamplesPath = 'C:\MySample\'
CREATE ASSEMBLY Currency
FROM @SamplesPath + 'Currency.dll'
with permission_set = safe;
USE AdventureWorks
GO
CREATE TYPE Currency EXTERNAL NAME [Currency].[Currency];
GO
CREATE FUNCTION ConvertCurrency
(
@fromAmount AS money,
@toCultureName AS nvarchar(10),
@when as DateTime
)
RETURNS Currency
AS EXTERNAL NAME [Currency].[CurrencyConverter].ConvertCurrency;
GO
CREATE PROCEDURE usp_LookupConversionRate
(
@OnDate datetime,
@ToCurrencyCode nchar(3),
@Result decimal(10,4) OUTPUT
)
AS
BEGIN
--It is not permitted to perform certain side-effects in functions, and
--SET NOCOUNT is one of them. Since this sproc is called from
--the ConvertCurrency CLR UDF, we must not do that side-effect or
--there will be an error at runtime.
--SET NOCOUNT ON
SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate
WHERE CurrencyRateDate <= @OnDate AND FromCurrencyCode = N'USD'
AND ToCurrencyCode = @ToCurrencyCode
ORDER BY CurrencyRateDate DESC);
IF (@Result IS NULL)
SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate
WHERE CurrencyRateDate > @OnDate AND FromCurrencyCode = N'USD'
AND ToCurrencyCode = @ToCurrencyCode
ORDER BY CurrencyRateDate ASC);
END;
Bu test.sql, hangi testleri örnek işlevleri yürüterek.
use AdventureWorks
GO
DECLARE @TwoBitsEuro Currency;
SELECT @TwoBitsEuro = dbo.ConvertCurrency(CAST('.25' as money), 'FR-FR', GetDate());
PRINT '$0.25 in USD is equivalent to ' + @TwoBitsEuro.ToString();
use AdventureWorks
GO
DECLARE @TwoBitsEuro Currency;
SELECT @TwoBitsEuro = dbo.ConvertCurrency(CAST('.25' as money), 'FR-FR', GetDate());
PRINT '$0.25 in USD is equivalent to ' + @TwoBitsEuro.ToString();
Aşağıdaki Transact-SQLKurul, türü ve işlevleri veritabanından kaldırır.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO
Ayrıca bkz.
Kavramlar
Kullanım senaryoları ve ortak dil çalışma zamanı (clr) tümleştirme örnekleri