Updating 2 Table At The Same Time Using Stored Procedure

Sep 14, 2004

G'day everyone...





I have two tables: Category and Product.


CategoryID is the Foreign Key of Product table.


I want to update those tables at the same time using stored procedure.





It will work like this.


If I update the Display column of Category table to have the value of 0, it will also update any records in the Product table -that has the same CategoryID- to have the value of 0 in its Display column.





I just read something about trigger and bit gives me idea that I might use it for this case, but don't know how to write the SQL.





Can anyone please help me...


Thanks...

View 2 Replies


ADVERTISEMENT

Stored Procedure For Updating A Table

Jul 10, 2013

I have a table Patient_Registration with columns Patient_ID,Registration_Date,RENEWAL_DATE_D

After billing This table will get updated every time , when there is any new patient registered or any old patient got renewed there account.

For New patient registration the service code is 1.
and for renewed patient the service code is 293.

CREATE PROCEDURE [dbo].[SP_UPDATE_PATIENT_REGISTRATION_RENEW_DATE]
@PID BIGINT
AS
BEGIN
UPDATE PATIENT_REGISTRATION SET RENEWAL_DATE_D = (SELECT top 1 BD.BILL_DATE_D

[Code] .....

View 3 Replies View Related

SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?

Jun 11, 2015

Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.

View 5 Replies View Related

Updating A Value In A Stored Procedure

Dec 12, 2007

I have a batch input system consisting of two tables which I've simplified below.
 
(Batch Table)BatchID   IntBatchTotal  Decimal(18.2)
(BatchTran Table)BatchTranID  IntBatchTranHeaderID Int   This links to BatchID BatchTranValue  Decimal(18.2)BatchTranAccountNo Int
 
When the batch is complete I want to move the details from the Batch files and transfer them to the relevant Accounts files.
 
(Account Table)AccountNoID  IntAccountTotal  Decimal(18.2)
(AccountTran Table)AccountTranBatchID IntAccountTranBatchRef IntAccountTranAmount Decimal(18.2)
 
I want to be able to run a stored procedure which selects all Batches and Transactions with a reference passed to the stored procedure (@BatchID) and create the entries in the AccountTran Table
 INSERT INTO AccountTran(AccountTranBatchID, AccountTranBatchRef, AccountTranAmount) SELECT  BatchTranId, BatchTranHeaderID, BatchTranValue FROM BatchTran WHERE BatchTranHeaderID=@BatchID
No problems so far, the details are created perfectly.
 
What I want to do next is to take the values in BatchTranValue (which have been passed into AccountTranAmount) and add it to AccountTotal field
My question is can this be completed in one stored procedure if so can you guide me as to how I achieve this?
 
Many thanks

View 3 Replies View Related

Stored Procedure Not Updating

Jun 5, 2006

Using VS.Net 2003 and C#
Why won't this update the table?
public bool changeVisibility( string Id, bool visibility )
{
using( SqlConnection conn = new SqlConnection( @SQLHelper.CONN_STRING ) )
{
using( SqlCommand command = new SqlCommand( "changeVisibility", conn ) )
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add( "@Id", Id );
command.Parameters.Add( "@visibility", visibility );
<snip>
And then the stored procedure:
CREATE PROCEDURE dbo.changeVisibility
 @Id           VARCHAR(20),  @visibility         BIT
 ASBEGIN BEGIN TRAN      UPDATE courseStructure  SET    visible = @visibility                       WHERE  Id = @Id  
 COMMIT TRAN
 ENDGO
The @visibility seems to be the problem.
I set it in the code as boolean.  And it goes into the sp as a BIT.
This should work but doesn't.
If I change the SET visible = 0 it works ok, but not with @visibility.
Thanks,
Zath

View 4 Replies View Related

Updating A View Through A Stored Procedure.

Oct 14, 2007

Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time?
CREATE PROCEDURE sp_SurveyMainDetails_Update
@Constructor_ID  int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes  text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
 
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert'
SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN  SET @Tstamp =  @CHANGED_Tstamp  RAISERROR('This survey has already been updated since you opened this record',16,1)  RETURN 14 ENDELSE
   BEGIN
SELECT * FROM tblSurvey WHERE  Constructor_ID   = @Constructor_ID   AND  --Contractor_ID  = @Contractor_ID  AND  Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND  IsAbortiveCall = @AbortiveCall     IF @@ROWCOUNT>0                          SET @ActionDone = 'Update'
UPDATE tblSurvey SET    Constructor_ID   = @Constructor_ID   ,  SurveyorName_ID   = @SurveyorName_ID ,     Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) ,  IsAbortiveCall = @AbortiveCall ,  Note  = @Notes               WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin                        exec dhoc_ChangeLog_Insert    'tblSurvey',  @Survey_ID,  @User_ID,  @ActionDone,  'Main Details',  @Survey_ID
    end else BEGIN  RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1)  RETURN 10   END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID     
END
--Make sure this has saved, if not return 10 as this is unexpected error
--SELECT * FROM tblSurvey
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
 This is the view;
CREATE VIEW dbo.vw_Property_FetchASSELECT     dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1,                       dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code,                       dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID,                       dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2,                       dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name,                       dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date,                       dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID,                       LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, '')))                       + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address,                       dbo.tblProperty.TenureFROM         dbo.tblPropertyType RIGHT OUTER JOIN                      dbo.tblProperty LEFT OUTER JOIN                      dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN                      dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN                      dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON                       dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN                      dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN                      dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
   
 

