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 timestamp
DECLARE @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
 END
ELSE

   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 tinyint
IF @@error <>0
 RETURN @@error
GO

 This is the view;

CREATE VIEW dbo.vw_Property_Fetch
AS
SELECT     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.Tenure
FROM         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


ADVERTISEMENT

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

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

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

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

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

Should I Use A View Or A Stored Procedure

Apr 5, 2007

I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views.  Does anyone know why someone would do this?  I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?  

View 1 Replies View Related

View Vs. Stored Procedure

Jul 7, 2000

Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.

View 3 Replies View Related

Stored Procedure Vs View

Jan 23, 2006

I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?

View 3 Replies View Related

Stored Procedure Or View?

Mar 14, 2007

Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...

with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?

CallID DateStopTimeStopCallStatus
002161772006-01-2005:39:24Open
002161772006-01-2005:39:27Open
002161772006-01-2005:40:13Open
002161772006-01-2005:40:24Pending
002161772006-02-0716:05:47Pending
002161772006-02-2117:26:22Pending
002161772006-02-2117:29:06Pending
002161772006-02-2117:29:08Open
002161772006-03-0316:35:10Open
002161772006-04-0515:12:26Open
002161772006-04-0515:17:09Open
002161772006-04-1414:37:49Open
002161772006-04-1414:37:54Awaiting
002161772006-04-1911:20:30Awaiting
002161772006-04-1912:12:34Awaiting
002161772006-04-1912:12:37Awaiting
002161772006-04-1912:12:58Awaiting
002161772006-04-1912:13:00Closed[/b]

View 20 Replies View Related

Stored Procedure From The View

Sep 25, 2007

How can I create a stored procedure that combines the results from three views, and puts them in a temp table?

View 20 Replies View Related

Using A Stored Procedure In A View

Jan 1, 2008

Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.

If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.

Thanks.

View 1 Replies View Related

Using A Stored Procedure In A View

Jan 1, 2008

Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.

If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.

Thanks.

View 1 Replies View Related

Help Coverting A Stored Procedure To A View

Aug 13, 2007

Can someone help me convert this stored procedure to a view? It is using two UDFs.
I appreciate this very much!@Start datetime,
@End datetime

AS

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as Description
FROM

tblClients C INNER JOIN
tblPMTs P

ON C.Client_ID = P.Client_ID INNER JOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_ID INNER JOIN
tblClientAddresses A

ON C.Client_ID = A.Client_ID LEFT OUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDate BETWEEN @Start AND @End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

ORDER

BY pmtLevel
RETURN 

View 3 Replies View Related

Using Stored Procedure In View As A Table ?!

Aug 28, 2007

Hi guys
I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs  must be ordered  in certain  way , for example "1st managers then engineers  … workers  … " so In the table that   job titles are defined  there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order .
The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that.
Any Idea?
 

View 8 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.

View 9 Replies View Related

When To Use ? Stored Procedure V/s View In SQL Server

Dec 6, 2005

hi,
Can someone tell me when to use SQL Server View as oppose to Stored Porcedure?
Currently we do everything with SQL Server stored procedure. I mean, even if we have to display some report, we use Stored Procedure.
In what situations and senarios views are better and one should consider them over Stored Procedure?

View 13 Replies View Related

View Creation Using Stored Procedure

Apr 21, 2001

Hi

I need to create a view using a stored procedure .

The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.

I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .


Sujit

View 1 Replies View Related

Stored Procedure - Can I Use 4-part Name With A &#34;VIEW&#34;?

Aug 3, 2001

Joe, thank you for the answer to my post.

Can I also use a

SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?

Also, in my sp, I have the following:

SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS,
ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted,
Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline
FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#]
WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1;
GO

ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?



Thanks,
John Wilson

View 1 Replies View Related

Accessing A 'View' From Within A Stored Procedure

Jun 7, 2004

Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.

The syntax I'm using is as follows :

SELECT TOP 1 @MyJobN = IJobN FROM MyView

I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.

SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView

But alas to no avail!


Any offers kind people??

View 12 Replies View Related

Call Stored Procedure From View

Sep 7, 2006

Hello all,
does anyone know if it's possible to call a stored procedure from a view.

Thnx,
Patrick

View 9 Replies View Related

Creating A View In A Stored Procedure

Apr 2, 2004

Hi Everyone

Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

CREATE PROC upProcName AS

DECLARE @Variable varchar(50)

CREATE VIEW vwName AS

SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @Variable

GO

Any Thoughts ideas would be great

Cheers

View 7 Replies View Related

Need Help To Create A View/Stored Procedure

Jun 24, 2008

Experts

I am trying to create a view or Stored Procedure between different table

Table1 consist of the follwing Fields:

Ref_No: String hold the reference number, Unique
Details: String

Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique

What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers

Any advice?

View 4 Replies View Related

Create View Within A Stored Procedure

May 21, 2007

hi,

Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go

go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?

thank you

View 20 Replies View Related

Creating A View From A Stored Procedure

Sep 19, 2007

HelloNewbie here.Is there a way of creating a VIEW...using a stored procedure. I ambasically trying to create a view to return some data that I amgetting using a stored procedure.I have created the procedure and when I execute this its working ok.The stored procedure uses a datefrom and dateTo which I have set up bytweaking the getdate() and getdate()-2.In other words can you create a view like thisCREATE VIEW view_testASexec proc_testGOAny help will be greatly appreciated.Remmy

View 1 Replies View Related

Cretae A View In One DB With A Stored Procedure From Another DB

Jul 20, 2005

Hi,Ik created an application with visuals basic.NET. This has aconnection string to one database, let's say 'A'. In this database astored procedure is called which should execute a string (which ispassed by the) VB tool. This string is a CREATE VIEW statement en thisshould be executed in another database let's say 'B'.I tried this in Transact - SQLEXEC('USE B;' + Query)An error occurs : CREATE VIEW should be the first in a batchedstatement.Could anyone help me with this one?Greetz,Hennie

View 1 Replies View Related







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