Sql-server – Use Stored Procedure or View to return Data from Multiple Tables

sql serverstored-proceduresview

Is it just down to preference for whether to use a View or a stored procedure (or TVF) to simply return data joined from multiple base tables?

Is there any best practice regarding this? Should a stored procedure be used just for manipulating data? And is it best to use a view for the purpose of returning data from multiple tables?

Are there also likely to be any performance issues?

Query:

SELECT
    t.TagID, t.TagName, 
    t.IsActive AS 'IsTagActive',
    tk.TaskID, tk.TaskName,
    tk.IsActive AS 'IsTaskActive',
    g.GroupID, g.GroupName
FROM 
    Model.[Tag] t
INNER JOIN
    Model.[TagTaskMapping] ttm ON ttm.TagID = t.TagID
INNER JOIN  
    Model.[Task] tk ON tk.TaskID = ttm.TaskID
INNER JOIN
    Model.[Group] g ON g.GroupID = t.GroupID

Best Answer

A view is a good approach for when you need to flatten or consistently address how various base tables are organized, essentially de-normalizing the data for the consuming application. Your example includes tasks, tags and group information as a cohesive whole.

Alternatively, views can be used to restrict data consistently. For example if the definition of an "open" purchase order was complex then a view could streamline that.

Stored Procedures and Table Valued Functions are quite a bit different. They can accept arguments and include branching or more complex logic while still offering a simple interface for the client applications. Stored procedures can also modify data (logging tables) if needed.

I have also seen stored procedures used as a method to avoid parameter sniffing or getting better execution plans. A master stored procedure which determines whether to call sub-procedure 1 for a small amount of data or sub procedure 2 for a large amount of data.

Table Valued Functions are more similar to Stored Procedures than views and accept arguments, but can be used in joins. Their performance is generally terrible and should be used cautiously.

Related Solutions

Sql-server – SQL Server 2000 DTS package, filter rows using stored procedure returned date

In SQL Server 2000, you cannot use the results of a stored proc directly in another query. However, you can insert them into a #temp table and then join to that. But you must create the temp table structure first, you can't use select into.

SQL Server – Stored Procedure to Return Dynamically Created Table Data

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t 
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script

Best Answer

Related Solutions

Sql-server – SQL Server 2000 DTS package, filter rows using stored procedure returned date

SQL Server – Stored Procedure to Return Dynamically Created Table Data

Related Question