创建获取状态代码功能

Question

我真的是创建功能的新手，似乎找不到与在线发布的任何类似的东西。我不确定为什么当我运行这个时，我要返回空白？我正在使用...语句的选择DBO.UDF_STATECODE([state](来调用它。

CREATE FUNCTION [dbo].[UDF_StateCode] (@State NVARCHAR)
RETURNS NVARCHAR
AS
BEGIN
DECLARE @StateCode NVARCHAR
SET @StateCode =
CASE 
WHEN @State = 'ALASKA' THEN 'AK'
WHEN @State = 'ALABAMA' THEN 'AL'
WHEN @State = 'ARKANSAS' THEN 'AR'
WHEN @State = 'AMERICAN SAMOA' THEN 'AS'
WHEN @State = 'ARIZONA' THEN 'AZ'
WHEN @State = 'CALIFORNIA' THEN 'CA'
WHEN @State = 'COLORADO' THEN 'CO'
WHEN @State = 'CONNECTICUT' THEN 'CT'
WHEN @State = 'DISTRICT OF COLUMBIA' THEN 'DC'
WHEN @State = 'DELAWARE'THEN 'DE' 
WHEN @State = 'FLORIDA' THEN 'FL'
WHEN @State = 'FEDERATED STATES OF MICRONESIA' THEN 'FM'
WHEN @State = 'GEORGIA' THEN 'GA'
WHEN @State = 'GUAM' THEN 'GU'
WHEN @State = 'HAWAII' THEN 'HI' 
WHEN @State = 'IOWA' THEN 'IA'
WHEN @State = 'IDAHO' THEN 'ID'
WHEN @State = 'ILLINOIS' THEN 'IL'
WHEN @State = 'INDIANA' THEN 'IN'
WHEN @State = 'KANSAS' THEN 'KS' 
WHEN @State = 'KENTUCKY' THEN 'KY'
WHEN @State = 'LOUISIANA' THEN 'LA'
WHEN @State = 'MASSACHUSETTS' THEN 'MA'
WHEN @State = 'MARYLAND' THEN 'MD' 
WHEN @State = 'MAINE' THEN 'ME'
WHEN @State = 'MARSHALL ISLANDS' THEN 'MH' 
WHEN @State = 'MICHIGAN' THEN 'MI'
WHEN @State = 'MINNESOTA' THEN 'MN'
WHEN @State = 'MISSOURI' THEN 'MO'
WHEN @State = 'NORTHERN MARIANA ISLANDS' THEN 'MP'
WHEN @State = 'MISSISSIPPI' THEN 'MS'
WHEN @State = 'MONTANA' THEN 'MT'
WHEN @State = 'NORTH CAROLINA' THEN 'NC'
WHEN @State = 'NORTH DAKOTA' THEN 'ND'
WHEN @State = 'NEBRASKA' THEN 'NE'
WHEN @State = 'NEW HAMPSHIRE' THEN 'NH' 
WHEN @State = 'NEW JERSEY' THEN 'NJ' 
WHEN @State = 'NEW MEXICO' THEN 'NM'
WHEN @State = 'NEVADA' THEN 'NV'
WHEN @State = 'NEW YORK' THEN 'NY'
WHEN @State = 'OHIO' THEN 'OH'
WHEN @State = 'OKLAHOMA' THEN 'OK'
WHEN @State = 'OREGON' THEN 'OR'
WHEN @State = 'PENNSYLVANIA' THEN 'PA'
WHEN @State = 'PUERTO RICO'THEN 'PR' 
WHEN @State = 'RHODE ISLAND' THEN 'RI'
WHEN @State = 'SOUTH CAROLINA' THEN 'SC'
WHEN @State = 'SOUTH DAKOTA' THEN 'SD'
WHEN @State = 'TENNESSEE' THEN 'TN'
WHEN @State = 'TEXAS' THEN 'TX'
WHEN @State = 'UTAH' THEN 'UT'
WHEN @State = 'VIRGINIA ' THEN 'VA'
WHEN @State = 'VIRGIN ISLANDS' THEN 'VI' 
WHEN @State = 'VERMONT' THEN 'VT'
WHEN @State = 'WASHINGTON' THEN 'WA'
WHEN @State = 'WISCONSIN' THEN 'WI'
WHEN @State = 'WEST VIRGINIA' THEN 'WV'
WHEN @State = 'WYOMING' THEN 'WY'
ELSE ''
END
RETURN @StateCode
END;

任何帮助都非常感谢。谢谢！

Answer 1

您必须声明nvarchar()的长度。它默认为1。

尝试以下操作：

CREATE FUNCTION [dbo].[UDF_StateCode] (@State NVARCHAR(64)) 
RETURNS NVARCHAR(2)
AS
BEGIN
DECLARE @StateCode NVARCHAR(2) ...

不良习惯：宣布无(长度(的varchar -Aaron Bertrand-您应始终为所有varchar或nvarchar变量/参数提供长度。

---

您可以简化返回到nchar(2)而不是可变长度输出。

您还可以通过将此功能作为在线表值函数而不是标量函数编写来提高性能。

通常，这种事情将是一个表代替函数，您将根据需要加入表。但是，对于学习如何编写功能，我猜这是一个合理的例子。

---

一些有关功能性能的好文章：

• 什么时候SQL函数不是函数？"如果不是内联的，那是垃圾。" - 罗布·法利(Rob Farley(
• 内联标量函数-Itzik Ben -Gan
• 标量功能，内部和性能：无聊的帖子的有趣标题-Adam Machanic
• TSQL用户定义的功能：您太害羞而无法提出的十个问题-Robert Sheldon