Recursive user-defined function in SQL Server example

Recursive user-defined function

create function RecursiveTest(@V int)

returns @t table (i int)

as

   begin

   set @v = @v - 1

   if @v<>0

     insert into @t 

     select @v union 

     select * from dbo.RecursiveTest(@v)

   else

     insert into @t values (0)

   return

   end

How to Call Recursive user-defined function:

select * from dbo.RecursiveTest(10) 

Github Link: https://github.com/adi501/SQL-Server-Examples

Call a SQL function in Entity Framework Core

Step 1: Create .net core MVC project with Entity framework

Step 2:  Create Function in SQL Server

create function funFullName(@firstName varchar(10),@lastName varchar(10))

RETURNS varchar(25)

AS

BEGIN

RETURN @firstName+' '+@lastName

END

Step 3:  Add Connection in "appsettings.json" file 

 "ConnectionStrings": {

    "SQLDB": "Server=.;Database=Dotnet_Core_MVC;Trusted_Connection=True;"

  },

Step 4:  Create Model class in "FuntionOutput.cs" under DBContext/Models folder

namespace Dotnet_Core_MVC.DBContext.Models

{

    public class FuntionOutput

    {

        public string FullName { get; set; }

    }

}

Step 5:  Create class in "DatabaseContext.cs" under DBContext folder

using Dotnet_Core_MVC.DBContext.Models;

using Microsoft.EntityFrameworkCore;

namespace Dotnet_Core_MVC.DBContext

{

    public class DatabaseContext : DbContext

    {

        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)

        {

        }

        //Funtion Call

        public virtual DbSet<FuntionOutput> funFullName { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            modelBuilder.Entity<FuntionOutput>(e => e.HasNoKey());

        }

    }

}

Step 6:  Create Controller "Call_A_SQL_FunctionController.cs"  and call the SQL Function ass below code.

using Dotnet_Core_MVC.DBContext;

using Microsoft.AspNetCore.Mvc;

using Microsoft.EntityFrameworkCore;

using System.Linq;

namespace Dotnet_Core_MVC.Controllers

{

    public class Call_A_SQL_FunctionController : Controller

    {

        private readonly DatabaseContext _databaseContext;

        public Call_A_SQL_FunctionController(DatabaseContext databaseContext)

        {

            this._databaseContext = databaseContext;

        }

        public IActionResult Index()

        {

          ViewBag.functionOutput= _databaseContext.funFullName.FromSqlInterpolated($"select dbo.funFullName('adi','jc') as FullName").FirstOrDefault().FullName;

            return View();

        }

    }

}

Step 7:  Create View and add below code.

<h5>Function Output:</h5>@ViewBag.functionOutput

Step 8:  Open "Startup.cs" class and update "ConfigureServices" method as below.

 public void ConfigureServices(IServiceCollection services)

        {

            services.AddControllersWithViews();

            var ConnectionString = Configuration.GetConnectionString("SQLDB");

            //Entity Framework  

            services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(ConnectionString));

        }

Output:

Github Code: https://github.com/adi501/Dotnet_Core_MVC

How to find recently executed queries in SQL Server

select dest.text as Query,deqs.last_execution_time 

from sys.dm_exec_query_stats as deqs

cross apply sys.dm_exec_sql_text(deqs.sql_handle) as dest

order by deqs.last_execution_time desc

(or)

select dest.text as Query,deqs.last_execution_time 

from sys.dm_exec_query_stats as deqs

cross apply sys.dm_exec_sql_text(deqs.sql_handle) as dest

where dest.text like '%dm_exec_query_stats%'

order by deqs.last_execution_time desc

Split Comma Separated List Without Using a Function in SQL Server

DECLARE @t TABLE
(
EmployeeID varchar(100),
Certs VARCHAR(8000)
)
INSERT @t VALUES ('Param1:','B.E.,MCA, MCDBA, PGDCA'), ('Param2:','M.Com.,B.Sc.'), ('Param1:','M.Sc.,M.Tech.')

SELECT EmployeeID,
EmployeeID+LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)


Output: