Stored Procedure For Archiving The Records In Another Table

Mar 27, 2008

I have two tables called A and B and C. Where A and C has the same schema

A contains the following columns and values
-------------------------------------------
TaskId   PoId   Podate         Approved

   1       2    2008-07-07      No
   3       4    2007-05-05      No
   5       5    2005-08-06      Yes
   2       6     2006-07-07     Yes


Table B contains the following columns and values
-------------------------------------------------
TaskId      TableName   Fromdate     Approved_Status

1                A        7/7/2007     No
3                B       2/4/2006      Yes

Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved  column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column

Pls provide me with full stored procedure code.

View 2 Replies


ADVERTISEMENT

Stored Procedure For Archiving Records

Mar 27, 2008

I have two tables called A and B and C. Where A and C has the same schema

A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved

1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes


Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status

1 A 7/7/2007 No
3 B 2/4/2006 Yes

Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column

Pls provide me with full stored procedure code.


C.R.P RAJAN

View 1 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

Can I Insert Records Into A Table From A Stored Procedure

Oct 9, 2007

I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset.

I use the logic in other sprocs too.

Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like....

INSERT INTO Insurance (Name)
Exec sp_AddNamesList

Thanks,

Mike

View 5 Replies View Related

Create A String Of Records From A Table In A Stored Procedure,

Jul 20, 2005

I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq

View 6 Replies View Related

Moving Records For Archiving

May 18, 2004

I've got two tables, one is an archive of the second (tables are identical). I'd like to migrate records from one to the other (as in, move, insert into one while deleting from the other).

I know I can start a transaction, do an INSERT INTO...SELECT, followed by a DELETE, check rows affected, then closing with a commit transaction (or rollback if the counts don't match), but it seems as though I might be over thinking it. Is this considered the optimal approach?

View 10 Replies View Related

Archiving And Deleting Records (Data)?

Aug 10, 2015

I wrote a script to archive and delete records rom a table back in 2005 and 2009.

I can't seem to get the syntax right. Any sample script to simply archive and delete records?

This is what I have so far.

DECLARE @ArchiveDate Datetime
SET @ArchiveDate = (SELECT TOP 1 DATEPART(yyyy,Call_Date)
FROM tblCall
ORDER BY Call_Date)
--SELECT @ArchiveDate AS ArchiveDate
DECLARE @Active bit

[Code] ....

View 9 Replies View Related

Millions Records Archiving And Delete

Feb 27, 2007

The iussue:Sql 2KI have to keep in the database the data from the last 3 months.Every day I have to load 2 millions records in the database.So every day I have to export (in an other database as historical datacontainer) and delete the 2 millions records inserted 3 month + one day ago.The main problem is that delete operation take a while...involvingtransaction log.The question are:1) How can I improve this operation (export/delete)2) If we decide to migrate to SQL 2005, may we use some feature, as"partitioning" to resolve the problems ? In oracle I can use the "truncatepartition" statement, but in sql 2005, I'm reading, it cant be done.This becouse we can think to create a partition on the last three mounts tosplit data. The partitioning function can be dinamic or containing afunction that says "last 3 months ?" I dont think so.May you help usthank youMastino

View 5 Replies View Related

Managed Procedure To Automate Archiving Files In A Database

Sep 3, 2007

I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date  is greater than todays date then archive the file by moving it to an archive folder.  I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?

View 1 Replies View Related

Archiving Table

Feb 14, 2002

Hi,

We have a table of size greater than 200GB, so all the SQL is very slow.

Is there any way of doing table partition, If so How?

Is there any better way of archiving a portion of table used currently which can be restored later when is required, If so How ?

Thanks
John Jayaseelan

View 5 Replies View Related

Using Stored Procedure Records

Jun 2, 2008

How can I use the data returned from a stored procedure within another stored procedure?

For example, I trying to something along these lines:
select * from tbl_Test
union all
select * from (exec sp_Test)

View 2 Replies View Related

SQL 2012 :: Deleting / Archiving From 100 GB Table

May 20, 2014

I would like to archive /delete data from a 100GB table. I have to delete on the basis of date column. Date column has been added to clustered index But not having an individual non clustered index.

My estimated execution plan shows a index scan.

Should I impose an non-clustered index on the date column then try to archive /delete after confirming the index seek is used in estimated execution plan or, is there any other method to do this?

View 3 Replies View Related

Inserting Records Via Stored Procedure

Mar 23, 2006

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
 
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
 
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
 
Please help, I am a newb and lost...as you can tell from my code...

View 4 Replies View Related

Return A Set Of Records From A Stored Procedure

Nov 14, 2000

What is the preferred method of returning a set of records from a stored procedure? Could you please provide a short example?

Thanks ahead of time!

G.

View 3 Replies View Related

Counting Records In A Stored Procedure

Jul 14, 2004

I am trying to count records in my stored procedure. Can someone please help me.

these are the two procedures I am using

Alter Procedure usp_rptQualityReport As

SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber

WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))

ORDER BY
tblFailureReasons.FailureReason



Alter Procedure usp_rptQualityReport2 As

exec usp_rtpQualityReport

SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,

(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel


FROM usp_rptQualityReport;

View 3 Replies View Related

Stored Procedure To Insert Records Into Next Row

Sep 20, 2014

Creating a stored procedure to insert records into multiple rows.

Let's say we have 3 colums in procedure to insert, col3 has a max limit of 1000, if col3 data exceeds limit it has insert into next row and vice versa.

View 1 Replies View Related

How Many Records A Stored Procedure Returns?

Oct 11, 2007

Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.

View 6 Replies View Related

Get Records After Executing A Stored Procedure

Dec 1, 2006

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@countRun =?,
@startTime =?,
@endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

View 9 Replies View Related

Extracting Data From A Table For Archiving Purposes

Sep 22, 1999

Hello,

I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.

My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.

Sorry this is so long...

Thanks,

Brian R
WESNet Systems, NOC

View 1 Replies View Related

Transact SQL :: Syntax Check For Archiving A Table

May 12, 2015

I am trying to write a SQL Server query that archives x-days old data from [Archive].[TestToDelete] to [Working].[TestToDelete]table. I want to check that if the records on ArchiveTable do not exist then insert then deleted...which will be converted to a proc later.. archives x-days old data from [Working].[TestToDelete] to [Archive].[TestToDelete] table */Here is the table definition, it is the same for both working and archive tables. There are indexes on: IpAddress, Logdate, Server, User and Sysdate (clustered).

CREATE TABLE [Archive].[TestToDelete]([Server] [varchar](16) NOT NULL,[Logdate] [datetime] NOT NULL,[IpAddress] [varchar](16) NOT NULL,[Login] [varchar](64) NULL,[User] [varchar](64) NULL,[Sysdate] [datetime] NULL,[Request] [text] NULL,[Status] [varchar](64) NULL,[Length] [varchar](128) NULL,[Referer] [varchar](1024) NULL,[Agent] [varchar](1024) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]/* the syntax, which will be converted to proc is: */SET NOCOUNT ON;DECLARE @Time DATETIME,@R INT,@ErrMessage NVARCHAR(1024)SET @R = 1/* set @Time to one day old data */SET @Time =

[code]....

View 8 Replies View Related

Stored Procedure Producing Duplicate Records

Feb 11, 2004

Hi,

I have written the following stored procedure:

alter proc GetProducts

@prodcatint=null
as

select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid

return

My Results are:

ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58


In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.

Does anybody have any suggestions.

Thank you in advance,

poc1010

View 4 Replies View Related

Calling A Stored Procedure On Multiple Records

Apr 18, 2006

I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made:
CREATE PROCEDURE dbo.GetUserStats @UserID BIGINT AS
DECLARE @TempTable TABLE
(
UserID BIGINT,
UserName VARCHAR(60),
TotalAmt FLOAT
)

INSERT INTO @TempTable (UserID, UserName, TotalAmt)
SELECT u.RecID, u.LastName + ', ' + u.FirstName,
(SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @UserID)
FROM Users u
WHERE u.RecID = @UserID

SELECT * FROM @TempTable
GO

So if I execute this amount entering a single ID, it returns a single row for that user:

UserID UserName TotalAmt
--------------------------
1 Doe, John 100.00


What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users:

UserID UserName TotalAmt
--------------------------
1 Doe, John 100.00
2 Smith, Bob 123.45
3 Blow, Joe 150.55


Is there a way to re-use a stored procedure within another, based on the results of a query?

View 7 Replies View Related

Only Run Stored Procedure If Update Records Exist?

Oct 6, 2015

I am trying to put together a Stored Procedure that runs other SP's based on a records found condition. Sometimes my update table either by deleting duplicates or for other reasons is empty. Running a SP to update records on a table is not needed.

IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT1')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT1_Trace_Data
END

IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT2')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT2_Trace_Data
END

IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'FULF')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_FULF_Trace_Data
END

View 1 Replies View Related

Count Records When RecordSource Is A Stored Procedure

Jul 20, 2005

I have a stored procedure named mySP that looks basically like this:Select Field1, Field2From tblMyTableWhere Field 3 = 'xyz'What I do is to populate an Access form:DoCmd.Openform "frmMyFormName"Forms!myFormName.RecordSource = "mySP"What I want to do in VBA is to open frmContinuous(a datasheet form) ifmySP returns more than one record or open frmDetail if mySP returnsonly one record.I'm stumped as to how to accomplish this, without running mySP twice:once to count it and once to use it as a recordsource.Thanks,lq

View 1 Replies View Related

SQL Stored Procedure Not Returning Expected Records.

Oct 24, 2006

I am running a SP using this code:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly]

(

@postcode nvarchar(50)

)

AS

SET NOCOUNT ON;

SELECT

Cust_Address.Cust_Address_ID,

Cust_Address.Cust_Address_1,

