SQL SERVER – 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

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Replace a Column Name in Multiple Stored Procedure all together

I receive a lot of emails every day. I try to answer each and every email and comments on Facebook and Twitter. I prefer communication on social media as this gives opportunities to others to read the questions and participate along with me. There is always some question which everyone likes to read and remember. Here is one of the questions which I received in email. I believe the same question will be there any many developers who are beginning with SQL Server. I decided to blog about it so everyone can read it and participate.

“I am beginner in SQL Server. I have a very interesting situation and need your help.

I am beginner to SQL Server and that is why I do not have access to the production server and I work entirely on the development server. The project I am working on is also in the infant stage as well.

In product I had to create a multiple tables and every table had few columns. Later on I have written Stored Procedures using those tables. During a code review my manager has requested to change one of the column which I have used in the table. As per him the naming convention was not accurate. Now changing the columname in the table is not a big issue. I figured out that I can do it very quickly either using T-SQL script or SQL Server Management Studio. The real problem is that I have used this column in nearly 50+ stored procedure. This looks like a very mechanical task. I believe I can go and change it in nearly 50+ stored procedure but is there a better solution I can use. Someone suggested that I should just go ahead and find the text in system table and update it there. Is that safe solution? If not, what is your solution.

In simple words, How to replace a column name in multiple stored procedure efficiently and quickly?

Please help me here with keeping my experience and non-production server in mind.”

Well, I found this question very interesting. Honestly I would have preferred if this question was asked on my social media handles (Facebook and Twitter) as I am very active there and quite often before I reach there other experts have already answered this question. Anyway I am now answering the same question on the blog so all of us can participate here and come up with an appropriate answer. Here is my answer -

“My Friend,

I do not advice to touch system table. Please do not go that route. It can be dangerous and not appropriate.

The issue which you faced today is what I used to face in early career as well I still face it often. There are two sets of argument I have observed – there are people who see no value in the name of the object and name objects like obj1, obj2 etc. There are sets of people who carefully chose the name of the object where object name is self-explanatory and almost tells a story. I am not here to take any side in this blog post – so let me go to a quick solution for your problem.

Note: Following should not be directly practiced on Production Server. It should be properly tested on development server and once it is validated they should be pushed to your production server with your existing deployment practice. The answer is here assuming you have regular stored procedures and you are working on the Development NON Production Server.

Go to Server Note >> Databases >> DatabaseName >> Programmability >> Stored Procedure

Now make sure that Object Explorer Details are open (if not open it by clicking F7). You will see the list of all the stored procedures there. Now you will see a list of all the stored procedures on the right side list. Select either all of them or the one which you believe are relevant to your query. Now…

Right click on the stored procedures >> SELECT DROP and CREATE to >> Now select New Query Editor Window or Clipboard.

Paste the complete script to a new window if you have selected Clipboard option. Now press Control+H which will bring up the Find and Replace Screen. In this screen insert the column to be replaced in the “Find What”box and new column name into “Replace With” box.

Now execute the whole script. As we have selected DROP and CREATE to, it will created drop the old procedure and create the new one.

Another method would do all the same procedure but instead of DROP and CREATE manually replace the CREATE word with ALTER world. There is a small advantage in doing this is that if due to any reason the error comes up which prevents the new stored procedure to be created you will have your old stored procedure in the system as it is. “

Well, this was my answer to the question which I have received. Do you see any other workaround or solution?

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name – Part 2

Earlier this week I wrote a blog about Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. I received plenty of comments on the subject. One of the statements which I used in the story (Time: Any Day – usually right before developer wants to go home) was very much liked by many developers. I guess this is because we are all like the same. We often get more work, when we are ready to go home. After reading the blog post many readers and SQL Server Experts have posted an enhanced T-SQL script to find column used in a stored procedure.

SQL Server Expert Imran Mohammed is a very good friend of mine. He posted a very interesting note that function used in the original script is going to be deprecated in future releases so better to use following scripts.

1) Search in All Objects

This script search stored procedures, views, functions as well other objects.
-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'BusinessEntityID' + '%'
GO

2) Search in Stored Procedure

This script search only stored procedure for specified column.
-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'BusinessEntityID' + '%'
GO

Thanks Imran for suggesting this follow up script. Btw, if you want to read a short story, I suggest you head to original blog post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

