Stored Procedure and function to select and split using parameter

Transact-SQL
1

I have these tables
departmental_course
departmental_course

CREATE TABLE [Admin].[departmental_course](
[departmental_course_id] [int] IDENTITY(1,1) NOT NULL,
[departmental_course_code] nvarchar NOT NULL,
[course_master_id] [int] NULL,
[department_id] [int] NOT NULL,
[departmental_course_status] [int] NOT NULL
)

I want to select departmental_course_code from departmental_course table where course_master_id is NULL. It will also pick the related department_id. Then split the departmental_course_code (comma separated), take each of the course codes to course_master table (the field is [course_master_code] nvarchar NOT NULL). It will equate the splitted departmental_course_code with course_master_code using department_id as parameter.

Please how do I achieve this using stored procedure and function

2

STRING_SPLIT (Transact-SQL)

3 
ALTER FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000)) 
RETURNS table
AS
RETURN (     
    WITH splitter_cte AS (       
        SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos       
        UNION ALL      
        SELECT CHARINDEX(@sep, @s, pos + 1), pos       
        FROM splitter_cte       
        WHERE pos > 0     
    )     
    SELECT SUBSTRING(@s, lastPos + 1,                      
        case when pos = 0 then 80000                      
        else pos - lastPos -1 end) as OutputValues     
        FROM splitter_cte   
        )
)
4

Lot of options. .in below article

5

hi

I have added "EXTRA" data
null [course_master_id] [department_id] 3 values

I know there is more work in Stored Procedure
I need to understand what you want to do in stored procedure

Create Data Script 
USE tempdb 
go 

drop table [departmental_course]
go 

CREATE TABLE [departmental_course](
[departmental_course_id] [int]  NOT NULL,
[departmental_course_code] nvarchar(100) NOT NULL,
[course_master_id] [int] NULL,
[department_id] [int] NOT NULL
)
go 

drop table course_master 
go 

create table  course_master 
(
course_master_code nvarchar(100) not null ,
)
go 

insert into [departmental_course] select 1,'EDU,PES,ENG,GSE',NULL,2 
insert into [departmental_course] select 1,'PES,ENG,GSE',NULL,3 
insert into [departmental_course] select 2,'GST 101',26,15
insert into [departmental_course] select 3,'GST 105',27,15
insert into [departmental_course] select 4,'GST 107',28,15
go
Create Function to Split 
create  FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000)) 
RETURNS table
AS
RETURN (     
    WITH splitter_cte AS (       
        SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos       
        UNION ALL      
        SELECT CHARINDEX(@sep, @s, pos + 1), pos       
        FROM splitter_cte       
        WHERE pos > 0     
    )     
    SELECT SUBSTRING(@s, lastPos + 1,                      
        case when pos = 0 then 80000                      
        else pos - lastPos -1 end) as OutputValues     
        FROM splitter_cte   
        )
go
drop create execute Stored Procedure 
drop PROCEDURE StoredProcedure_DepartmentalCourse
go 

CREATE PROCEDURE StoredProcedure_DepartmentalCourse
AS  
    SET NOCOUNT ON;  
    select a.departmental_course_id,a.course_master_id,a.department_id,u.* from departmental_course a 
Cross apply Split(',',a.departmental_course_code) u  
GO  


exec StoredProcedure_DepartmentalCourse
go
OutPut

image
image.png1324×763 11.9 KB