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

Introduction

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

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, you must know that it's 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, you know how to provide database and server information to the project at the time of creation. So, here I am, skipping those concepts.

How to Call a 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, so 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.

How to call a user-defined function from a Stored Procedure?

We will now 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");
    }

};

Modify existing Stored Procedures 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 another CLR Stored Procedure. Read more about this series here-