Hi
I'm trying to create a stored procedure to use with a Crystal Report. To get the data I need, I need to first create either a temp table or a view before performing my main query. I would like to keep this within the stored procedure itself for easy maintenance.
What is the correct syntax to create/delete a temp table or a view from within a stored procedure that has parameters?
Here is the structure of my stored procedure:
CREATE PROCEDURE [MY_PROCEDURE]
(parameters here)
AS
(select statement here)
Here is the code for, say, a VIEW that I would like to include:
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'MY_VIEW')
DROP VIEW MY_VIEW
GO
CREATE VIEW MY_VIEW
AS
(select statement here)
GO
Is there any way to place that DROP VIEW/CREATE VIEW code within the CREATE PROCEDURE? I've tried it a few ways, and it keeps giving me errors. Thanks!
PTW
I'm trying to create a stored procedure to use with a Crystal Report. To get the data I need, I need to first create either a temp table or a view before performing my main query. I would like to keep this within the stored procedure itself for easy maintenance.
What is the correct syntax to create/delete a temp table or a view from within a stored procedure that has parameters?
Here is the structure of my stored procedure:
CREATE PROCEDURE [MY_PROCEDURE]
(parameters here)
AS
(select statement here)
Here is the code for, say, a VIEW that I would like to include:
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'MY_VIEW')
DROP VIEW MY_VIEW
GO
CREATE VIEW MY_VIEW
AS
(select statement here)
GO
Is there any way to place that DROP VIEW/CREATE VIEW code within the CREATE PROCEDURE? I've tried it a few ways, and it keeps giving me errors. Thanks!
PTW
