Use Stored Procedure To Create View

Question

I want to create a stored procedure which I can pass a parameter to for the database name and it will create a view for me.

I am just trying to save some time by not writing the same statement over and over and over for a create vew.

Below is my syntax - how could this be modified to run in a stored procedure accepting a parameter?

Alter View dbo.ForceClose
As
SELECT DISTINCT(SessionID) As CountofSessionID
FROM Database1      
WHERE forceClosed IS NOT NULL 
AND stillOpen IS NULL
and (userName is not null or userName IN ('JJones', 'MHill', 'RMort'))
Go

And in the stored procedure accept a parameter as the database name (I know this isn't valid just trying to show an example) -- call the stored procedure like so

exec dbo.Procedure 'DBName'

And the stored procedure would then look like

@DBName varchar(100)
Select blah blah FROM' + @DBName + '

Answer 1

You mean this?

CREATE PROCEDURE dbo.ForceClose
(
    @DBNAME NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @SQL AS NVARCHAR(255)

    @SQL='SELECT DISTINCT(SessionID) As CountofSessionID
    FROM ['+@DBNAME+'].[SCHEMA].[TABLE_NAME]
    WHERE forceClosed IS NOT NULL 
    AND stillOpen IS NULL
    and (userName is not null or userName IN (''JJones'', ''MHill'', ''RMort''))'
    EXEC (@SQL)
END

And you can run it by:

EXEC ForceClose <DBNAME>

With the View:

CREATE PROCEDURE dbo.ForceClose
(
    @DBNAME NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @SQL AS NVARCHAR(255)

    @SQL='CREATE VIEW VIEW_'+DBNAME+' AS SELECT DISTINCT(SessionID) As CountofSessionID
    FROM ['+@DBNAME+'].[SCHEMA].[TABLE_NAME]
    WHERE forceClosed IS NOT NULL 
    AND stillOpen IS NULL
    and (userName is not null or userName IN (''JJones'', ''MHill'', ''RMort''))'
    EXEC (@SQL)
END

And you can run it by:

SELECT * FROM VIEW_<DBNAME>