Help With Delete Procedure?
Mar 18, 2008
Hi, I am kinda stuck on a delete stored procedure in my project and I was wondering if you could give me a hand.
My table is like this: Image <----FKconstraint ----- ImageNote --------FKconstraint------>Note.
What I want to do is delete all the notes linkeds to and Image through ImageNote when I am calling the storedprocedure DeleteImage:
So far I did something like this:
DELETE FROM [ImageNote] WHERE [Image Id]=@Id
DELETE FROM [Note] WHERE Id IN (SELECT [Note Id] FROM [ImageNote] WHERE [Image Id]=@Id)
DELETE FROM [Image]
WHERE (Id = @Id)
RETURN
Obvisouly the second DELETE statement won't work as ""SELECT [Note Id] FROM [ImageNote] WHERE [Image Id]=@Id"" won't return a thing at that time. However I have to delete ImageNote first due to the constraint.
A friend told me I should try a trigger, but perhaps there would be something easier to do with some kind of array?
View 7 Replies
ADVERTISEMENT
Dec 5, 2006
Hello,I created a delete procedure which is working but I still have aproblem.When I delete a localized content from dbo.by27_ContentLocalized givena ContentName and ContentCulture I want to check if this is the onlyrecord in ContentLocalized for that ContentName.If it is then I also want to delete the record in dbo.by27_Contentwhich has that ContentName.How can I do this?Thanks,MiguelHere is my DELETE procedure:-- Define the procedure parameters@ContentCulture NVARCHAR(5),@ContentName NVARCHAR(100)AS-- Allows @@ROWCOUNT and the return of number of records whenExecuteNonQuery is usedSET NOCOUNT OFF;-- Declare and define ContentIdDECLARE @ContentId UNIQUEIDENTIFIER;SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName =@ContentName-- Check if ContentId is Not NullIF @ContentId IS NOT NULL BEGIN -- Check if ContentId is Null IF @ContentCulture IS NULL BEGIN -- Delete all localized contents from dbo.by27_ContentLocalized DELETE FROM dbo.by27_ContentLocalized WHERE ContentId = @ContentId -- Delete content from dbo.by27_Content DELETE FROM dbo.by27_Content WHERE ContentName = @ContentName; END ELSE -- Delete localized content from dbo.by27_ContentLocalized DELETE FROM dbo.by27_ContentLocalized WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture) END
View 4 Replies
View Related
Dec 3, 2006
Hello, I created a DELETE Stored Procedure in SQL 2005. When calling this procedure from my server code (VB.NET in my case) I need to know if the record was deleted or not. How should I do this? Should I make the procedure to return True or False? If yes, how can I do this? My Stored Procedure is as follows (I think it is ok): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Content_DeleteContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Check if ContentId is Null IF @ContentCulture IS NULL BEGIN -- Delete all localized contents from dbo.ContentLocalized DELETE FROM dbo.ContentLocalized WHERE ContentId = @ContentId -- Delete content from dbo.Content DELETE FROM dbo.Content WHERE ContentName = @ContentName; END ELSE -- Delete localized content from dbo.ContentLocalized DELETE FROM dbo.ContentLocalized WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture) END GO Thanks, Miguel
View 7 Replies
View Related
Jul 25, 2001
Hi,
I need to create a stored procedure which would
delete just the photo of a student once he graduates.
I found out that a delete SQL statement will delete a whole row.
Is there a way to just delete the Photo and leave the rest of the data
untouched.
Thanks.
Regds,
Ran
View 1 Replies
View Related
Jul 14, 2004
I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table
Please help!
************************************************** ***
My contrat DDL is follows
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
************************************************** ***
My insert stored procedure is follows
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char],
@contactId[int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @contactId = contactId
and @studentId = studentId)
begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
go
View 1 Replies
View Related
May 31, 2006
I want to delete some rows in a table using a Stored procedure. I am using MS sql 2000 using Enterprise manager. I want to delete all rows that have col. status listed as shipped.
/Create Procedure [DeleteOld] AS
/
/Delete Equipment
/FROM Yahoo
/WHERE Status <> 'shipped'
I have two rows of data, one row has a col. names status, and in that row of data says "shipped". The other row says "hold" in the place of "shipped".
The procedure is written, and is setting under that database ready to be run. I do not get any error's when I excute the procedure from SQL query, but it just does nothing when run!
I need help to make this procedure work properly
Thank You,
Ernie
P.S. I am new to this forum!
View 5 Replies
View Related
Sep 24, 2007
i have accidentally deleted a stored procedure is there anyway of getting this back, just found out there is no back up
View 7 Replies
View Related
Mar 9, 2008
Hi, I'm having a hell of a time trying to get a delete query to work properly. I have an existing query that finds records in the database that have expired and now I want to delete those records. I tried wrapping a delete around it, but it deletes everything.
How can I delete the results of this procedure? Thanks!
SELECT rssName, [Days Old] FROM
(SELECT rssName, DATEDIFF(D, timestamp, getdate()) AS [Days Old]
FROM tblRSSFeeds) AS feeds
WHERE [Days Old] > @expireTime
View 4 Replies
View Related
Aug 8, 2007
Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.
my PERSON table is this below:
ID name fname ownerid id2
1 a b
2 c c
3 e f
4 a b 1 10
5 c c 2 11
I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname
select * from ( Select name ,fname, count(1) as cnt from PERSON group by
name,Fname ) where cnt > 1
ID name fname ownerid id2
1 a b
4 a b 1 10
2 c c
5 c c 2 11
With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..
thanks
alex
View 5 Replies
View Related
Sep 24, 2007
Table Strucure - :
TestEven(TestNo,EmpNo,TestDate)
I jst want to write a stored procedure to delete all data which is 2 years old. thank you in adavance.
Hussain
View 3 Replies
View Related
Jun 1, 2007
I have the following sp:@TABLE_NAME varchar(255),
@CONTROL_ID int
AS
DECLARE @sql varchar(4000)
SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = 5'SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = ' + @CONTROL_ID + ''
EXEC (@sql)
When I use the the first SELECT line, it works great.When I use the second dynamic select, SQL raises an error:
Syntax error converting the varchar value 'DELETE FROM [TABLE_SETTINGS] WHERE CONTROL_ID = ' to a column of data type int.What is wrong?
View 2 Replies
View Related
Apr 18, 2008
I have 3 tables...TableA, TableB, TableC TableA - Personal InformationPersonalInfoId (Primary) , First Name,Last NameTableB - Personal Information To Department IDReferenceID, FKPersonalInfoId, FKDepartmentIdTableC - DepartmentDepartmentId, DepartmentNameI am coding Asp.Net VB using VWD express with Sql Server Express. I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger. I never have used a Trigger and it is new to me. Can someone point me a way on how to use one in this case of my deleting scenario. Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid. In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department. Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure. Hope that made sense.Thanks.
View 2 Replies
View Related
Apr 26, 2001
Hi
I'm new to SQL. The primary keys of the tables I use or not autonumbered.
I need to insert data and delete data. When I need to insert data al the primary keys should change. example
1 tim
2 frank
3 jan
4 klaus
I need to insert a new name at place number 3. So jan get's number 4, Klaus number 5.
The same thing goes if I want to delete a row
How can this be done with a stored procedure??? anybody an idea
Or are there script available for things like this?
Tim
View 1 Replies
View Related
Dec 5, 2006
How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script..
Code:
CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw
/***************************************************************
** CREATED: 12/04/2006
****************************************************************
** DESCRIPTION: Clean the tblSoldRaw by deleting any data that
** is over 90 days old. We need keep the database
** as small as possible so the performance won't
** suffer on the server's and the client's machine.
**
****************************************************************
** NOTES: Hooked up to BookItOut Data Importer Windows Service
****************************************************************
** MODIFICATIONS:
**
** DATE WHO MODIFICATION DESCRIPTION
** -------------------------------------------------------------
**
***************************************************************/
/***************************************************************
** Uses: tblSoldRaw
***************************************************************/
AS
DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90)
RETURN ROWCOUNT
ERROR_HANDLER:
RETURN -1
SET NOCOUNT OFF
Thanks...
View 1 Replies
View Related
Nov 26, 2013
How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.
CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
[code]...
View 5 Replies
View Related
Feb 26, 2006
Hi, I would like to delete some records in a table older than a specified date using a stored procedure. Can anyone help?
Thank you,
Cynthia
View 5 Replies
View Related
Jul 20, 2005
Hi all,Can anyone help me with a script which would delete files ormove them to a different folder at some scheduled time..!Please.....!!!Thanks in advance...
View 5 Replies
View Related
Apr 30, 2008
help on CREATE stored procedure delete and after insert where not exist
in one stored procedure
in table_B
Code Snippet
CREATE PROCEDURE [dbo].[delete_from_table_B]
@empID varchar(500)
as
DELETE FROM table_B
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0
---HELP from this ponit how to insert ? after where not exist
IF @@ROWCOUNT > 0
BEGIN
insert into
table_B
set (empID,ShiftDate,shiftType)
where not exist
select
empID,ShiftDate,shiftType
from
table_A
table_A
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
999 qqq 21/03/2008 9
222 bbb 02/05/2008 7
222 bbb 03/05/2008 8
222 bbb 04/05/2008 9
222 bbb 05/05/2008 7
222 bbb 06/05/2008 9
222 bbb 07/05/2008 3
222 bbb 08/05/2008 4
222 bbb 09/05/2008 5
333 ccc 03/04/2008 9
333 ccc 04/04/2008 2
TABLE B
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
TNX for the help
View 1 Replies
View Related
Jan 25, 2008
Hopefully someone can help me with this, or tell me if it's even possible for SQL Server 2005. I'm new to this and learning as I go along.
Here's what I want to do - I have a couple of queries running off a linked server. I want to take the results of those queries and insert them into a table in SQL Server, but first I want to delete the current contents of that table. I want to build a stored procedure that I can run monthly to temporarily store this data for review; once I'm ready to review the next month I want the procedure to delete the data in the table and then insert the next month's data. Hopefully that makes sense.
Is it possible to build? Anyone have any examples of such a thing, or can you point me someplace that might be able to help?
Thanks.
View 5 Replies
View Related
Oct 22, 2007
hi every body
can any onehelp me in making a stored procedure to delete a duplicate rows in tables
thanks in advance
View 9 Replies
View Related
Jun 13, 2008
hi guys,I am using SQL server 2005, i created a table of 4 columns. I want to create a stored procedure which will insert delete and modify the table using flag. all the three insert, delete and update must work in a single stored procedure. pls help me out. Thank You.
View 1 Replies
View Related
Apr 29, 2015
I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
[Code] ....
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
View 8 Replies
View Related
Oct 5, 2007
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
Thanks In Advance
MPM
View 1 Replies
View Related
May 7, 2007
Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
end
Is this a good approach design, or its bad???
Thanks all
View 5 Replies
View Related
Sep 3, 2007
I am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?
View 5 Replies
View Related
Feb 5, 2008
spaces problem on multi update delete stored procedure
on the the insert i get it like this
58830803, 55329429, 308962604, 55696314, 309128726
as you see only the first empid=58830803 is without spaces
how to fix this
my code
Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 4 Replies
View Related
May 27, 2007
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
View 1 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
Sep 11, 2015
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
View 0 Replies
View Related
May 19, 2012
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies
View Related
Oct 23, 2004
Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
View 1 Replies
View Related
Nov 13, 2006
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
View 6 Replies
View Related
Nov 17, 2006
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
View 6 Replies
View Related