Cust_Address.Cust_Address_2,

Cust_Address.Cust_Address_Post_Code,

Cust_Address.Cust_Post_Town,

Post_Town.Post_Town_ID,

Post_Town.Post_Town_Name,

Post_Town.Post_Town_Priority

FROM Cust_Address

INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID

WHERE Cust_Address.Cust_Post_Town LIKE @postcode



The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all.



Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine.



Cheers Matt



View 1 Replies View Related

Stored Procedure Does Not Return Records Error In VB

Jul 28, 2007

I am using SQL Server 2005 std edition SP2 on a Windows 2003 server. I have created a simple stored procedure that deletes all records from two tables:

BEGIN

SET NOCOUNT ON

DELETE FROM dbo.table1

DELETE FROM dbo.table2

END

Executing the procedure generates the message "The stored procedure executed successfully but did not return records." which produces an error condition when run from an Access 2007 VB module using the DoCMD function:

On Error GoTo ErrorExit
DoCmd.SetWarnings False
DoCmd.OpenStoredProcedure "dbo.myStoredProcedure"

When the above VB code is run (it's part of an Access 2007 adp project connected to the SQL Server database) it takes the error exit and returns the "... did not return records." message. How can I avoid this??
Thanks,
Paul

View 10 Replies View Related

Limit The Number Of Records Returned In Stored Procedure.

Aug 17, 2007

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
 Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

View 2 Replies View Related

Stored Procedure Inserting Duplicate Records Randomly

Feb 1, 2005

I have a web app that calculates tax filing status and then stores data about the person.

Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.

Any help would be greatly appreciated.

There are many sqlcmdInsert.Parameters("@item").Value =

cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()

And that is it.

View 6 Replies View Related

SQLdatasource Wired To A Stored Procedure Not Returning Records.

Dec 9, 2005

Hi, and thanks in advance.
I have VWD 2005 Express and SQL 2005 Express running.
I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting...
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded.
Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle.
Any help would be appreciated!
 
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString %>"
ProviderName="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString.ProviderName %>"
SelectCommand="spDisplayServiceOrders" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" EnableCaching="True" cacheduration="300" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:ControlParameter ControlID="RadioButtonList1" ConvertEmptyStringToNull="False"
DefaultValue="2005-11-1" Name="SDate_Entered" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
 
 
ALTER PROCEDURE [dbo].[spDisplayServiceOrders]
(
@SDate_Entered SmallDateTime,
@SClosed nvarchar(50)= NULL
)
AS
If @SClosed IS NULL
BEGIN

SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
Order by SDate_Entered DESC
END
ELSE
BEGIN

SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
and SClosed = @SClosed
Order by SDate_Entered DESC

END

View 2 Replies View Related

Stored Procedure Using A Parameter Default To Select ALL Records - Help Please

Jan 6, 2006

Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice  (@searchpostcode as nvarchar(100))  AS
SELECT   dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM         dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN                      dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND                       dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN                      dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE   dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice   G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
 
 
 
 

View 9 Replies View Related

Executing A Stored Procedure As Times As The View Has Records

Oct 6, 2007

i have a stored procedure with one coming id parameter





Code BlockALTER PROCEDURE [dbo].[sp_1]
@session_id int
...







and a view that holds these @session_id s to be sent to the stored procedure.

how could i execute this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records with different ids.

View 1 Replies View Related

Transact SQL :: Stored Procedure To Return Multiple Records

Oct 21, 2015

I am looking out for sample  stored procedures returning multiple records

Example: GetOrderDetailsByOrderId

The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.

View 4 Replies View Related

How To Update Group Of Records In SQL Statement Or Stored Procedure

Dec 5, 2007

I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.

How could I write this in a stored procedure or a SQL statement if possible?

Thanks.

Kevin


SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count,
dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1
FROM dbo.ReportTable INNER JOIN
dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND
dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type
GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget
HAVING (dbo.ReportTable.Budget < 1)
ORDER BY dbo.ReportTable.ProjectNo





ProjectNo

Category

Type

Count

Budget

BudgetTotal


100143

Travel

Travel, Meals, No Report IRS

2

0

300.27


100146

Travel

Travel Costs, Training (all)

1

0

300.27


100164

Supplies & Materials

Supplies, Educational

1

0

300.27


100167

Equipment

Eq NonCapital Desktop Comp

1

0

300.27


100170

Faculty Salaries

FB, Faculty

11

0

300.27


100170

Faculty Salaries

Salary, Faculty, T&R FT

11

0

300.27


100170

Wages

Wages, Student

2

0

300.27


100171

Faculty Salaries

FB, Faculty

19

0

300.27


100171

Faculty Salaries

Salary, Faculty, T&R FT

19

0

300.27


100176

Scholarships & Fellowships

Fell, Assist, Out, Grad

1

0

300.27


100177

Scholarships & Fellowships

Fell, Assist, In, Grad

1

0

300.27


View 5 Replies View Related







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