Stored Procedure To Delete Data Which Is 2 Yrs Old

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


ADVERTISEMENT

Stored Procedure To Delete And Insert Data

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

Stored Procedure For Delete

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

Little Help On Delete Stored Procedure

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

Stored Procedure Using Delete Row

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

Delete Stored Procedure

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

Need Help With Delete Stored Procedure

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

Dynamic Delete Stored Procedure Question

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

Using Trigger/Stored Procedure (Delete, Insert)?

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

Stored Procedure, Insert, Delete Updat

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

Stored Procedure - Delete Some Rows (table)

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

Stored Procedure For Insert / Update And Delete

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

Delete Records In A Table Using Stored Procedure

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

Need A Stored Procedure To Delete Duplicate Rows

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

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

Insert Delete Modify In A Single Stored Procedure

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

T-SQL (SS2K8) :: One Stored Procedure To Do Add / Update / Select And Delete

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

Delete / Drop Extended Stored Procedure In 2005

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

General Stored Procedure, For Insert, Update, Select, Delete?

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

Spaces Problem On Multi Update Delete Stored Procedure

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

I Have One Stored Procedure For Insert, Update And Delete Operations, I Need Automatically Logged

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

How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?

Oct 8, 2007

How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?

Something like this:

CREATE PROCEDURE TestHardDisk
AS
BEGIN

DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source

OPEN CURSOR HardDisk_Cursor


FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space

WHILE @@FETCH_STATUS = 0
BEGIN

...
END
END

View 6 Replies View Related

The Multi Delete &&amp; Multi Update - Stored Procedure Not Work Ok

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

Getting Data From A Storeed Procedure In A Stored Procedure

Jul 23, 2005

What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer

View 4 Replies View Related

How Do I Call A Stored Procedure To Insert Data In SQL Server In SSIS Data Flow Task

Jan 29, 2008



I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks

View 6 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

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

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

End Stored Procedure If No Data

Oct 15, 2013

I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run.

INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW

INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW

DECLARE @BcpHeader AS VARCHAR (2000)

[Code] ....

View 2 Replies View Related

Retrieving Data Using A Stored Procedure

Mar 31, 2007

I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks!  1 ALTER PROCEDURE [dbo].[GetCustomerById]
2
3 @CustId NCHAR(5),
4 @CustomerName NVARCHAR(50) OUTPUT
5
6 AS
7 BEGIN
8
9 SELECT @CustomerName = ContactName
10 FROM Customers
11 WHERE CustomerId = @CustId
12
13 END
14
15 RETURN
16
17
18
19
20
21
22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection
23 SqlCommand cmd = new SqlCommand();
24 cmd.Connection = conn;
25 cmd.CommandType = CommandType.StoredProcedure;
26 cmd.CommandText = "GetCustomerById";
27
28 SqlParameter paramCustId = new SqlParameter();
29 paramCustId.ParameterName = "@CustId";
30 paramCustId.SqlDbType = SqlDbType.NChar;
31 paramCustId.Direction = ParameterDirection.Input;
32 paramCustId.Value = "ALFKI";
33
34 SqlParameter paramCustomerName = new SqlParameter();
35 paramCustomerName.ParameterName = "@CustomerName";
36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;
37 paramCustomerName.Direction = ParameterDirection.Output;
38
39 cmd.Parameters.Add(paramReturn);
40 cmd.Parameters.Add(paramCustId);
41 cmd.Parameters.Add(paramCustomerName);
42
43 conn.Open();
44 SqlDataReader reader = cmd.ExecuteReader();
45
46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
  

View 6 Replies View Related

Getting Text Data Into XML Stored Procedure

Jan 16, 2008

Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a
local variable because they can't be of type text. I can't pass it into
the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt

View 3 Replies View Related

Data Repeating In Stored Procedure

Jan 31, 2008

Can someone tell me why my stored procedure is repeating the Name in the same column?
Here's my stored procedure and output:
select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name],
Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate,
Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate
from LibraryRequest
join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null 
Output: 
ID      Title                      Name                   Request Date     Ship Date
29     Heads, You Win     Brenda Smith        1/18/2008          1/18/200835     Still More Games    Brenda Smith        1/22/2008          1/22/200851     The Key to..           Brenda Smith  Brenda Smith 1/29/2008 1/29/200852     PASSION...           Brenda Smith  Brenda Smith  1/29/2008 1/29/200853     LEADERSHIP       Brenda Smith   Brenda Smith  1/29/2008  1/29/2008
Going crazy ugh... 
 
 

View 3 Replies View Related

How To Get The ID Of An Inserted Data In A Stored Procedure

Feb 14, 2008

hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure.
for example:
alter procedure [dbo].[AddDetails]
(
@IndustryName nvarchar(50),
@CompanyName nvarchar(50),
@PlantName nvarchar(50),
@Address nvarchar(100),
@Createdby int,
@CreatedOn datetime
)
as
begin
insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn)
insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn)
insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp)
end
 
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this.
Please help me, i need the solution soon.

View 11 Replies View Related

Stored Procedure For Getting Data Into Dropdownlist

Feb 27, 2008

Hi iam working with two dropdownlists,one gets the data dynamically when the pageload.later after selecting particular item in the dropdownlist1 i must get data to the dropdownlist2 depending on 1.
For example:
Dropdownlist1 is for industry and 2 is for company.
when i select particual industry in ddl1 i must get companies based on this industry in ddl2.Both the Industry name and company name are maintained in two different tables industry and company with common field ID.please help me with a stored procedure to sort out this problem...

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved