Views Inside Stored Procedures.

Apr 18, 2007

Hi everybody,
I am trying to alter a view inside a Stored Procedure and sql server is not allowing me to do so.
Can we not have an alter view inside a Stored Procedure?
Here is the code:
 CREATE PROCEDURE dbo.[UPDATE_VIEW_LINEITEMALLOTMENT]
    -- Add the parameters for the stored procedure here
@MAINID int,
@BE VARCHAR(8)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;

        ALTER VIEW [Budget_Management_System].[dbo].[LineItem_OCAFund]
        AS
        SELECT     TOP (100) PERCENT SUM(dbo.ALL_OCAFUND.AMOUNT) AS Expr2, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC,
                              dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_LINEITEM.MAINID
        FROM         dbo.APP_LINEITEM INNER JOIN
                              dbo.ALL_ORG ON dbo.APP_LINEITEM.ROWID = dbo.ALL_ORG.LINEITEMID INNER JOIN
                              dbo.ALL_OCAFUND ON dbo.ALL_ORG.ROWID = dbo.ALL_OCAFUND.ORGID INNER JOIN
                              dbo.APP_AMENDMENT ON dbo.ALL_ORG.AMENDMENTID = dbo.APP_AMENDMENT.ROWID AND
                              dbo.ALL_OCAFUND.AMENDMENTID = dbo.APP_AMENDMENT.ROWID
        GROUP BY dbo.APP_LINEITEM.LINEORDER, dbo.APP_LINEITEM.BE, dbo.APP_LINEITEM.MAINID, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC,
                              dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_AMENDMENT.AMENDMENTORDER
        HAVING      (dbo.APP_LINEITEM.BE = @BE) AND (dbo.APP_LINEITEM.MAINID = @MAINID) AND (dbo.APP_AMENDMENT.AMENDMENTORDER = 1)
        ORDER BY dbo.APP_LINEITEM.LINEORDER

  
END


thanks a lot,
Murthy here 

View 7 Replies


ADVERTISEMENT

Calling Stored Procedures Inside Cursors

Aug 13, 2001

I have created a cursor using the following type of syntax:

DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor

Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.

View 2 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Using Views Within Stored Procedures

Jul 22, 2007

Good evening:Can anyone give me the low-down on using view from within my stored procedures?  I've got some rather complex views that join multiple tables resulting in a staggering SQL query.  I then use stored procedures to accept input parameters to determine order by and direction clauses.  This results in some rather large "IF ELSE IF ELSE IF..." statements in my SP.  Put a complicated SQL query together with lots of IF THEN and it becomes difficult to keep stuff straight in the SP.  Therefore, using the view to keep my SQL in one place is an ideal solution. However, and here's the question, does it have a negative impact on speed and/or scalability.  We plan to use these SP's within an ASP.NET website that may get a very large amount of traffic. Any advice/help you can give me would be really appreciated. Thanks!
- Future Daddy
 

View 2 Replies View Related

Getting At Views And Stored Procedures

Feb 15, 2001

I have been having troubling getting at views and stored proceduers with ADO objects while developing in VB.

I can get a list of the views and stored procedures, but I don't know how to actually get the text values of them... i would like the text so that I can parse/compare them agianst one another...

Any thoughts or help on this?

I would really appreciate the help!

Matt

View 2 Replies View Related

Views In Stored Procedures?

Feb 16, 2007

Hello,

This maybe a simple question, I'm using reporting services to make a report, question is can I write a stored procedure that queries from a join of two views instead of two tables?

I'm having trouble getting my table data into the reportable state I want it, and I think organising into views might be easier.

Thanks for any help.

View 3 Replies View Related

Performance Comparision - Stored Procedures VS SQL (Inside SSIS)

Mar 23, 2007

I am working on a technical design of data integration ETL package which will be moving data from SQL Server Source to DB2 destination. I currently have two options, when moving data to DB2(IBM AS400). I can call a AS400 Stored Procedure, and pass in the data to the stored procedure, and perform the insert processing within the AS400 environment or I could do inserts from SSIS in a DFT and write individually to AS400 tables. My question is from a performance and good practice perspective, which method should I move forward with. I need a possible list of pros- and cons when using AS400 Sproc vs using SQL within SSIS? I would really appreciate response from individuals who have done something similar in the past. Thanks a lot and I am really looking forward to responses.

View 1 Replies View Related

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures

Oct 30, 2005

Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. 
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _                                    "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View 7 Replies View Related

Accessing Views From Stored Procedures

Sep 7, 2007

Hi,I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?Thanks. 

View 4 Replies View Related

Is There Any Point In Views? Vs Stored Procedures?

Nov 8, 2007

I do all my data access through stored procedures. Is there any benefit in creating views that the stored procedure accesses. At the moment, I tend to just write the select and join within the stored procedure.

View 12 Replies View Related

Scripting Views && Stored Procedures

Oct 9, 2002