Place: Any Developer Shop

Scenario: A developer wants to drop a column from a table

Time: Any Day – usually right before developer wants to go home

The developer rushes to the manager and following conversation begins:

Developer: I want to drop  a column from one of the tables.

Manager: Sure, just document it where all the places it is used in our application and come back to me.

Developer: We only use stored procedures.

Manager: Sure, then documented how many stored procedures are there which are using your column and justify the modification. I will approve it once I see the documentation.

Developer back to the desk looking at hundreds of stored procedures in SSMS thinking how to find which stored procedure may be using his column. Suddenly he remembers a bookmark which he has saved earlier which had T-SQL Script to do so. Here quickly opened it and run the code.

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

Above T-SQL Script will search in the stored procedure text and return the name of the stored procedure if it will find the value specified in the WHERE condition. He was happy with his discovery and immediately created the list of the stored procedures and next action items as asked by the manager. He sent the list to the manager right after 10 minutes of his discussion with the manager. He rushed to manager to office to inform his promptness and realized that the manager had left for the day just few moments before.

Moral of the story: Work life balanced can be maintained if we work smart!


Let us see above T-SQL Script in action. Let us assume that in AdventureWorks2012 database we want to find the BusinessEntityID column in all the stored procedure. We can use run following T-SQL code in SSMS Query Editor and find the name of all the stored procedure.

USE AdventureWorks2012
GO
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'BusinessEntityID' + '%'
AND TYPE = 'P'
GO

Above T-SQL script will give results containing the name of the stored procedure and stored procedure text along with it.

While we are discussing this subject here are a couple of other additional related blog post which may interesting.

A question to you: Is there any better way to find column used in a stored procedure? Please leave a comment with your solution. I will post the same in this blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012

I might have said this earlier many times but I will say it again – SQL Server never stops to amaze me. Here is the example of it sp_describe_first_result_set. I stumbled upon it when I was looking for something else on BOL. This new system stored procedure did attract me to experiment with it. This SP does exactly what its names suggests – describes the first result set. Let us see very simple example of the same. Please note that this will work on only SQL Server 2012.

EXEC sp_describe_first_result_set
N'SELECT * FROM AdventureWorks.Sales.SalesOrderDetail', NULL, 1
GO

Here is the partial resultset.

Now let us take this simple example to next level and learn one more interesting detail about this function.

First I will be creating a view and then we will use the same procedure over the view.

USE AdventureWorks
GO
CREATE VIEW dbo.MyView
AS
SELECT
[SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

Now let us execute above stored procedure with various options. You can notice I am changing the very last parameter which I am passing to the stored procedure.This option is known as for browse_information_mode.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 0;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 1;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 2;
GO

Here is result of all the three queries together in single image for easier understanding regarding their difference.

You can see that when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table. When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

I found it really really interesting that there exists system stored procedure which now describes the resultset of the output.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

Madhivanan is SQL Server MVP and very talented SQL expert. Here is one of the nugget he shared on Just Learned.

He shared a tip where there were two interesting point to learn.

  1. Do not use keywords as an object name
  2. [read DHall's excellent comment below]

He has given excellent example how GO can be executed as stored procedure. Here is the extension of the tip. Create a small table and now just hit EXEC GO; and you will notice that there is row in the table.

Create Stored Procedure
CREATE PROCEDURE GO
AS
SELECT
1 AS NUMBER

Create Table
CREATE TABLE T1 (ID INT)

Now execute following code
INSERT INTO T1(ID)
EXEC GO;

Now when selecting from table it will give us following result:
SELECT *
FROM T1

Now see following resultset:

So without inserting any data we inserted the data, well indeed good puzzle but very bad practical practice. Every body should be aware of this gotcha and avoid it. Thanks Madhivanan for teaching this interesting learning.

Republishing here with authorization of Just Learned.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Powershell – Get a List of Fixed Hard Drive and Free Space on Server

Earlier I have written this article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server. I recently received excellent comment by MVP Ravikanth. He demonstrated that how the same can be done using Powershell. It is very sweet and quick solution.

Here is the powershell script. Run the same in your powershell windows.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Well, I ran this script in my powershell window, it gave me following result – very accurately and easily.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Thanks Ravikanth one more time for excellent tip.

Reference: Pinal Dave (http://blog.SQLAuthority.com)