SQL-CLR For Beginners: Part-5: Call Function and Procedure From Other Procedure

Welcome to the SQL-CLR for Beginners article series. In this series we are discussing how to write Stored Procedures and functions on top of the CLR. If you are new to this series then here are links to the previous articles.

• SQL CLR For Beginner: Part 1: Create Stored Procedure in SQL-CLR Project
• SQL CLR For Beginner: Part 2: Create Trigger in SQL-CLR Project
• SQL CLR For Beginner :Part-3: Create function in SQL-CLR Project
• SQL CLR For Beginner: Part-4 :Consume C# Class from Stored Procedure

In today's article we will learn how to call a Stored Procedure and User Defined Functions ( UDF in Database) from a Stored Procedure. If you have a small understanding of PL/SQL then you must know that it's very possible to call another Stored Procedure and function from one Stored Procedure in PL/SQL.

Now, we will see how it's true for Database CLR applications. Let's begin.

Create one SQL CLR project in VisualStudio. If you follow the previous article thyen you know how to provide a database and server information to project in time of creation. So, here I am skipping those concepts.

Call Stored Procedure from another Stored Procedure

We will hope that you have successfully created one blank SQL-CLR project.

Right-click on TestScript and add one Stored Procedure.



In the next step you will get the following screen that will prompt you for a procedure name. Give a suitable name and proceed.



Once you click the "Add" button, it will create a blank Stored Procedure. Now edit the code as in the following.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void Procedure_1()

    {

        SqlContext.Pipe.Send("This is First Stored Procedure");

        Procedure_2();

    }

 

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void Procedure_2()

    {

        SqlContext.Pipe.Send("This is Second Stored Procedure");

    }

 

};

Here we have created two Stored Procedures and from Procedure_1() we are calling Procedure_2(). It's very similar to normal C# function calls. Now we need to deploy the application.

We will deploy this project in SQLServer 2008 and for that we need to change the target .NET framework to 3.5. Go to the project's property and change it.



Go to the Build option and select Deploy Solution.



Once it is deployed successfully, it will show a success message.

Now open SQL Server Management Studio and select the proper database.



Call Procedure_1 by writing EXEC Procedure_1. And the following is the output screen.



Call function from Stored Procedure

Now, we will see how to call a User Defined Function from a Stored Procedure. Add one User Defined Function to the existing application. Right-click on TestScript and add one User Defined Function.



In the following we have created one simple database function (very similar to C# functions) . Note that we are returning a SqlString from the function. And this value will be returned to the Stored Procedure.
 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString MyFunction()

    {

        // Put your code here

        return new SqlString("This is user defined function");

    }

};

Modufy exhisting Stored Procedure as in the following
 

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void Procedure_1()

    {

        SqlString ReturnValue = UserDefinedFunctions.MyFunction();

        SqlContext.Pipe.Send(ReturnValue.ToString());

    }

 

};

Here is the output.



Conclusion

Here we have explained how to call a procedure and function from an other CLR Stored Procedure. The next few articles will explain a few more concepts on this topic.