Store CURSOR results into Stored Procedure or View I have this SQL code that creates a set of results from a dynamic table range. I want to know if I can create a view from this or a stored procedure?

DECLARE @CommonTablePrefix SYSNAME = 'IncidentResolvedByUserFact_'
DECLARE @SQL VARCHAR(MAX);

SET @SQL = '';
DECLARE TBL_CURS CURSOR FOR
   SELECT [Name] FROM SYs.tables Where name like @CommonTablePrefix + '%';

OPEN TBL_CURS;
DECLARE @TBL_INSTANCE SYSNAME;
DECLARE @FIRST BIT = 1;
FETCH TBL_CURS INTO @TBL_INSTANCE;
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @FIRST = 1 
   BEGIN
          SET @FIRST = 0;
   END
   ELSE
   BEGIN
          SET @SQL = @SQL + ' UNION ALL ';
   END

   SET @SQL = @SQL + 'SELECT * FROM ' + @TBL_INSTANCE;
   FETCH TBL_CURS INTO @TBL_INSTANCE;
END
CLOSE TBL_CURS;
DEALLOCATE TBL_CURS;
EXEC(@SQL);
You should be able to wrap your code into a stored procedure that accepts your 'CommonTablePrefix' as an input parameter fairly easily.

CREATE PROCEDURE ReturnCommonTablePrefixResults (@CommonTablePrefix SYSNAME)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX);

    SET @SQL = '';

    DECLARE TBL_CURS CURSOR
    FOR
    SELECT [Name]
    FROM SYs.tables
    WHERE NAME LIKE @CommonTablePrefix + '%';

    OPEN TBL_CURS;

    DECLARE @TBL_INSTANCE SYSNAME;
    DECLARE @FIRST BIT = 1;

    FETCH TBL_CURS
    INTO @TBL_INSTANCE;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @FIRST = 1
        BEGIN
            SET @FIRST = 0;
        END
        ELSE
        BEGIN
            SET @SQL = @SQL + ' UNION ALL ';
        END

        SET @SQL = @SQL + 'SELECT * FROM ' + @TBL_INSTANCE;

        FETCH TBL_CURS
        INTO @TBL_INSTANCE;
    END

    CLOSE TBL_CURS;

    DEALLOCATE TBL_CURS;

    EXEC (@SQL);
END


Then, you'd simply execute the stored procedure and supply the 'variable' @CommonTablePrefix = 'IncidentResolvedByUserFact_'

EXEC ReturnCommonTablePrefixResults 'IncidentResolvedByUserFact_'