• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Using ALTER VIEW from within a stored procedure

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
PeterMatthews
Asked:
PeterMatthews
  • 2
1 Solution
 
urban_smurfCommented:
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
 
PeterMatthewsAuthor Commented:
Sorry only put on the tags that Im using SQL 2000 which doesnt appear to support this.
0
 
ralmadaCommented:
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
 
PeterMatthewsAuthor Commented:
Great that will do fine, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now