Is there a way to script views and/or stored procedures from Query Analyzer? I am hoping to do this as part of a stored procedure.

Thanks!

View 3 Replies View Related

Transfer Stored Procedures And Views Only

Oct 19, 2001

I have two databases named A and B. They are exactly same table structure, but the records are different, and we have near 400 stored procedures and over100 views in the database A. Now my question is that how can I transfer all stored procedures and views from database A to database B?

Thank you very much!

View 2 Replies View Related

Views / Stored Procedures / Functions

Dec 1, 2005

Hi All,

Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.

View 7 Replies View Related

Question Regarding Stored Procedures, Views And ASP

Jul 20, 2005

Please could someone explain to me the differences between a storedprocedure and a view.The reason for this question is I have two almost identical ASP pages.Both get the same results but one uses a stored procedure and one usesa view. If the query returns no results the 'view page' generateserrors and therefore I have to check for BOF and EOF, whereas the'stored procedure page' does not generate errors and instead wouldappear to return a recordset with 0 entries.Thanks in advanceNeil.

View 1 Replies View Related

Diffrence Between Views And Stored Procedures

Jun 18, 2007

Hi

what is the diffrence between views and stored procedures in sqlserver 2005

thanks in advance.

View 3 Replies View Related

Create Views In Stored Procedures?

Sep 25, 2007

Can you create views within a stored procedure?

View 10 Replies View Related

MS Access And Stored Procedures Or Views

Mar 13, 2008

Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error?
It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access.
Thanks in advance - I hope.

View 9 Replies View Related

Performance Problem With Views And Stored Procedures..

Nov 8, 2000

Hello Everybody,

I posted this same question couple of times in the news groups but no answers. I have a 2 tables and i am doing a union query using a view. each has 250 rows. The query takes 20 seconds to return the results. no joins or anything. the create view simply looks like this:

create view myview as
select id, name from table1
union
select id,name from table2

Where as if i write a stored procedure like below, it returns the rows in 4 seconds.
create table #mytable
( id int, name varchar(30))
insert into #mytable (id, name) select id, name from table1
insert into #mytable (id, name) select id, name from table2
select id,name from #mytable.


I prefer doing in the view since both returns the same result. I tried running dbcc, update statistics. but no luck. Can anyone please help me in this issue.

Thanks
Ramesh

View 3 Replies View Related

Views Vs Complex Joins In Stored Procedures

Oct 17, 2007

I have a multiple table dataset that needs to be returned, with at least 5 joins, some inner, some left outer.

Currently, this is done via a parameterized stored prodedure, which is used fairly frequently. The parameters only affect the where clause, not the joins.

Would it be better to create the view with the joins already done, then pass in the parameters with the stored procedure? Which is better for overall performance? Which is better for quicker response times to the calling asp.net application?

Thanks in advance,

Leah

View 2 Replies View Related

Views / Stored Procedures / Updating Records

Jul 23, 2005

This is a general question regarding the use of view and storedprocedures. I'm fairly new to databases and SQL.I've created a SQL database using an Access Data Project ("ADP") andI'm satified with the table structure. I've moved on to building somefront ends for our users.I'm running into situations where I want subreports to be built fromqueries [views or stored procedures-I don't know which to use so I usethe term query] that are dependent on the values in other controls.I've played with stored procedures and I've figured out how to sendcriteria to a stored procedure and then dynamically change the recordsource of a subreport.However, I'm running into cases where I can't add records to theresults of a stored procedure. The table I'm running a stored procedureon has five fields: (1) Primary Key for each record, (2) FundID that'sa primary key in another table, (3) CompanyID that's a primary key inanother table, (4) Attribute 1 of the (Fund/Company) and (5) Attribute2 of the (Fund/Company).The stored procedure filters the set of Fund/Companies based on aFundID from a form. I can update this stored procedure. However, forusers, they would like to see the Fund Name from the table that hasunique FundIDs. As soon as I include that into the stored procedure, Ican no longer add records.My questions are many:1. Is there a primer online that discusses the theory behind myquestion? Recordsets, updatability, working with recordsets in forms?2. What are some best practices for developing subreports, combo boxes,list boxes, etc. where the data is dependent on the values in a control3. I'm struggling with the best ways to grab objects on a form. If I'mon the main form I'm comfortable working with theMe.__object__.__sub-oject routine. However, if I'm in one subform whereI need another subform to change based on the record I'm in, I feelthat my code to get at the subform is very klunky..forms.main form name.sub form name.form.record sourceI don't even know how I figured out the "form" part before recordsource. Again, are there some basic rules or guides about navigatingthrough forms in VBA?4. Should I be developing front ends in some other environment?I know it's a lot, but all the advice from the newsgroups seems topresuppose some knowledge about how ADP, ADO, ODBC..blah blah and Ican't seem to find any documents about ADP and SQL.

View 3 Replies View Related

Views Vs Stored Procedures, Whats The Difference?