View 1 Replies View Related

Update Stored Procedure Not Updating

Apr 12, 2008

Hello,I'm working on a grant management database for a project in my databases class and I'm having some issues updating grants into the database.Here is my situation:How this page works is, it gets a query string from a search_grant.aspx page.  In this query string, it gets the grant ID of the grant the user wants to edit.  If the grant id is a valid grant, it then, upon page_load in C#:1.) Creates an Sql connection set to a viewGrant stored procedure,2.) Adds in all the necessary parameters as output variables3.) Sets private members declared inside of the partial class to those values it gets from the stored procedure4.) Sets textbox controls on the page to those values5.) Displays the page with all the populated data from the stored procedureThat part works fine.  I was having an issue where clicking the update button would not grab the new values that the user input into the textboxes.  I later realized that the Page_Load code was being re-executed BEFORE the button was being clicked (kind of dumb but...whatever).  To fix it, I placed all of the code to do the above statements inside of a: if (!Page.IsPostBack){ // Do code here}That works fine.  The problem, however, is that it's STILL not updating.  The stored procedure works just fine inside of the management studio, but not in the ASP Page.  The code is similar to that of my new_grant.aspx page, which creates a grant into the database.  Why that works and this doesn't, I don't know.  Even when I hard code the values into the parameters in C#, it's not updating the data!  There are no errors that are being returned, so this has really boggled my mind.Any help is greatly appreciated! Here is some sample code of what I'm doing:protected void Update_button_Click(object sender, EventArgs e){ // Create SQL connection to update Grant string ConnectionString = "connection string which works fine"; SqlConnection sqlConnection2 = new SqlConnection(); try { sqlConnection2.ConnectionString = ConnectionString; sqlConnection2.Open(); } catch (Exception Ex) { if (sqlConnection2 != null) { sqlConnection2.Dispose(); } SQLErrorLabel.Text = Ex.Message; SQLErrorLabel.Visible = true; return; } // ------------------ Update values into database ------------------- // Create the statement to use on the database SqlCommand editGrant = new SqlCommand("editGrant", sqlConnection2); editGrant.CommandType = CommandType.StoredProcedure; editGrant.Connection = sqlConnection2; // Set our values for each variable GrantName = GrantName_input.Text; ProjectDescription = ProjDesBox.Text; ReportingYear = Int32.Parse(ReportYearBox.SelectedItem.ToString()); ActivityStarted = Activity_Date.Text; DateSubmitted = Date_Submitted.Text; Audit = chkAudit.Checked; TypeID = Type_ID_input.SelectedValue; FunderID = Funder_List.SelectedValue; StatusID = Status_ID_input.SelectedValue; AcademicDepartmentID = AcademicID_List.SelectedValue; PIID = PI_List.SelectedValue; ContractNumber = txtContractNum.Text; ESUAccountNumber = txtESUAccountNum.Text; AmountAwarded = txtAmount.Text; AwardDate = Award_Date.Text; DateContractSigned = txtDateSigned.Text; ReportingNotes = ReportingNotesbox.Text; NotesNotes = GrantNotesbox.Text; string ReportingTimestamp = DateTime.Now.ToString(); string ReportingWho = Membership.GetUser().ToString(); string NotesTimestamp = DateTime.Now.ToString(); string NotesWho = Membership.GetUser().ToString(); #region insertParams // Add our parameters that SQL will be using editGrant.Parameters.AddWithValue("@GrantID", GID); editGrant.Parameters["@GrantID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@GrantName", GrantName); editGrant.Parameters["@GrantName"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ProjectDescription", ProjectDescription); editGrant.Parameters["@ProjectDescription"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingYear", ReportingYear); editGrant.Parameters["@ReportingYear"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ActivityStarted", ActivityStarted); editGrant.Parameters["@ActivityStarted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateSubmitted", DateSubmitted); editGrant.Parameters["@DateSubmitted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@Audit", Audit); editGrant.Parameters["@Audit"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@TypeID", TypeID); editGrant.Parameters["@TypeID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@StatusID", StatusID); editGrant.Parameters["@StatusID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@FunderID", FunderID); editGrant.Parameters["@FunderID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AcademicDepartmentID", AcademicDepartmentID); editGrant.Parameters["@AcademicDepartmentID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@PIID", PIID); editGrant.Parameters["@PIID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ContractNumber", ContractNumber); editGrant.Parameters["@ContractNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ESUAccountNumber", ESUAccountNumber); editGrant.Parameters["@ESUAccountNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AmountAwarded", AmountAwarded); editGrant.Parameters["@AmountAwarded"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AwardDate", AwardDate); editGrant.Parameters["@AwardDate"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateContractSigned", DateContractSigned); editGrant.Parameters["@DateContractSigned"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingNotes", ReportingNotes); editGrant.Parameters["@ReportingNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingTimestamp", ReportingTimestamp); editGrant.Parameters["@ReportingTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingWho", ReportingWho); editGrant.Parameters["@ReportingWho"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesNotes", NotesNotes); editGrant.Parameters["@NotesNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesTimestamp", NotesTimestamp); editGrant.Parameters["@NotesTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesWho", NotesWho); editGrant.Parameters["@NotesWho"].Direction = ParameterDirection.Input; #endregion // Execute the UPDATE statement to Grant editGrant.ExecuteNonQuery(); sqlConnection2.Close(); sqlConnection2.Dispose(); Response.Redirect("editedGrant.aspx?id=" + GrantIDBox.Text);}EDIT: Problem Solved! Problem was the that the GrantID wasn't being properly set.

