MySQL – How to Create Stored Procedure in MySQL

MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to create a stored procedure which will accept item_description and returns the total sales_amount for the enire period, you can do it as shown below

DELIMITER $$
CREATE PROCEDURE Test.usp_get_sales
(
param_item_description VARCHAR(100)
)
BEGIN
SELECT
item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
JOIN sales
WHERE item_description=param_item_description
GROUP BY item_description;
END;
$$
DELIMITER
;

Note that the creation of the stored procedure starts with setting the Delimiter $$. The default delimiter for MySQL statements are semicolon so in order to instruct the MySQL engine about the start and end of the stored procedure block, you need to use a different delimiter (which in this case $$ is used).

Now you can execute a stored procedure using CALL keyword as shown below.

Execution 1

CALL usp_get_sales('Television');

When you execute the above code, the result is

Item_description sales_amount
Television 3600.00

Execution 2

CALL usp_get_sales('laptop');

When you execute the above code, the result is

Item_description sales_amount
laptop 6800.00

Note: The parameters do not start with @ like we use in SQL Server. So in order to differentiate between the actual column name and parameter name, the name param_item_description is used. You may need to use different naming conventions as you like.

I have previously written a similar article here: MySQL – How to Create Stored Procedure.

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

About these ads

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people -

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

The above query will return results something very similar to the following:

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

Now over here when you scroll down you will notice Port Details.

It is that easy to find the port of the second instance of SQL Server.

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

SQL SERVER – Retrieve Maximum Length of Object Name with sp_server_info

A very common question which I often receive is -

“How big my table name can be?”

The answer is simple 128.  You can figure this out by executing stored procedure sp_server_info on SQL Server.

Execute following stored procedure in SQL Server.

EXEC sp_server_info

It will return following resultset.

attribute_id attribute_name attribute_value
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH 128
14 MAX_QUAL_LENGTH 128
15 COLUMN_LENGTH 128
16 IDENTIFIER_CASE MIXED
17 TX_ISOLATION 2
18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
19 SAVEPOINT_SUPPORT Y
20 MULTI_RESULT_SETS Y
22 ACCESSIBLE_TABLES Y
100 USERID_LENGTH 128
101 QUALIFIER_TERM database
102 NAMED_TRANSACTIONS Y
103 SPROC_AS_LANGUAGE Y
104 ACCESSIBLE_SPROC Y
105 MAX_INDEX_COLS 16
106 RENAME_TABLE Y
107 RENAME_COLUMN Y
108 DROP_COLUMN Y
109 INCREASE_COLUMN_LENGTH Y
110 DDL_IN_TRANSACTION Y
111 DESCENDING_INDEXES Y
112 SP_RENAME Y
113 REMOTE_SPROC Y
500 SYS_SPROC_VERSION 11.00.3000

In the result set you can notice there is a row with information TABLE_LENGTH and the value is 128. There are few other information related to server is also listed in the result set.

Though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character. You can read the detailed blog post over here Maximum Allowable Length of Characters for Temp Objects is 116 – Guest Post by Balmukund Lakhani.

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

SQL SERVER – Select Columns from Stored Procedure Resultset

It is fun to go back to basics often. Here is the one classic question:

“How to select columns from Stored Procedure Resultset?”

Though Stored Procedure has been introduced many years ago, the question about retrieving columns from Stored Procedure is still very popular with beginners. Let us see the solution in quick steps.

First we will create a sample stored procedure.

CREATE PROCEDURE SampleSP
AS
SELECT
1 AS Col1, 2 AS Col2
UNION
SELECT
11, 22
GO

Now we will create a table where we will temporarily store the result set of stored procedures. We will be using INSERT INTO and EXEC command to retrieve the values and insert into temporary table.

CREATE TABLE #TempTable (Col1 INT, Col2 INT)
GO
INSERT INTO #TempTable
EXEC SampleSP
GO

Next we will retrieve our data from stored procedure.

SELECT *
FROM #TempTable
GO

Finally we will clean up all the objects which we have created.

DROP TABLE #TempTable
DROP PROCEDURE SampleSP
GO

Let me know if you want me to share such back to basic tips.

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

SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

Here is a very common question which I keep on receiving on my facebook page as well on twitter.

“How do I insert the results of the stored procedure in my table?”

This question has two fold answers – 1) When the table is already created and 2) When the table is to be created run time. In this blog post we will explore both the scenarios together.

However, first let us create a stored procedure which we will use for our example.

CREATE PROCEDURE GetDBNames
AS
SELECT
name, database_id
FROM sys.databases
GO

We can execute this stored procedure using the following script.

EXEC GetDBNames

Now let us see two different scenarios where we will insert the data of the stored procedure directly into the table.

1) Schema Known – Table Created Beforehand

If we know the schema of the stored procedure resultset we can build a table beforehand and execute following code.

CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
INSERT INTO #TestTable
EXEC GetDBNames
-- Select Table
SELECT *
FROM #TestTable;

The disadvantage of this code is that if due to any reason the stored procedure returns more or less columns it will throw an error.

2) Unknown Schema – Table Created at Runtime

There are cases when we do know the resultset of the stored procedure and we want to populate the table based of it. We can execute following code.

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetDBNames')
-- Select Table
SELECT *
FROM #TestTableT;

The disadvantage of this code is that it bit complicated but it usually works well in the case of the column names are not known.

Just note that if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

I will be interested to know which of the above method do you use in your projects? and why?

Click to Download Scripts

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

SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Here is one of the very common question I receive on SQLAuthority Facebook page. I usually answer them on Facebook but this one I find it very interesting so I decided to answer here.

“There are few of the stored procedure when I try to execute they return following message. Would you please explain what does it mean and is it alright to receive them?

The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.”

Very interesting question and before I explore it further let us answer execute following stored procedure.

-- First Stored Procedure
CREATE PROCEDURE TestSP
AS
DECLARE
@MyFirstParam INT
-- Additional Code
RETURN (@MyFirstParam)
GO

Now execute the stored procedure with following code.

EXEC TestSP
GO

When you execute the stored procedure it will return following message:

The ‘TestSP’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

If you notice this message, it is clear that this message is due to only one reason – we have declared a variable inside the scope of the Stored Procedure and we have assigned no value to the same procedure before we return the same variable. When any variable is not assigned any value it is by default assumed as a NULL. By Design stored procedures are not allowed to return the NULL value. Hence the value is automatically converted to zero (0) and returned.

We can verify above logic by running the following code:

DECLARE @TestVar INT
EXEC
@TestVar = TestSP
SELECT @TestVar TestVar
GO

You can see that when you run above code, instead of returning NULL value we get value of Zero (0) and in the message field we get above message.

Alternate Code

To avoid this situation you can modify above stored procedure to not return the value null. You can use ISNULL function before returning the values back from the stored procedure.

CREATE PROCEDURE TestSP
AS
DECLARE
@MyFirstParam INT
-- Additional Code
RETURN (ISNULL(@MyFirstParam,0))
GO

Above change in the code will automatically covert the NULL to Zero (0) and it will not show the above message.

Which one is better?

Now the question is which method is better – to use ISNULL and covert it to Zero or let SQL handle this and message show up. Personally I prefer to use ISNULL as that gives me clear idea and there is no ambiguity in my code. I will not have to write additional code to handle NULL when it shows up.

What is your opinion? Please share in the comment area.

Click to Download Scripts

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

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)