Post
Ask Question

Scalar-valued Function

In saclar- Valued function multiple column name data display data in single cell, using scalar-valued function.

Create registration table

  1. CREATE TABLE [dbo].[Registration]
  2. (
  3.    [RId] [bigint] IDENTITY(1001,1) NOT NULL,
  4.    [Name] [varchar](100) NULL,[DOB] [datetime] NULL,
  5.    [RegDate] [datetime] NULL,
  6.    [Address] [varchar](200) NULL,
  7.    [State] [varchar](100) NULL,
  8.    [District] [varchar](100) NULL,
  9.    [Email] [varchar](100) NULL,
  10.    [MobileNo] [varchar](100) NOT NULL,
  11.    [PostId] [varchar](100) NULL,
  12.    [SponserRID] [bigint] NULL,
  13.    [SponserPost] [varchar](50) NULL,
  14.    [LoginId] [varchar](50) NULL,
  15.    [Password] [varchar](50) NULL,
  16. )  
Create scalar-valued function
  1. CREATE FUNCTION [dbo].[fn_NameMobile]  
  2. (@id varchar(100))  
  3. RETURNS VARCHAR(100)  
  4. AS  
  5. begin  
  6.   
  7. RETURN  
  8. (  
  9. select Name +' '+'['+cast(mobileno AS VARCHAR)+']' +' '+'['+cast(loginid AS VARCHAR)+']' as Name from Registration WHERE RId = @id  
  10. )  
  11.   
  12. END  
  13.   
  14. GO  
Query and output

SQL Query and output

select Name,mobileno,loginid from registration where RId='1024'

select dbo.fn_NameMobile(1024) as Name_MobileNo_LoginID