Delete records using TVP in stored procedure [duplicate]

Question

I have a table called Student

sno    Name    Course    Fee    Section
1      AAA     BCA       10000   A
2      BBB     BCom      9000    B
3      CCC     BTech     12000   B

I created a temp table called Course_tvp_tbl

CREATE TYPE dbo.Course_tvp_tbl AS TABLE (course VARCHAR(25) NOT NULL PRIMARY KEY (course))

and inserted values into this temp table.

course
BTech

Now I want to delete values from student where the course in temp table course column. We can use the in clause directly but, the table volume is big and also my in clause will contain 1000's of course ids. Tried with creating index(non-clustered as we have clustered already) also having issue as its taking more time OR lock request time out is happening as the query run from multiple instances. I want to use the stored procedure with table value parameters.

CREATE PROCEDURE DBO.DELETE_STUDENTS
    @section char(1),
    @course_tbl dbo.Course_tvp_tbl READONLY 
    AS
    BEGIN
        SET NOCOUNT ON;
        DELETE FROM Student where section = @section and course IN (SELECT course FROM @course_tbl)
    END

Is the stored procedure delete query having issue. how can I use the temp table course column in delete query In clause?

Followed the TVP Example and wants to use tvp column in deleting the rows from main table

What's not working with your current code? Apart from you forgot the @ on section? — Dale K
– Dale K, Commented Sep 13, 2023 at 18:44
What is the data type of the section column? Char with no length specified probably won’t do what you need — Martin Smith
– Martin Smith, Commented Sep 13, 2023 at 19:10

marc_s · Accepted Answer · 2023-09-13 20:04:45Z

What you've written should work fine; thousands of records is not really much when it comes to data manipulation in modern relational databases (e.g. SQL Server).

You could also accomplish your goal by doing an inner join between Student and @course_tbl which might gain you a slight performance boost if it can make use of the clustered index on the table variable (I haven't tested query plans for these; it's possible the IN clause would give you the same query plan as the join approach, but generally, if you can get away with it, joins will perform better than IN clauses).

That would look like:

delete s
from Student s
inner join @course_tbl ct
    on s.course = ct.course
where s.section = @section

Also, as pointed out in some of the comments, you probably want to give your @section variable a length (e.g. char(10) instead of char or varchar(30) instead of varchar). Not specifying the length can cause it to be treated as a char(1) which is probably not what you're after.

Collectives™ on Stack Overflow

Delete records using TVP in stored procedure [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related