Search This Blog

Wednesday, April 23, 2008

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.

4 comments:

JAKECH ROBERT said...

How can i create a view using a stored procedure results.
I have a stored procedure which returns values. i want to create a view from the results of the stored procedure. How can i do it?
Jakech

In blues said...

Hi, not quite understand your question. Can you elaborate more or do you have example what you want?
Thanks

Anonymous said...

could u please let me know how to include dates and integer values in the query?
startdate,enddate are date variables and intcomanyid integer variable
Eg: select sum(debit-credit) as balance where trdate>=startdate and trdate<=enddate and ncompanyId=intcomanyid

In blues said...

Hi, i think it's not hard, it's like normal query. You just need to pass in the parameter & generate the view.
example:
CREATE PROCEDURE [dbo].psp_Generate_view
(
@Type VARCHAR(255)
@CompanyID INT,
@startdate DATETIME,
@enddate
)
AS

....
...
...
...
@SQL = select sum(debit-credit) as balance where trdate>=@startdate and trdate<=@enddate and ncompanyId=@CompanyID

....
....

hope this can help