Solved

how to delete rows from table using table type

Posted on 2014-07-25
2
123 Views
Last Modified: 2014-07-31
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.
0
Comment
Question by:yadavdep
2 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
not really sure to fully understand what you want but would it be:

delete
from Table1 as T1
inner join table2 as T2
on t2.key = t1.key
and t2.tabletypefield = 'valueyouwanttodelete'
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 140 total points
Comment Utility
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
AS
IF @tableType = 'A'
BEGIN
	DELETE 
	FROM MyTableA
	WHERE itemID = @itemID
END
ELSE IF @tableType = 'Z'  -- always use an IF test. This allows for future expansion
BEGIN
	DELETE
	FROM MyTableZ
	WHERE itemID = @itemID
END

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now