Jan 27, 2006

The purpose for my questions is accessing these technologies fromapplications. I develop both applications and databases. Working withMicrosoft C#.NET and Microsoft SQL Server 2000 Production and 2005 TestEnvironments.What is the purpose of a view if I can just copy the vode from a viewand put it into a stored procedure?Should I be accessing views from stored procedures?Should I use views to get information? and Stored Procedures forInserts, Updates and Deletes?What are the performance differences between the two?Thank you for any and all information.SBProgrammer

View 28 Replies View Related

Problematic Stored Procedures Using Views With Triggers

May 9, 2006

Hi,

I would like to use the view of the company data in the following stored procedures

without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.

Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.

INSERT INTO State(StateName)
VALUES(@StateName)

Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.

SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber


FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID

Here is code for the insert trigger on the view

CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN

DECLARE @CompanyName VARCHAR(128)
DECLARE @AddressID INT
DECLARE @Street VARCHAR(256)
DECLARE @City VARCHAR(128)
DECLARE @StateID INT
DECLARE @StateName VARCHAR(128)
DECLARE @ZipCode INT
DECLARE @Country VARCHAR(128)
DECLARE @PhoneID INT
DECLARE @PhoneNumber VARCHAR(32)
--DECLARE @CompanyID INT

SELECT
--@CompanyID = CompanyID,
@CompanyName = CompanyName,
@Street = Street,
@City = City,
@StateName = StateName,
@ZipCode = ZipCode,
@Country = Country,
@PhoneNumber = PhoneNumber
FROM INSERTED

INSERT INTO State(StateName)
VALUES(@StateName)

SET @StateID = @@IDENTITY

INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@Street, @City, @StateID, @ZipCode, @Country)

SET @AddressID = SCOPE_IDENTITY()

INSERT INTO Phone(PhoneNumber)
VALUES(@PhoneNumber)

SET @PhoneID = SCOPE_IDENTITY()

INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@CompanyName, @AddressID, @PhoneID)

END

The stored procedures are here.

CREATE PROCEDURE GetCompany
(
@CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @CompanyID
END
GO

CREATE PROCEDURE AddCompany
(
@CompanyName VARCHAR(128),
@Street VARCHAR(256),
@City VARCHAR(128),
@StateName VARCHAR(128),
@ZipCode VARCHAR(128),
@Country VARCHAR(128),
@PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@CompanyName, @Street, @City, @StateName,
@ZipCode, @Country, @PhoneNumber)
END
GO

View 3 Replies View Related

Starting Out With : Views, Stored Procedures, Functions, Synonyms

Mar 22, 2006

Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time

View 1 Replies View Related

Do You Replicate Views, Stored Procedures, And User Functions?

Jul 22, 2007

Greetings,



We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.



Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?



Thanks for any ideas that you share.



BCB

View 4 Replies View Related

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

Nov 1, 2007

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

View 1 Replies View Related

Switching Schema Ownership Of Views, Stored Procedures, Tables

Feb 20, 2007

Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go

This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:

Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.

How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?

View 1 Replies View Related

Programmatically Extract Stored Procedures, Views, Tables, And Functions

May 21, 2008



Hi Folks,


I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.

I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?

For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.

For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).

For Views, I need to know the Name(s) and access the Views Source

For functions, I need to know the Name(s) and access the function source.


I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:

select * from sys.objects .. I get an error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.



Thank you in advance.



View 13 Replies View Related

Is It Possible To Make An Installer Package(msi And Exe Files) For Stored Procedures,tables,views

Nov 9, 2007

Hi,

I've a doubt regarding deploying and creating package (MSI and EXE files) for SQL Stored procedures,views,tables,functions,triggers etc.,just like ASP.NET application by publishing and deploying..Is it possible in the same way for sql objects.If so,pls help me how to do it?

Thanks in advance.

View 1 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Views Vs. Standard Procedures

Jan 31, 2007

Hello,
I have a question where the answer may be simply opinion, or there may be strong reasons.  I guess that's why I'm asking the question... 
The question is - when should one create a view, and when should one use a stored procedure?  Obviously, when doing multiple queries or passing parameters, you must use a SP.  I'm talking about the case of a single query perhaps joins a couple of tables.
 
Hope that's not a stupid question...
 
Thanks in advance,
Paul

View 4 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Queries, Views, Procedures : Best Practice

Nov 23, 2007

Hello all,

I'm looking for a best practice.
Let's say you have a report that contains out of 3 queries.

How are you going to create the report and why?

1. Just use "select * from table where p1= value" in your report
2. Save the query in your db as a view and use the view in your report
3. Create a procedure that contains all 3 queries. And use the procedure with some parameters in your report?

Kr
Karel.

View 5 Replies View Related

Can't Import Views And Store Procedures

Aug 6, 2007

I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help

Thank

Andy Wong
awong@virginiadare.com

View 3 Replies View Related







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