View 6 Replies View Related

Stored Procedure For Updating Bit Datatype Column

Mar 25, 2008

Hi guys,
I have a table with following columns and records.
Empid       Empname        Phone     Flag
14             Rajan                 2143          116             Balan                 4321          122             Nalini                 3456          023             Ganesh              9543          0
Now i need to create a stored procedure which will convert the flag values to vice versa since it is a bit datatype. That is if execute the stored procedure it should convert all the flag values to 1 if it zero and zero's to 1. How? Pls provide me the full coding for the stored procedure.
Thanx. 

View 2 Replies View Related

Trouble Updating SQLTable Using A Stored Procedure

Jan 30, 2004

If I run a update stored procedure on my SQLServer It work Fine.
But When I try it in my VB code, it's just do nothing not even an error message.

What I've got to do for being able to Update SQLTable with a stored procedure?

That's my VB code:


Dim objConnect As SqlConnection
Dim strConnect As String = System.Configuration.ConfigurationSettings.AppSettings("StringConn")

objConnect = New SqlConnection(strConnect)

Dim objCommand As New SqlCommand("internUpdate", objConnect)
objCommand.CommandType = CommandType.StoredProcedure

Try
Dim objParam As SqlParameter

objParam = objCommand.Parameters.Add("Id", SqlDbType.Int)
objParam.Direction = ParameterDirection.Input
objParam.Value = InternIDValue

objParam = objCommand.Parameters.Add("Address", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Address.Value.Trim()

objParam = objCommand.Parameters.Add("City", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = City.Value.Trim()

objParam = objCommand.Parameters.Add("ProvinceCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myProvince.SelectedValue

objParam = objCommand.Parameters.Add("PostalCode", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = PostalCode.Value.Trim()

objParam = objCommand.Parameters.Add("Phone", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone1.Value.Trim()

objParam = objCommand.Parameters.Add("Phone2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone2.Value.Trim()

objParam = objCommand.Parameters.Add("Email", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress1.Value.Trim()

objParam = objCommand.Parameters.Add("Email2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress2.Value.Trim()

objParam = objCommand.Parameters.Add("EmploymentStatusCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myStatus.SelectedValue

objConnect.Open()
objCommand.ExecuteNonQuery()
objConnect.Close()

Catch ex As Exception
Exit Sub
End Try



Thanks!!

View 1 Replies View Related

Access Crashes When Updating A Stored Procedure

Jul 20, 2005

Hello,I am having a problem when using access xp as a frontend for sql server2000.I have been trying to update a number of stored procedures (Just simpleadding fields etc) which results in access crashing with event ID 1000 and1001.Does anyone have any ideas as to what could be the problem?Thanks in advance..

View 3 Replies View Related

Stored Procedure Not Updating Database (no Errors Appearing)

Dec 1, 2003

Hi All,

I have a stored procedure (works form the SQL side). It is supposed to update a table, however it is not working, please help. What is supposed to happen is I have a delete statement deleting a payment from the payment table. When the delete button is pushed a trigger deletes the payment from the payment table and transfers it to the PaymentDeleted table. The stored procedure is supposed to update the PaymentDeleted table with the empID and reason for deleting, the delete and transfer work fine, however these 2 fields are not updated. Below is the sp and below that is the vb code. Thanks, Karen



ALTER PROCEDURE dbo.PaymentDeletedInfoTrail (@EmpID_WhoDeleted varchar(10), @Reason_Deleted varchar(255), @PmtDeletedID int)
AS
BEGIN

UPDATE dbo.PaymentDeleted
SET EmpID_WhoDeleted = @EmpID_WhoDeleted
WHERE PmtDeletedID = @PmtDeletedID

UPDATE dbo.PaymentDeleted
SET Reason_Deleted = @Reason_Deleted
WHERE PmtDeletedID = @PmtDeletedID

END



Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
Me.Validate()
If Me.IsValid Then
Dim DR As SqlClient.SqlDataReader

Dim strPmtID As String
strPmtID = lblPmtIDDel.Text

Dim MySQL As String
MySQL = "DELETE From Payment WHERE PmtID = '" & strPmtID & "'"
Dim MyCmd As New SqlClient.SqlCommand(MySQL, SqlConnection1)
SqlConnection1.Open()
DR = MyCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
SqlConnection1.Close()


Dim strDeletePmt As String
strDeletePmt = lblPmtIDDel.Text

Dim cmd As New SqlClient.SqlCommand("PaymentDeletedInfoTrail", SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure

Dim myParam As SqlClient.SqlParameter
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@PmtDeletedID", SqlDbType.Int))
myParam.Direction = ParameterDirection.Input
myParam.Value = lblPmtIDDel.Text

myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@EmpID_WhoDeleted", SqlDbType.VarChar))
myParam.Value = txtEmpIDDelete.Text

myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@Reason_Deleted", SqlDbType.VarChar))
myParam.Value = txtDeleteComments.Text

SqlConnection1.Open()
cmd.ExecuteNonQuery()
SqlConnection1.Close()

End If
Response.Redirect("PaymentVerification.aspx")
End Sub

View 1 Replies View Related

Updating Specific Columns Through A Stored Procedure At Runtime

Feb 25, 2008

I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.

FYI: All columns in the table design are set to null by default.

Thanks in advance,

Dimeji

View 4 Replies View Related

Stored Procedure Definition Not Updating, Causing DTS Copy Error

Jun 27, 2007

I've got a weird one here. I'm running a DTS package on SQL Server2005. It copies a bunch of stored procedures. I renamed them on theoriginating server and ran the DTS again.The came over with the old name and code!I deleted the DTS and built it from scratch, and the same thinghappened.I ran SELECT * FROM sys.objects where type = 'P' on the source serverand the names were correctI'm explicitly checking which sp to copy rather than using Copy all. Ican see the sp namesI've deleted and recreated the sp on the source server using scriptsI've checked the source server nameI've Refreshed everywhereNothing worksWhy is up_Department_GetAllBySchool trying to be be pulled over whenit doesn't exist?Why is up_Department_GetBySchool not being pulled over when it doesexist?I've heard that SQL 2005 pre-SP2 has a problem where renaming anobject that has a text definition (like sprocs, functions, triggers,views) doesn't update the definition. So if you pull that objectdefinition and run it into your new database, it will use the originalscript, which has the original name.I ransp_helptext 'up_Department_GetBySchool'and checked the CREATE statement at the top. Sure enough, it had theold textI asked our NetAdmin to install SP2 on our server. Then I ransp_refreshsqlmodule 'up_Department_GetForSchool'and got this error:Invalid object name 'up_Department_GetAllForSchool'.So even the code which was supposed to fix it, doesn't. Has anyoneelse had this problem, and managed to fix it?--John Hunter

View 1 Replies View Related

Is There A Way To Find The Last Procedure Execution Time If Procedure Updates The Existing Table And There Is No Flags/triggers?

Aug 21, 2007

View 8 Replies View Related

Time Out Calling A Stored Procedure

Jan 30, 2007

I am trying to call a stored procedure from an asp.net application. If I execute the stored procedure in Query Analyzer it takes 1 second to execute, but when I am trying to call it from c# code it times out.
Ideas?
Thanks in advance.

View 4 Replies View Related

Long Time To Run A Stored Procedure

Feb 18, 2000

Hi There,
We have developed a application in VB and connected to SQL Server 6.5, we have some stored procedures where it brings the data from SQL Server 6.5, this application is running since some months, when we run this application it usually take only one minute to generate the report but since couple of days it is taking 25 Minutes to generate the report, even when I run that stored procedure at backend in Query analyzer at Server it is taking 15-20 Minutes to give the result.
please can any one help in identifying the problem, What all the things I need to check to identify it.
Give me the solution.

Thanks in Advance,

Bye,
Madhuker.

View 1 Replies View Related

Time Consuming Stored Procedure (need Help)

Aug 24, 2000

I have a sotred procedure using a cursor which sort data and create subsets based on same oid and same decision_date columns, for each subset I am trying to order them and affect values 1, 2,... for each record in a different subset.
The stored procedure seems to work very well and fast against a small tables (during tests). When used against a table with 200,000 records it takes more than 24 hours...
I am looking for a help to make it work faster, thanks guys.
here is the stored procedure:


CREATE PROCEDURE ORDERING_TEST_PROCEDURE AS

DROP TABLE REVIEWS_TEST_TABLE

SELECT OID,DECISION_DATE,DECISION_ID,VOTES_REQUIRED, ORDERING INTO REVIEWS_TEST_TABLE FROM DECISION_FLAGS
WHERE VOTES_REQUIRED IN ('1','2','3') and FINAL_DECISION_CODE NOT IN ('0800','0810','0840') and DECISION_TYPE_CODE < '0100'
ORDER BY OID, DECISION_DATE,VOTES_REQUIRED DESC


CREATE INDEX OID_DECISIONID_DATE_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID, DECISION_DATE, OID)

CREATE INDEX VOTESREQUIRED_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (VOTES_REQUIRED)

CREATE INDEX DECISIONID_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID)

declare @oldoid varchar(12)
declare @olddecision_date varchar(75)
declare @oid varchar(12)
declare @decision_date varchar(75)
declare @Decision_id varchar(12)
declare @ordering varchar(1)
declare @ordering_count int
declare @votes_required varchar(12)

set @oldoid = 'space'
set @olddecision_date = 'space'
set @oid = 'space'
set @decision_date = 'space'
set @votes_required='space'
set @Decision_id = 'space'
set @ordering = '0'
set @ordering_count = 0

declare review_test_cursor cursor for
select oid,decision_date,votes_required,ordering,decision _id from CRIMS.dbo.reviews_test_table
order by oid,decision_date,votes_required asc
open review_test_cursor
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id

while (@@fetch_status = 0 )
begin
if @oldoid <> @oid or @olddecision_date <> @decision_date
begin
set @oldoid = @oid
set @olddecision_date = @decision_date
set @ordering_count=0
end
update reviews_test_table
set ordering = CAST ((@ordering_count + 1) as VARCHAR)
where decision_id = @Decision_id
set @ordering_count = @ordering_count + 1

fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
end

close review_test_cursor
deallocate review_test_cursor


/*********************************/
UPDATE DECISION_FLAGS
SET ORDERING = '0'

UPDATE DECISION_FLAGS
SET DECISION_FLAGS.ORDERING = TEM.ORDERING
FROM DECISION_FLAGS DEC, REVIEWS_TEST_TABLE TEM

View 4 Replies View Related

Stored Procedure Run Time Efficiency.

Oct 5, 1999

I'm having problems testing the effectiveness of changes I'm making to a stored_procedure. I need to test the runtime, but each time I run the sp, it gets progressively faster because the data has already been cached. Is there a way for me to clear the data from cache before each run? I can't stop SQL Server.

View 2 Replies View Related

How To Increase Stored Procedure Run Time

Jun 24, 2008

I am trying to test the connection pooling of my app and need to artificially increase the execution of a stored procedure to 10-15 seconds. There aren't any big tables in my sample data to select on. What would be the easiest way to make a stored procedure fun for a few seconds?

Thanks.

View 6 Replies View Related

Stored Procedure Execution Time

Mar 7, 2008

Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help?
Thanks,
Anne.

View 19 Replies View Related

Help-take A Long Time Run This Stored Procedure 15 Second

May 3, 2008

help-take a long time run this stored procedure 15 second



Code Snippet

DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP

@BaseDate SMALLDATETIME,@NumDays TINYINT,@myNum TINYINT

set @myNum=4

SELECT @WantedDate = CAST(CAST(YEAR(GETDATE()) AS nvarchar)+ '-' +CAST(1 AS nvarchar)+ '-' +CAST(@myNum AS nvarchar) AS SMALLDATETIME), -- User supplied parameter value

@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),

@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

select @WantedDate

IF @NumDays = 28

BEGIN

SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,

CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis

FROM dbo.v_hasot_all INNER JOIN

dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int

WHERE (NOT (dbo.v_hasot_all.nameday IN (29, 30, 31)))



END

ELSE IF @Numdays = 29

BEGIN

SELECT ...............

WHERE (NOT (dbo.v_hasot_all.nameday IN ( 30, 31)))



END

ELSE IF @Numdays = 30

BEGIN

SELECT ......................

WHERE (NOT (dbo.v_hasot_all.nameday IN (31)))



END

ELSE IF @Numdays = 31

BEGIN

SELECT dbo.v_hasot_all.empID, dbo.v_hasot_all.Fname, dbo.v_hasot_all.day1A, dbo.v_hasot_all.nameday, dbo.tb_mis_hsaot.mishs_txt, CONVERT(datetime,

CAST(dbo.v_hasot_all.nameday AS nvarchar) + '/' + CAST(@myNum AS nvarchar) + '/' + CAST(YEAR(GETDATE()) AS nvarchar), 103) AS date_mis

FROM dbo.v_hasot_all INNER JOIN

dbo.tb_mis_hsaot ON dbo.v_hasot_all.day1A = dbo.tb_mis_hsaot.mishs_int

END
and whan i run my code separately it run fast
i think it is the ELSE IF @Numdays

TNX

View 5 Replies View Related

DTS PACKAGE From A Stored Procedure-Iam Sending This The Third Time.can Anybody Look At This

Jul 14, 2000

I have created a DTS package. I want to know how to call/run this package from a stored procedure.

Please let me know how to call this DTS from a Stored Procedure.

Thanks.

-Rajesh

View 1 Replies View Related

Retrieving Server Time Using Stored Procedure

Jul 21, 2000

Hello all,

We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.

So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?

Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!

Kevin Kraus

View 2 Replies View Related

Time/date In A Scheduled Stored Procedure

Nov 12, 2004

Hi there,

SQL newbie here, and thanks for any help you may able to provide.

My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday.

I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it.

Thanks again.

View 4 Replies View Related

Stored Procedure Taking More Time Than Sql Query

Oct 6, 2005

Hi

I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.

What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.

Thanks.

View 1 Replies View Related

How To Catch Stored Procedure Execution Time?

Dec 6, 2007

Hello, everyone:

For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.

ZYT

View 14 Replies View Related

Suddenly Stored Procedure Takes Too Much Time?

Jul 17, 2015

I have a sp that was taking very little time (about 34 sec). But suddenly is stacked. It is running and running and running but not LOCKED neither SUSPENDED. It is always RUNNABLE. I have made Index and statistics optimization but nothing. I looked into execution plan but everything seems ok. All the time is in 3 indexes that are Index Seek and not Table Scan!!! So why is stacked... I do not know how much time it takes because I have to stop it. (SQL SERVER 2008 R2, the database was migrated from SQL SERVER 2000)

View 6 Replies View Related

Time Out In Triggering A Sqlclr Stored Procedure

Jul 18, 2007

Hello,

I have a sqlclr stored procedure that works well if started via execute in SSMS. (The stored procedures makes a SSRS web service call.) When I install a trigger on a database table to execute the stored procedure

create trigger NewWdsStatsEntry on dbo.WDSSTATS after insert
as execute UpdateReportExecutionSnapshot N'http://localhost/ReportServer/reportservice2005.asmx', N'/Report1';
go

and add something to the table, I get the following error:

Msg 6522, Level 16, State 1, Procedure UpdateReportExecutionSnapshot, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "UpdateReportExecutionSnapshot":
System.Net.WebException: The request was aborted: The operation has timed out.
System.Net.WebException:
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at UserProcedures.ReportService2005.ReportingService2005.UpdateReportExecutionSnapshot(String Report)
at StoredProcedures.UpdateReportExecutionSnapshot(String url, String reportPath)
.
The statement has been terminated.

The insert times out after 100 seconds and no row has been added to the table, but the web service call actually created a new report! Any idea why the trigger can't execute the stored procedure?
Thanks!

werner

View 3 Replies View Related

Optimise Stored Procedure Respone Time?

Apr 30, 2008



Hello,
If i want to optimise stored procedure response time.
Which things i need to be considered?

Thanks in advance.

View 3 Replies View Related

Execution Time For Stored Procedure Vs. Query Analyzer

Feb 21, 2002

HI,
I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.

I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?

Any feedback would be appreciated!

Thanks in advance...

View 2 Replies View Related

Stored Procedure Vs SQL Huge Difference In Execution Time

Jul 23, 2005

I have a Stored Procedure (SP) that creates the data required for areport that I show on a web page. The SP does all the work and justreturns back a results set that I dump in an ASP.NET DataGrid. The SPtakes a product area and a start and end date as parameters.Here are the basics of the SP.1.Create temp table to store report results, all columns are createdthat will be needed at this point.2.Select products and general product data into the temp table.3.Create a cursor that loops through all the products in the temptable, running a more complex query with each individual product.4.The results of that query are updated on the temp table based on thecurrent product of the cursor.5.A complex "totals" query is run and the results from that areinserted into the temp table as the last 3 rows.In all we are talking about 120 rows in the temp table with 8 columnsthat are mostly numbers.I originally wrote this report SP about a month ago and it worked fine,ran in about 10 - 20 seconds based on server traffic and amount ofdata in the temp table. For the example I'm running there are the120 products.Just yesterday the (SP started timing out and when I ran the SPmanually from Query Analyzer (QA) (exec SP_NAME ... ) with the sameparameters as it was getting in the code it took 6 minutes to complete.I was floored. I immediately copied the SQL out of the SP and pastedinto another QA window, changed the variables to be hard coded valuesand ran it. It completed in 10 seconds.I'm really confused now. I ran a Profiler on the 2 when I ran themagain. The SQL code in QA executed again in ~10 seconds with 65,000reads. When the SP finished some 6 minutes later it had completed witthe right results but it needed 150,000,000 reads to do its job.How can the exact same SQL code produce such different results (time,disk reads) based on whether its in a SP or just run from QA but stillgive me the exact same output. The reports both look correct and havethe same numbers of rows.I asked my Sys Admin if he had done anything to anything and he saidno.I've been reading about recompiles and temp table indexes and allkinds of other stuff that could possibly be affecting it but havegotten nowhere.Any ideas are appreciated.

View 5 Replies View Related

Report Calling A Stored Procedure Taking Too Much Time While

Aug 23, 2007

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.
Please help--Iam stumped.

There are no infinite loops in the report. I checked.

View 9 Replies View Related

Query Runs In Sub Second Time Until I Put It In A Stored Procedure Then It Takes 2 Minutes.

Jun 25, 2007

The query below runs in sub second time if I don't call it as a stored procedure. I have looked at the execution plan for both the query and the query as a stored procedure and they are the same.
When I put the query into a stored procedure it takes over 2 minutes to run.
All feedback (even the ugly stuff) is more than welcome. I want to master this issue and forever put it behind me.
This is the sql when I just execute it outright:1 DECLARE
2 @WebUserID nvarchar(20)
3 ,@DocumentTypeID int
4 ,@RouteID nvarchar(10)
5 ,@CustomerID nvarchar(15)
6 ,@DocumentIDPrefix nvarchar(20)
7 ,@StartDate datetime
8 ,@EndDate datetime
9 ,@OversoldOnly bit
10 ,@DexCustomersOnly bit
11 ,@DeviationsOnly bit
12 ,@CashNoPaymentOnly bit
13 ,@SignatureName nvarchar(45)
14 ,@SortExpression varchar(200)
15 ,@StartRowIndex int
16 ,@MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 WITH OrderedDocumentHistory AS
32 (
33 SELECT
34 dh.DocumentHistoryID
35 ,dh.DocumentID
36 ,dh.DocumentTypeID
37 ,dh.DocumentTypeDesc
38 ,dh.RouteID
39 ,dh.RouteDesc
40 ,dh.CustomerID
41 ,dh.CustomerName
42 ,dh.DocDate
43 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
44 ,ddt.Description AS 'SignatureReason'
45 ,a.Amount
46 ,ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC) AS 'RowNumber'
47 FROM
48 DocumentHistory dh
49 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
50 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
51 INNER JOIN
52 (
53 SELECT
54 DocumentHistoryID
55 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
56 FROM
57 DocumentHistoryItems dhia
58 GROUP BY
59 dhia.DocumentHistoryID
60 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
61 LEFT OUTER JOIN
62 (
63 SELECT DISTINCT
64 dca.DocumentID
65 ,1 AS 'HasReceipt'
66 FROM
67 DocumentCollections dca
68 ) AS dc ON dh.DocumentID = dc.DocumentID
69 WHERE
70 dh.DocDate BETWEEN @StartDate AND @EndDate
71 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
72 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
73 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
74 AND dh.DocumentID LIKE @DocumentIDPrefix
75 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
76 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
77 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
78 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
79 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
80 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
81 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
82 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
83 OR @WebUserID IS NULL)
84 )
85
86 SELECT
87 DocumentHistoryID
88 ,DocumentID
89 ,DocumentTypeDesc
90 ,RouteID
91 ,RouteDesc
92 ,CustomerID
93 ,CustomerName
94 ,DocDate
95 ,Amount
96 ,HasReceipt
97 ,SignatureReason
98 FROM
99 OrderedDocumentHistory
100 WHERE
101 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows) Here is the sql for creating the stored procedure.  1 CREATE Procedure w_DocumentHistory_Select
2 (
3 @WebUserID nvarchar(20)
4 ,@DocumentTypeID int
5 ,@RouteID nvarchar(10)
6 ,@CustomerID nvarchar(15)
7 ,@DocumentIDPrefix nvarchar(20)
8 ,@StartDate datetime
9 ,@EndDate datetime
10 ,@OversoldOnly bit
11 ,@DexCustomersOnly bit
12 ,@DeviationsOnly bit
13 ,@CashNoPaymentOnly bit
14 ,@SignatureName nvarchar(45)
15 ,@SortExpression varchar(200)
16 ,@StartRowIndex int
17 ,@MaximumRows int
18 )
19 AS
20 SET NOCOUNT ON
21
22 IF LEN(@SortExpression) = 0 OR @SortExpression IS NULL
23 SET @SortExpression = 'Number DESC'
24
25 IF @StartRowIndex IS NULL
26 SET @StartRowIndex = 0
27
28 IF @MaximumRows IS NULL
29 SELECT
30 @MaximumRows = COUNT(dh.DocumentHistoryID)
31 FROM
32 DocumentHistory dh;
33
34 WITH OrderedDocumentHistory AS
35 (
36 SELECT
37 dh.DocumentHistoryID
38 ,dh.DocumentID
39 ,dh.DocumentTypeID
40 ,dh.DocumentTypeDesc
41 ,dh.RouteID
42 ,dh.RouteDesc
43 ,dh.CustomerID
44 ,dh.CustomerName
45 ,dh.DocDate
46 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
47 ,ddt.Description AS 'SignatureReason'
48 ,a.Amount
49 ,CASE
50 WHEN @SortExpression = 'Number DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID DESC))
51 WHEN @SortExpression = 'Number ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID ASC))
52 WHEN @SortExpression = 'CustomerName DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName DESC))
53 WHEN @SortExpression = 'CustomerName ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName ASC))
54 WHEN @SortExpression = 'CompletedDate DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC))
55 WHEN @SortExpression = 'CompletedDate ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate ASC))
56 WHEN @SortExpression = 'RouteDescription DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc DESC))
57 WHEN @SortExpression = 'RouteDescription ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc ASC))
58 END AS 'RowNumber'
59 FROM
60 DocumentHistory dh
61 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
62 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
63 INNER JOIN
64 (
65 SELECT
66 DocumentHistoryID
67 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
68 FROM
69 DocumentHistoryItems dhia
70 GROUP BY
71 dhia.DocumentHistoryID
72 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
73 LEFT OUTER JOIN
74 (
75 SELECT DISTINCT
76 dca.DocumentID
77 ,1 AS 'HasReceipt'
78 FROM
79 DocumentCollections dca
80 ) AS dc ON dh.DocumentID = dc.DocumentID
81 WHERE
82 dh.DocDate BETWEEN @StartDate AND @EndDate
83 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
84 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
85 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
86 AND dh.DocumentID LIKE @DocumentIDPrefix
87 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
88 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
89 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL((SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
90 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
91 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
92 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
93 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
94 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
95 OR @WebUserID IS NULL)
96 )
97 SELECT
98 DocumentHistoryID
99 ,DocumentID
100 ,DocumentTypeDesc
101 ,RouteID
102 ,RouteDesc
103 ,CustomerID
104 ,CustomerName
105 ,DocDate
106 ,Amount
107 ,HasReceipt
108 ,SignatureReason
109 FROM
110 OrderedDocumentHistory
111 WHERE
112 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows)
 

 Here is the code for calling the stored procedure:1 DECLARE @RC int
2 DECLARE @WebUserID nvarchar(20)
3 DECLARE @DocumentTypeID int
4 DECLARE @RouteID nvarchar(10)
5 DECLARE @CustomerID nvarchar(15)
6 DECLARE @DocumentIDPrefix nvarchar(20)
7 DECLARE @StartDate datetime
8 DECLARE @EndDate datetime
9 DECLARE @OversoldOnly bit
10 DECLARE @DexCustomersOnly bit
11 DECLARE @DeviationsOnly bit
12 DECLARE @CashNoPaymentOnly bit
13 DECLARE @SignatureName nvarchar(45)
14 DECLARE @SortExpression varchar(200)
15 DECLARE @StartRowIndex int
16 DECLARE @MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 EXECUTE @RC = [Odom].[dbo].[w_DocumentHistory_Select]
32 @WebUserID
33 ,@DocumentTypeID
34 ,@RouteID
35 ,@CustomerID
36 ,@DocumentIDPrefix
37 ,@StartDate
38 ,@EndDate
39 ,@OversoldOnly
40 ,@DexCustomersOnly
41 ,@DeviationsOnly
42 ,@CashNoPaymentOnly
43 ,@SignatureName
44 ,@SortExpression
45 ,@StartRowIndex
46 ,@MaximumRows
 

View 3 Replies View Related







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