Storing records in Temp table while executing SP

Temporary tables are tables which are created in the tempDB and are automatically deleted when they are not used. They act like an regular table and can be queried records and modifyu their value via update, delete, insert. If we are using temp table inside sp they are automatically destroyed once the connection is closed.

The scope of the temp table is in the session in which it’s created.

Different type of Temp Table

  • Local Temporary tables
  • Global Temporary tables

Syntax


CREATE TABLE #MstCustomerUpload(ID int, NAME NVARCHAR(100),MOBILENO VARCHAR(10),PANNO NUMERIC(10,0))

When we create a temporary table we gave the name of the table with # which denotes that the table is local temporary table. All the temp table are stored in tempdb as shown below.

https://giphy.com/embed/3o7bugWhds34OM5E9q

6

Explicitly dropping the Temp table


drop table #MstCustomerUpload

Usually we use temp table in stored procedure where we want to stored the records in a table and query, update, insert, delete the records from the table. If we are creating the temp table inside the stored procedure, once the stored procedure get executed the temp table is automatically destroyed.

Simple stored procedure with Temp table

There are much better scenarios where we will leveraging usage of Temp table. For now let just focus on this small sceario where i am retrieving records from two table customer and cust and performing union operation and then insert the records to the temp table and retrieving records from the temp table to the user.


CREATE PROCEDURE spGetCustomerDetails
as
begin

create table #tmpCustomer(Id int identity(1,1) ,Name nvarchar(50),Email nvarchar(50))

insert into #tmpCustomer  
select top(2) Name,Email from [dbo].[Customer] 
union all
select top(2) Name,'' as Email from [dbo].[Cust] 

select * from #tmpCustomer;

end
go

Running the stored procedure

6

Global temporary table

In order create a global temporary table we use double ## symbols. The difference between local temp tables and global temo tables is global temp tables are available to all SQL connections and is destoyed when last connection is closed.


create table ##tmpCustomerDetails (Id int, Name nvarchar(50), Email nvarchar(50))

The table created for Global temporary table is same as the name defined by the user as compared to local temp table where SQL server add random values at the end.

5

Happy Learning

User-defined Functions in SQL Server

csharIn Sql server there are three kinds of User-defined functions

  1. Scalar functions
  2. Table Valued functions
    1. Inline table valued funtions
    2. Multi-statement table-valued functions

Scalar function

Scalar function is a function which may or may not take any input parameter but always return a single value. The Scalar function may return any kind of datatype except text, ntext, image, cursor and timestamp.

Syntax for creating Scalar Function.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION  
(
	-- Add the parameters for the function here
	 
)
RETURNS 
AS
BEGIN
	-- Declare the return variable here
	DECLARE  

	-- Add the T-SQL statements to compute the return value here
	SELECT  = 

	-- Return the result of the function
	RETURN 

END
GO

Now we have a table named order which has following records

1

So now i want to calculate the number of orders created by particular CustomerId by using Scalar function

Calling function in a Select query.


SELECT DISTINCT CUSTOMERID,DBO.GetOrderCount(CUSTOMERID) AS ORDER_COUNT FROM    [Order]

2

Inline table valued function

As in Scalar function we learn that we can return a single value so incase where we want to return the multple value from the function we can make user of Inline table valued function which returns a table data type.

Scenario:

We have a below table:

orders

Now we want to return the records based on the passed customer id to the Inline table valued function.

Syntax:

-- ================================================
-- Template generated from Template Explorer using:
CREATE FUNCTION fn_GetOrdersByCustomerId
(	
	-- Add the parameters for the function here
	@CustomerId nvarchar(100)
)
RETURNS TABLE 
AS
RETURN 
(
	
SELECT  CUSTOMERID,STOREID,GROSSTOTAL, ORDERCREATEDDATE
 from [Order] where CUSTOMERID=@CustomerId
)
GO

In Inline table valued function we don’t use Begin and End block as we did in Scalar function.

Once we compile the table valued function the function is located in Table-Valued function section under Programmatically-functions. This function will return the CustomerId, StoreId, GrossTotal, OrderCreatedDate from Order table based on the CustomerId passed to the function as shown below:

Executing the inline table valued function


select * from dbo. fn_GetOrdersByCustomerId('123456789') 

Output:

3

Multi Statement table value Functions

Multi statement table valued function is quiet similar to Inline table valued function.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: 
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

Now we can see the syntax different between Inline table valued function and Multi-statement Inline function. In Multi-Statement we return a table with structure defined after the Table data type.

Using Multi-Statement Inline Function to retrieve records from the Order table based on storeId passed.


create function fn_GetOrders(@StoreId nvarchar(20))
RETURNS @Table Table (ID int,CUSTOMERID nvarchar(200),STOREID nvarchar(20))
AS
BEGIN
INSERT INTO @Table
SELECT ID,CUSTOMERID,STOREID FROM [ORDER] WHERE STOREID=@StoreId;
return

END

select * from fn_GetOrders('3267')

4

 

Happy Learning.

Other function in SQL Server

CAST

CAST function converts the expression from one data type to another.
Syntax Select Cast(EXPRESSION AS DataType([Length]))
We have following records in our table:

1

We want the datetime part to be converted to varchar type. In order to do the same we will make use of Cast function as shown below:

2

In real life scenario we will be using CAST a lot when we to concatenate two different data type with each other as shown below:

We have following records in a table

ID Name
2 Saillesh
3 VIrender
4 NIhsant

Now we want to display Id,Name, Name+id values in a query. So in order to achieve the same if I simply try to concatenate both these two columns, we will receive an error as shown below:

5
So in order to solve the same, we will make use of Cast function

6

Convert

Convert function is also used to convert the expression from one data type to another data type. Convert function has a different syntax as compared to Cast method.
Convert method first take the datatype than expression and Style which is an optional parameter as shown below:

SELECT TOP 1000 [ID]
      ,[name]
      ,[dob],
	  CONVERT(nvarchar,dob) convertedDate
  FROM [SampleDb].[dbo].[dob]

6

Style parameter in Convert function

We have an optional style parameter in Convert function. In cast function we saw how we were converting the dob of type nvarchar, the problem with CAST function is that we cannot control the format of DOB with CAST, which we can resolve using Convert with Style parameter.

In order to format the DateTime column Microsoft has provided us with styles as shown below:

Style DateFormat
101 mm/dd/yyyy
102 yy.mm.dd
103 dd/mm/yyyy
104 dd.mm.yy
105 dd-mm-yy

In order to check the more Style, you can go and check the MSDN doc for the same.

Date and Time style

So now I want to print the DOB in dd-mm-yyyy format. In order to achieve the same I make use of style parameter in Convert function as shown below:

6