[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using ALTER VIEW from within a stored procedure

Posted on 2010-01-07
5
Medium Priority
?
478 Views
Last Modified: 2012-06-22
Im looking to fire a stored procedure one per day.  It retrieves the primary key of records that have been added in the last 30 days. Im then looking to ALTER VIEW so that the view filters for records where the primary key is greater than the one just retieved.
Having issues with ALTER VIEW needing to be the first statement.
Using the PK opposed to just using date criteria is required for speed.
0
Comment
Question by:PeterMatthews
  • 2
4 Comments
 
LVL 4

Expert Comment

by:urban_smurf
ID: 26198831
Perhaps
"CREATE OR REPLACE VIEW"
is a better strategy. You could try it.

From the manual:
This statement creates a new view, or replaces an existing
one if the OR REPLACE clause is given. If the view does
not exist, CREATE OR REPLACE VIEW is the same as CREATE
VIEW. If the view does exist, CREATE OR REPLACE VIEW is
the same as ALTER VIEW.
0
 

Author Comment

by:PeterMatthews
ID: 26199788
Sorry only put on the tags that Im using SQL 2000 which doesnt appear to support this.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 26201702
You can alter your view like this:
 

alter procedure yourprocedure
as
... code blah blah
...
execute sp_executeSQL N'ALTER VIEW dbo.yourview AS SELECT column1, column2, column3 FROM dbo.yourtable;'

Open in new window

0
 

Author Closing Comment

by:PeterMatthews
ID: 31674147
Great that will do fine, thanks.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question