Thanks in advance for your help. I need a Stored Procedure that will do the following steps:
1. delete a single record from table GROUP
2. delete all records from table SUBGROUP with a matching 'groupID'
from the deleted GROUP
2. as each record from SUBGROUP is deleted there are multiple associated
records in the table PROFILE with a matching 'subGroupID' that also
need to be deleted
Table definitions: ---------------------------------------------------
CREATE TABLE [dbo].[tblGroup](
[groupID] [int] IDENTITY(100000 ,1) NOT NULL,
[groupName] [varchar](max) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSubGroup](
[subGroupID] [int] IDENTITY(100000 ,1) NOT NULL,
[subGroupName] [varchar](max) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
[groupID] [int] NOT NULL,
[profileID] [int] NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProfile](
[profileID] [int] IDENTITY(100000 ,1) NOT NULL,
[profileName] [varchar](max) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
[subGroupID] [int] NOT NULL,
) ON [PRIMARY]
2 9467
Thanks in advance for your help. I need a Stored Procedure that will do the following steps:
1. delete a single record from table GROUP
2. delete all records from table SUBGROUP with a matching 'groupID'
from the deleted GROUP
2. as each record from SUBGROUP is deleted there are multiple associated
records in the table PROFILE with a matching 'subGroupID' that also
need to be deleted
Table definitions: ---------------------------------------------------
CREATE TABLE [dbo].[tblGroup](
[groupID] [int] IDENTITY(100000 ,1) NOT NULL,
[groupName] [varchar](max) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSubGroup](
[subGroupID] [int] IDENTITY(100000 ,1) NOT NULL,
[subGroupName] [varchar](max) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
[groupID] [int] NOT NULL,
[profileID] [int] NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProfile](
[profileID] [int] IDENTITY(100000 ,1) NOT NULL,
[profileName] [varchar](max) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
[subGroupID] [int] NOT NULL,
) ON [PRIMARY]
Why you need a procedure to do that in case if it is going to be one time process?
And is there any primary key - foreign key relation shp between these tables?
here is a quick solution - we could add error checking between deletes
create procedure dbo.usp_DeleteR ecords
@id_to_delete int
as
delete from [dbo].[tblGroup]
where [groupID] = @id_to_delete
delete from [dbo].[tblProfile]
where [subGroupID] in (select [subGroupID] from [dbo].[tblSubGroup]
where [GroupID] = @id_to_delete)
delete from [dbo].[tblSubGroup]
where [GroupID] = @id_to_delete
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: deprins |
last post by:
Hello,
I have wrote a stored procedure but its real slow. Its activated by a
button on web page but its takes to long to process and the web server
gives a timeout message after 5 minutes.
Is there anyway to speed up this stored procedure? What am I doing
wrong here?
------------------------------------------------------------------------------
|
by: Jarrod Morrison |
last post by:
Hi All
I was wondering if there is a way to call a stored procedure from inside
another stored procedure. So for example my first procedure will call a
second stored procedure which when executed will return one record and i
want to use this data in the calling stored procedure. Is this possible ?
Thanks in advance
|
by: Brad H McCollum |
last post by:
I've looked through many suggestions and partial examples all over
this newsgroup and still am not coming up with anything that does
specifically what I'm wanting to accomplish.
I'm writing a VB 6.0 application which uses SQL Server as the
back-end.
Here's an example of what I'm wanting to do...
A user accessing the VB GUI attempts to open a certain form. Code
|
by: Eric E |
last post by:
Hi all,
I am using an Access client linked to a PG 7.4 server via ODBC.
I have a stored proc on the server that inserts rows into a
table.particular table, accomplished via an INSERT
within the body of the stored proc. The procedure does not explicitly
commit this data, as no transactions are invoked.
The problem is that Access will not modify these records via table or
form view, giving its generic "Write conflict: another user has...
|
by: Catch_22 |
last post by:
Hi,
I have a large SQL Server 2000 database with 3 core tables.
Table A : 10 million + records
Table B : 2 million + records
Table C : 6 million + records
One of the batch tasks that I have to perform firstly builds a list of
all keys for records from each of the three tables that I need to
| |
by: vikram.mankar |
last post by:
I have a stored procedure thats transferring/processing data from one
table to two different tables. The destination tables have a unique
value constraint as the source tables at times has duplicate records
and this will prevent the duplicates from being reported. When the
stored procedure (which includes a cursor) is executed through query
analyzer, it runs fine, and reports an error everytime it sees a
duplicate value (as expected). It...
|
by: yin_n_yang74 |
last post by:
I am new to SQL and SQL Server world. There must be a simple solution
to this, but I'm not seeing it. I am trying to create a crystal
report (v8.5) using a stored procedure from SQL Server (v2000) in
order to report from two databases and to enable parameters.
When I create the stored procedure, it joins multiple one-to-many
relationship tables. This results in repeated/duplicate records. Is
this an issue that should be solved within...
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
by: padmaneha |
last post by:
Hi
I have created two tables 'TrainsMaster' & 'TransArrvlDepinfo'
Columns which I have created in 'TrainsMaster' are 'trainName,TrainNo, StartStaionId, & EndstationId'
Columns which I have created in ''TransArrvlDepinfo' are 'Stationcode, TrainNo, Arrvaltime,Depttime'
I have to delete few trains from 'TrainsMaster' for which the Startstationid or Endstationid are nulls which comes to 50 records
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |