Calling Procedure result set (Out Put) in another procedure

One of my friend asked me about calling a procedure result set into another procedure to perform some further operations before final result. Using this technique we can use procedure as a function.
I have 2 solutions for that


1- Using Table Parameter in procedure to get table data and perform some actions on that. Table Value Parameter. This can be used in SQL SERVER 2008 and above only.
2- Calling procedure into another procedure and storing its data into temporary table.


I am going to mention an example for second solution and link for another post to understand second one.


In first picture I have created a procedure that create a declare table insert some data into that table.

In second picture I have created another procedure that create a declare table  . Note there is an Insert statement for second table but this insert statement using an result data set from first procedure.

In last picture there are 2 execute statements for both first and second procedure. You can clearly view that first result set is the output from first procedure and in 2nd result set i have add some more rows along with first procedure output that is already added in second table using exec procedure statement. Now in my last output i have used Rollup statement to sum up total values. that you can see in last result set.

Using this technique you can use existing procedure result set to add some more logic.

Here is the code for this example.



if exists (select * from sys.objects where name like 'up_returnDataSet')
drop procedure up_returnDataSet
go
if exists (select * from sys.objects where name like 'up_returnDataSet2')
drop procedure up_returnDataSet2
go


Create procedure up_returnDataSet
as
begin
Declare @vTable Table(ID int,Value int)

Insert into @vTable values (1,10),(1,50),(2,15),(2,45),(3,30),(3,10)

Select * from @vTable

end

go

Create procedure up_returnDataSet2
as
begin
Declare @vTableInsert Table(ID int,Value int)

Insert into @vTableInsert
exec up_returnDataSet

-- Insert some more values
Insert into @vTableInsert values (1,55),(2,65),(3,95)

Select ID,Value from @vTableInsert order by id

-- Return Totals with New values
Select isnull('Category Total:' +cast(ID as varchar(5))  ,'Grand Total'),SUM(Value) from @vTableInsert
group by ID
with ROLLUP

end


-- displaying results
exec up_returnDataSet
exec up_returnDataSet2

SQL SERVER 2012 "Denali" -- Programming Enhancements

I have gathered this information through different blogs and demos

1.         Sequences: Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence.

/****** Create Sequence Object ******/
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;

/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);

/****** Insert Data ******/
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Shamas Saeed'),
(NEXT VALUE FOR MySequence, 'Qamar Saeed');

/****** Display results ******/
SELECT * FROM @Person;

The results would look like this:

ID FullName
1 Shamas Saeed
2 Qamar Saeed

2.         New Paging through Query: Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bring back only the results you want to show to your users when they are needed. Following is the example of paging though query. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.

SELECT *
FROM TableSample
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

3.         Full Text Search: The Full Text Search in SQL Server 2012 has been enhanced by allowing you to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but its possible now.

SQL SERVER 2012 "Denali" -- Hardware and Software Requirements

I have gathered this information from Microsoft and other blogs.

·         Microsoft recommends using NTFS file format instead of FAT32. FAT32 will work but you should probably not use it.

·         You can't install SQL Server 2012 (code-named Denali) on mapped drives or compressed drives.

·         You have to have the "no-reboot" package installed prior to installing SQL Server 2012 (code-named Denali). This is included in Windows 7 SP1 and Windows Server 2008 R2. Otherwise, you can download the no-reboot package from Microsoft.

·         SQL Server 2012 (code-named Denali) requires the .NET Framework 4.0.

·         Virtualization is supported using Microsoft's Hyper-V technology.

·         You will need at least 3.6 GB of free disk space.

·         Microsoft recommends that you do not install SQL Server 2012 (code-named Denali) on a domain controller.

·         Recommended Processors & RAM

·         64-bit version: AMD Opteron, AMD Athlin 64, Intel Xeon with Intel EM64T Support or Intel Pentium IV with EM64T support running 2.0 GHz or faster. Recommended RAM is maximum that operating system supports or at least 2 GB.

·         32-bit version: Pentium III or compatible running at 2.0 GHz of faster. Recommended RAM is maximum that operating system supports or at least 2 GB.

·         Windows PowerShell 2.0 is a pre-requisite for installing SQL Server 2012 (code-named Denali). You can get this software from the Windows Management Framework page.

·         Check out the step by step installation guide with screenshots to get a preview of the SQL Server 2012 install and configuration process.

SQL SERVER History

The history of SQL Server dates back to 1989 when the product came about as a result of a partnership between Microsoft, Sybase, and Ashton-Tate.

Database Version	Year	Release Name	Codename
1.0 (OS/2)	1989	SQL Server 1.0
4.21 (WinNT)	1993	SQL Server 4.21
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0 OLAP Tools	Plato
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64-bit Edition	Liberty
9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai
10.5	2010	SQL Server 2008 R2 & 2010	Denali
	2012	SQL Server 2012 CTE0

Computed / Calculated Column with Persisted Value

In previous blog http://shamas-saeed.blogspot.com/2011/05/creating-computed-calculated-column-in.html we have learned how to create a computed or calculated column and what is its limitation. In this blog we will what is difference between computed columns and persisted computed column and why computed column persisted is required.

Persisted computed columns are giving better performance than the cost of reading IO from database. Although you can see by creating index it will increase performance but our focus is to show performance difference

·         Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

·         A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns Value1 and Value2, the computed column Value1 + Value2 can be indexed, but computed column Value1 + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

As shown from attached screen we can see how much computed column index will improve performance for example by creating index on FullName it will improve 99.7884 percent. I will post another blog for creating and effect of index on computed column.

/*  Performance Effect  */

/*   Code sample */

USE AdventureWorks2008R2_Data

GO

-- Create Table

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[nonpresisted]') AND TYPE IN (N'U'))

DROP TABLE [dbo].[nonpresisted]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[presisted]') AND TYPE IN (N'U'))

DROP TABLE [dbo].[presisted]

GO

CREATE TABLE nonpresisted (ID INT,

FirstName VARCHAR(100),

LastName CHAR(8000))

GO

CREATE TABLE presisted (ID INT,

FirstName VARCHAR(100),

LastName CHAR(8000))

GO

-- Insert One Hundred Thousand Records

INSERT INTO nonpresisted (ID,FirstName,LastName)

SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

'Bob',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'

ELSE 'Brown' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

INSERT INTO presisted (ID,FirstName,LastName)

SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

'Bob',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'

ELSE 'Brown' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

-- Add Computed Column

ALTER TABLE dbo.nonpresisted ADD

FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)

GO

-- Add Computed Column PERSISTED

ALTER TABLE dbo.presisted ADD

FullName_P AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12) PERSISTED

GO

 -- Select Comparision

SELECT FullName

FROM dbo.nonpresisted

WHERE FullName = 531441

GO

SELECT FullName_P

FROM dbo.presisted

WHERE FullName_P = 531441

GO

 --Clean up Database

DROP TABLE nonpresisted

DROP TABLE presisted

GO