Hi,
A stored procedure cannot be created with the create View command in it .
But we could create a t-sql string with the create View statement and execute the string with sp_executesql
-refer to http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/af8b0c2d-8117-42e3-a701-05035644f73d
Solutions :
ALTER PROCEDURE [dbo].[prc_TEST]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
--SET ANSI_NULLS ON
--SET QUOTED_IDENTIFIER ON
DECLARE @sql NVARCHAR(max)
SELECT @sql='IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''[dbo].[IView_LISTING]''))
DROP VIEW [dbo].[I IView_LISTING ]
'
EXEC(@sql);
SELECT @sql = N'
CREATE VIEW [dbo].[IView_LISTING] WITH SCHEMABINDING
AS
XXXXXX
'
EXEC (@sql);
SELECT @sql=N'CREATE UNIQUE CLUSTERED Index PK_IView_LISTING_FEED ON IView_LISTING
(FEED_ID);'
EXEC (@sql);
END