ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Archive for 6月 2010

SQL Serverのユーザー定義関数を使おう~(9)日付に関するユーザー定義関数を作る

leave a comment »

日付に関するユーザー定義関数を作った。この関数を作った目的は、今年度の範囲で処理をする場面が多かったので、「今年度の最初の日」と「今年度の最後の日」を知るユーザー定義関数を作ろうと思ったためだ。例えば日付が「2010年6月15日」だと、この日に対して「今年度最初の日」は「2010年4月1日」であり、「今年度最後の日」は「2011年3月31日」である。

2010年であっても、4月1日以前は前年度である。日付が「2010年3月15日」だと、この日に対して「今年度最初の日」は「2009年4月1日」であり、「今年度最後の日」は「2010年3月31日」なのだ。

そこでこの関数の中では、まず「日付」に対して「年度」を求めている。「年度」さえ判定できれば、その年の4月1日が「今年度最初の日」であり、次の年の3月31日が「今年度最後の日」である。

使い方は、select dbo.fx日付(‘2010/06/15′,’年度最初日’)、のようにする。最初の引数が日付で、次の引数がパラメーターだ。パラメーターは「年度最初日」と「年度最後日」を実装しているが、これも拡張可能である。日付に関するいろいろな処理を一括してこの関数に盛り込もうと思ったので、パラメーターによって拡張可能にしている。

また、今日の日付で「今年度の最初の日」を求めたいなら、select dbo.fx日付(getdate(),’年度最初日’)、にすることもできる。

——————————————————————————————————-

create function [dbo].[fx日付]
 (
 @P_日付 smalldatetime,
 @p_パラメータ varchar(10)
 )
returns smalldatetime
as
begin

declare @thisnendo int
declare @thisyear41 smalldatetime

/*年度の取得*/
set @thisyear41 = cast(cast(datepart(year,@p_日付) as varchar) + ‘/’ +’4/1′ as smalldatetime)
if @p_日付 >= @thisyear41
 begin
  set @thisnendo = datepart(year,@p_日付)
 end
if @p_日付 < @thisyear41
 begin
  set @thisnendo = datepart(year,@p_日付)-1
 end

/*今年度最初の日を求める*/
if @P_パラメータ = ‘年度最初日’
 begin
  return cast(cast(@thisnendo as varchar) + ‘/’ +’4/1′ as smalldatetime)
 end

/*今年度最後の日を求める*/
if @P_パラメータ = ‘年度最後日’
 begin
  return cast(cast((@thisnendo + 1) as varchar) + ‘/’ +’3/31′ as smalldatetime)
 end
return null
end

Written by Yoshio Matsumoto

2010年6月15日 at 12:14 AM

カテゴリー: SQL Server

SQL Serverのユーザー定義関数を使おう~(8)生徒情報を取得するユーザー定義関数を作る

leave a comment »

システムを作っていくと、多くの場所で同じ処理をすることに気づく。例えば生徒の基本情報を記録する「生徒」テーブルには、生徒の氏名を「姓」と「名」のフィールドで記録している。しかし帳票では「姓+名」で表示をしたいので、至る所で「姓+’ ’+名」のような処理をすることになる。このような似たような処理はユーザー定義関数を作っておくとよい。

ここでは生徒基本情報である「生徒」テーブルから、学籍番号をキーにして生徒の情報を関数で取り出すものを作ってみた。

使い方はこうだ。生徒の姓名が欲しいときには「select dbo.fx生徒(‘1234567′,’姓名’)」のようにする。最初の引数が学籍番号で、次の引数はパラメータだ。性別を知りたいときは「select dbo.fx生徒(‘1234567′,’性別’)」のようにする。関数の作りは簡単で、パラメータによってif文で条件分岐しているだけである。

この関数はパラメーターを増やすと改正可能である。増やす分には既に作ったクエリやストアドプロシージャに影響を与えない。

————————————————————————————————

create function [dbo].[fx生徒]
              (
              @p_学籍番号 char(7),
              @p_種類 varchar(50)
              )
returns varchar(50)
as
begin
              if @p_種類= ‘姓名’
              begin
                            return (SELECT 生徒姓+ ‘ ’ + 生徒名 FROM dbo.生徒 where 学籍番号 = @p_学籍番号)
              end
              if @p_種類= ‘姓’
              begin
                            return (SELECT 生徒姓 FROM dbo.生徒 where 学籍番号= @p_学籍番号)
              end
              if @p_種類= ‘名’
              begin
                            return (SELECT 生徒名 FROM dbo.生徒 where 学籍番号= @p_学籍番号)
              end
              if @p_種類= ‘性別’
              begin
                            return (SELECT dbo.kbn_性別.区分名 FROM dbo.生徒                                                        INNER JOIN dbo.kbn_性別 ON dbo.生徒.性別 = dbo.kbn_性別.区分コード                                                        where dbo.生徒.学籍番号 = @p_学籍番号)
              end
              if @p_種類= ‘性別コード’
              begin
                            return (SELECT 性別 FROM dbo.生徒 where 学籍番号= @p_学籍番号)
              end
return null
end

Written by Yoshio Matsumoto

2010年6月14日 at 11:41 PM

カテゴリー: SQL Server