{"id":258,"date":"2009-02-08T15:47:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=258"},"modified":"2009-02-08T15:47:00","modified_gmt":"1970-01-01T00:00:00","slug":"sql-server-da-function-kullanimi","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2009\/02\/08\/sql-server-da-function-kullanimi\/","title":{"rendered":"SQL Server da Function Kullanimi"},"content":{"rendered":"

Merhaba Arkadaslar,<\/span><\/p>\n

Bu makalemizde SQL Server da önem arz eden, bazen gerekliligini iliklerinize kadar hissettiginiz kendi yazacagimiz SQL Functionlarini inceleyecegiz.<\/span><\/p>\n

Functionlar istenilen deger tipinde dönüs yapabilir. INT, VARCHAR deger döndürebileceginiz gibi bir tablo da döndürebilirsiniz. Basit bir function ile örnekleyelim konuyu.<\/span><\/p>\n

.<\/a><\/p>\n

Mesela MUSTERI tablosunda parametre olarak verdigimiz ID’den ID’si büyük esit olan müsterilerden yasi en büyük olanin yasini döndürelim.<\/span><\/p>\n

CREATE<\/span> FUNCTION<\/span> udf_MusteriEnBuyukYas<\/span><\/p>\n

(<\/span><\/p>\n

@Id int<\/span> <\/span><\/p>\n

— alacagi parametreleri virgül <\/span><\/p>\n

–ile ayirarak parametre birden cok parametre verebilirsiniz<\/span><\/p>\n

)<\/span><\/p>\n

RETURNS<\/span> int<\/span> –döndürecegi degerin tipi<\/span><\/span><\/p>\n

AS<\/span><\/p>\n

BEGIN<\/span><\/p>\n

 <\/span><\/p>\n

  <\/span>DECLARE<\/span> @Result int<\/span><\/span><\/p>\n

  <\/span>SET<\/span> @Result =<\/span> (<\/span>SELECT<\/span> Max<\/span>(<\/span>Yas)<\/span> FROM<\/span> MUSTERI Where<\/span> Id >=<\/span>@Id)<\/span> <\/span><\/p>\n

 <\/span><\/p>\n

RETURN<\/span>(<\/span>@Result)<\/span><\/span><\/p>\n

END<\/span><\/p>\n

 <\/span><\/p>\n

Peki, bunu nasil çalistiracagiz. Hemen onun da kod örnegini verelim.<\/span><\/p>\n

 <\/span><\/p>\n

\"\"<\/a><\/span><\/p>\n

 <\/span><\/p>\n

DECLARE<\/span> @Id int<\/span><\/span><\/p>\n

SET<\/span> @Id =<\/span> 2<\/span><\/p>\n

SELECT<\/span> dbo.<\/span>udf_MusteriEnBuyukYas(<\/span>@Id)<\/span><\/span><\/p>\n

 <\/span><\/p>\n

\"\"<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

Çok güzel bir fonksiyon örnegi ile konumuzu bitirelim. Bu fonksiyon bir tablo döndürmektedir. Mesela DB den adinda “Musteri” nin geçtigi  <\/span>‘Default or DEFAULT constraint’ <\/span><\/span><\/span><\/p>\n

‘Scalar function’ ,’Inlined table-function’ ,’Stored procedure’ ,’Rule’,’Replication filter stored procedure’ ,’Table function’ ,’Trigger’ ,’User table’ ,’View’ ,’Extended stored procedure’<\/span> leri bulalim ve tipini de yazdiralim. Bunun için asagidaki fonksiyonu olusturalim.<\/span><\/span><\/p>\n

.<\/a><\/p>\n

 <\/span><\/p>\n

Create<\/span> Function<\/span> dbo.<\/span>udf_FindObjects (<\/span>@string varchar<\/span>(<\/span>100))<\/span><\/span><\/p>\n

returns<\/span> @tbl  <\/span>Table<\/span>(<\/span>Name<\/span> varchar<\/span>(<\/span>128),<\/span> Type<\/span> varchar<\/span>(<\/span>100))<\/span><\/span><\/p>\n

AS<\/span> <\/span><\/p>\n

BEGIN<\/span><\/p>\n

insert<\/span> into<\/span> @tbl(<\/span>Name<\/span>,<\/span>Type<\/span>)<\/span> select<\/span> name<\/span>,<\/span> ObjectType =<\/span> case<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘D’<\/span> then<\/span> ‘Default or DEFAULT constraint’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘FN’<\/span> then<\/span> ‘Scalar function’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘IF’<\/span> then<\/span> ‘Inlined table-function’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘P’<\/span> then<\/span> ‘Stored procedure’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘R’<\/span> then<\/span> ‘Rule’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘RF’<\/span> then<\/span> ‘Replication filter stored procedure’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘TF’<\/span> then<\/span> ‘Table function’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘TR’<\/span> then<\/span> ‘Trigger’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘U’<\/span> then<\/span> ‘User table’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘V’<\/span> then<\/span> ‘View’<\/span> <\/span><\/p>\n

when<\/span> TYPE<\/span>=<\/span>‘X’<\/span> then<\/span> ‘Extended stored procedure’<\/span><\/span><\/p>\n

else<\/span> ‘others’<\/span><\/span><\/p>\n

end<\/span><\/p>\n

 <\/span>from<\/span> sysobjects where<\/span> id in<\/span><\/span><\/p>\n

(<\/span>select<\/span> id from<\/span> syscomments where<\/span> text<\/span> like<\/span> ‘%’<\/span>+<\/span>@string+<\/span>‘%’<\/span>)<\/span><\/span><\/p>\n

return<\/span><\/p>\n

END<\/span><\/p>\n

 <\/span><\/p>\n

Örnek sorgu olarak da <\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> *<\/span> FROM<\/span> dbo.<\/span>udf_FindObjects(<\/span>‘musteri’<\/span>) <\/span><\/span>çalistirdigimiz da asagidaki sonucu aliriz.<\/span><\/p>\n

 <\/span><\/p>\n

\"\"<\/span><\/p>\n

 <\/p>","protected":false},"excerpt":{"rendered":"

Bu makalemizde Sql Serverda önem arz eden bazen gerekliligini iliklerinize kadar hissettiginiz kendi yazacagimiz SQL Functionlarini inceleyecegiz.<\/p>\n","protected":false},"author":550,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[5294,4960,4555,3877],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/258"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/550"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=258"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/258\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=258"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}