how to delete rows from table using table type

I have two tables from which I need to remove rows.

I have created a table type which I want to use in SP to remove rows from these two table.

But I am not able to figure out how to use this table type  in the delete query.
Who is Participating?
Brendt HessConnect With a Mentor Senior DBACommented:
If table type is used to decide which table to delete from, then you either will need to write Dynamic SQL (NOT recommended for this), or trigger two delete queries from one stored procedure, based on the table type value, something like this:

CREATE PROCEDURE deleteSomething 
	@tableType varchar(1),
	@itemID int
IF @tableType = 'A'
	FROM MyTableA
	WHERE itemID = @itemID
ELSE IF @tableType = 'Z'  -- always use an IF test. This allows for future expansion
	FROM MyTableZ
	WHERE itemID = @itemID

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
not really sure to fully understand what you want but would it be:

from Table1 as T1
inner join table2 as T2
on t2.key = t1.key
and t2.tabletypefield = 'valueyouwanttodelete'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.