Function in SQL Server (How to use function in sql server 2005 / 2008 )

-
Types :
Inline Table-Valued  (For reducing complex logic or big logic )
Multi-statement Table-valued. (For reducing complex logic or big logic )
Scalar valued function      (mostly for beginners)


How do I create and use a Scalar User-Defined Function?

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.
 
CREATE FUNCTION [dbo].[whichContinent] 
(
 @country nvarchar(30)
)
RETURNS nvarchar(30)
AS
BEGIN
 declare @result nvarchar(20) 
 select @result=case @country
 when 'A' then 'AMERICA'
 when 'B' then 'BELGAON'
 when 'I' then 'INDIA'
 else 'Unknown'
 end
 
 return @result
 END
 
 

Execution :

 
1)  create table test1(Country nvarchar(15),cont as ([dbo].[whichContinent](country)))
 
2)  insert into test1(country) values ('I')
 
3)  select * from test1
 
 
I refer following link ...
... 










More info..

 

















































Comments











Post a Comment



















Popular posts from this blog









File operations in C# (.net)






-















  Here we are performing some basic file operations which widely needed in applications.   ASPX :       <div>          <asp:Button ID="Button1" runat="server" Text="CLick" onclick="Button1_Click" />          <asp:FileUpload ID="FileUpload1" runat="server" /><br /><br />           </div>   C# :         using System.IO;         using System.Text;            protected void Button1_Click(object sender, EventArgs e)          {               string path = @"E:\MVC Application Demos\ABC.txt";            // File Path taken               string name = FileUpload1.FileName.ToString();                // Here we get File Name only               string pa...








Read more










Add Serial no in crystal report without coding






-














Image







  Adding a serial number in grouping without any code. I have placed the step by step screenshots which will explain easily how to do it.         1) Add a new field in Running Total Fields in Field Explorer           Give a proper name to your Running total name. For example I have choosen here RTotal0. Now categorize based on which field in database you want to summarize it. Next is as we want serial number select count. If you want your serial number to start again from the start when the value in the group changes then select on change of group and specify the grouping name else keep the default value.           Smaple Preview of the Design View of Report where I Have added SRNo column at the start in grouping section.         








Read more










How to get top " n " number of rows from table  






-















    Suppose you want top n  values from table , so execute following query  with replacement of following  item as mention below :   1 )  Replace your our tablename with [table_name]    2 )  Replace your "n" with row  numbers  (how many rows you want )   [   Note :  You can do multiply  and division operations also   ]    Query for Top rows  :   SELECT   MainSQL .*  FROM  (  SELECT  t .*, ROW_NUMBER ()  OVER (  ORDER  BY  1  )   Row_Number , COUNT ( 1 )  OVER (  ORDER  BY  1  RANGE  BETWEEN  UNBOUNDED PRECEDING AND  UNBOUNDED  FOLLOWING )  Row_Count  FROM  (SELECT  *  FROM  [table_name] )  t ORDER  BY  1  )  MainSQL  WHERE   Row_Number >=  1   AND  Row_Number <  (  1  +  2  )  ORDER  BY   Row_Number ;     Query for Bottom  rows  :   SELECT MainSQL .*  FROM  (  SELECT  t .*, ROW_NUMBER ()  OVER (  ORDER  BY  1  )   Row_Number ,  COUNT ( 1 )  OVER (  ORDER  BY  1  RANGE  BETWEEN   UNBOUNDED PRECEDING AND  UNBOUNDED FOLLOWING )  Row_C...








Read more