Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

This is one of the most asked questions in recent time and the answer is even simpler.

Here is the question – How to Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter. Stored Procedures are very old concepts and every day I see more and more adoption to Stored Procedure over dynamic code. When we have almost all of our code in Stored Procedure it is very common requirement that we have need of one stored procedure’s result to be passed as another stored procedure’s parameter.

Let us try to understand this with a simple example. Please note that this is a simple example, the matter of the fact, we can do the task of these two stored procedure in a single SP but our goal of this blog post is to understand how we can pass the result of one SP to another SP as a parameter.

Let us first create one Stored Procedure which gives us square of the passed parameter.

-- First Stored Procedure
CREATE PROCEDURE SquareSP
@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam
-- Additional Code
RETURN (@MyFirstParamSquare)
GO

Now let us create second Stored Procedure which gives us area of the circle.

-- Second Stored Procedure
CREATE PROCEDURE FindArea
@SquaredParam INT
AS
DECLARE @AreaofCircle FLOAT
SELECT @AreaofCircle = @SquaredParam * PI()
RETURN (@AreaofCircle)
GO

You can clearly see that we need to pass the result of the first stored procedure (SquareSP) to second stored procedure (FindArea). We can do that by using following method:

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter
DECLARE @ParamtoPass INT, @CircleArea FLOAT
-- First SP
EXEC @ParamtoPass = SquareSP 5
-- Second SP
EXEC @CircleArea = FindArea @ParamtoPass
SELECT @CircleArea FinalArea
GO

You can see that it is extremely simple to pass the result of the first stored procedure to second procedure.

You can clean up the code by running the following code.

-- Clean up
DROP PROCEDURE SquareSP
DROP PROCEDURE FindArea
GO

Trovato qui.

Group by Rows and Columns using XML PATH – Efficient CONCAT Trick

vedi anche qui.

I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.
For example here is my table:

How can I generate result as following?

We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result.

Here is the quick script:

-- Create table
CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), Instructor VARCHAR(100), RoomNo VARCHAR(100))
GO

-- Populate table
INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO

-- Check orginal data
SELECT *
FROM #TestTable
GO

-- Group by Data using column and XML PATH
SELECT
StudentName,
STUFF((
SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
FROM #TestTable
WHERE (StudentName = StudentCourses.StudentName)
FOR XML PATH (''))
,1,2,'') AS NameValues
FROM #TestTable StudentCourses
GROUP BY StudentName
GO

-- Clean up
DROP TABLE #TestTable
GO

Trovato qui.