Create View using Stored Procedure dynamically

We can create view using stored procedure dynamically.

Please find my sample below:
I want to create a view name v_rpt + @Type and @Type as parameter. If the view already exists, it will be drop and recreate a new one.

We will need to execute @SQL twice

• execution 1: check whether the view exists, drop it if exists
• execution 2: create a new view

if we put both in the same query, it will hit error "'CREATE VIEW' must be the first statement in a query batch."


CREATE PROCEDURE [dbo].psp_Generate_view
(
@Type VARCHAR(255)
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = ''
SET @SQL = @SQL + 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N''[dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + ']'') AND OBJECTPROPERTY(id, N''IsView'') = 1) ' + char(13)
SET @SQL = @SQL + 'DROP VIEW [dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + '] ' + char(13)

EXECUTE (@SQL)

SET @SQL = ''
SET @SQL = @SQL + 'CREATE VIEW dbo.v_rpt' + CONVERT( VARCHAR, @Type) + ' AS ' + char(13)
SET @SQL = @SQL + 'SELECT DISTINCT '
SET @SQL = @SQL + 's.SKUNo, '
SET @SQL = @SQL + 's.Model, '
SET @SQL = @SQL + 'lk1.LookupDesc [Region], '
SET @SQL = @SQL + 'lk2.LookupDesc [Country], '
SET @SQL = @SQL + 'CASE s.Status '
SET @SQL = @SQL + ' WHEN 1 THEN ''Saved'' '
SET @SQL = @SQL + ' WHEN 2 THEN ''Rejected'''
SET @SQL = @SQL + ' WHEN 3 THEN ''Approved'''
SET @SQL = @SQL + 'END AS Status, '
SET @SQL = @SQL + 's.Remark, '
SET @SQL = @SQL + 'u1.FullName [Creator], '
SET @SQL = @SQL + 's.CreDte [Date of Creation], '
SET @SQL = @SQL + 'u2.FullName [UpdatedBy], '
SET @SQL = @SQL + 's.LastUpdDte [Updated On], '
SET @SQL = @SQL + 'FROM tblSKU s '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u1 ON s.OwnerID = u1.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u2 ON s.LastUserID = u2.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk1 ON s.Region = lk1.RefIdn AND lk1.GroupIdn = 1 '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk2 ON s.CTY = lk2.RefIdn AND lk2.GroupIdn = 2 '
SET @SQL = @SQL + 'WHERE '
SET @SQL = @SQL + 's.Type= ' + CONVERT( VARCHAR, @Type) + ' AND '

EXECUTE (@SQL)


SET NOCOUNT OFF
END

Please let me know if you have any comment.