Dynamic SQL - Exec(@Sql) Versus Exec Sp_Executesql(@Sql)

Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

sp_executesql is more likely to promote query plan reuse. When using sp_executesql, parameters are explicitly identified in the calling signature. This excellent article descibes this process.

The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".

Execute dynamic query with IF-ELSE statements and sp_executesql command

You have the right idea and are headed in the right direction. I figured I'd give you a hand to help you learn how I would approach this.

Here is my version of your stored procedure:

CREATE OR ALTER PROCEDURE [dbo].[usp_dynamicquery1] (
    @TableName NVARCHAR(50),
    @Field NVARCHAR(100) = NULL,
    @Criterion NVARCHAR(100) = NULL,
    @Parameter NVARCHAR(100) = NULL,
    @All VARCHAR(2) = '-1'
)
AS          
BEGIN

    SET NOCOUNT ON;  
 
    DECLARE 
        @SQL NVARCHAR(MAX),
        @SQL_WHERE NVARCHAR(MAX),
        @ParameterDef NVARCHAR(500);

    SET @ParameterDef = '@Parameter NVARCHAR(100)'
    SET @SQL = 'SELECT * FROM ' + @TableName;
    SET @SQL_WHERE = '';

    /* BUILD THE WHERE CLAUSE IF @Field IS PRESENT */

    IF NULLIF ( @Field, '' ) IS NOT NULL BEGIN

        -- Field.
        SET @SQL_WHERE = ' WHERE ' + @Field;

        -- Field Comparison.
        SET @SQL_WHERE += CASE @Criterion 
            WHEN 'greater than' THEN ' >'
            WHEN 'greater than or equal' THEN ' >='
            WHEN 'less than' THEN ' <'
            WHEN 'less than or equal' THEN ' <='
            WHEN 'not equal' THEN ' <>'
            ELSE ' ='
        END;

        -- Field Parameter.
        SET @SQL_WHERE += ' @Parameter';

        -- Set @Parameter value.
        SET @Parameter =
            CASE WHEN NULLIF ( @Parameter, '' ) IS NOT NULL
                THEN @Parameter
                ELSE @All
            END;

    END

    -- Finish SQL statement.
    SET @SQL = @SQL + ISNULL ( @SQL_WHERE, '' ) + ';';

    -- Execute the dynamic statement.
    EXEC sp_executesql @SQL, @ParameterDef, @Parameter = @Parameter;

END
GO

A few quick things worth noting:

• @TableName must be required, otherwise, what do you intend to query?

• The WHERE clause is only constructed if @Field is present.

• Shortened the @ParameterDef variable to include @Parameter only. The rest of the t-sql is formed prior to calling sp_execute_sql. I left @Parameter so that you wouldn't have to worry about single ticks around values, etc.

• Added @All as a DEFAULTed parameter (-1) to the SP to the value can be changed if wanted.

T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql

Here is a fully functional example of how you can do this. Notice this is using a parameterized where clause and quotename around the column name in the dynamic sql to prevent sql injection.

if OBJECT_ID('tempdb..#Agents') is not null
    drop table #Agents

create table  #Agents 
(
    AgentNumber char(10)
    , Level1Agent char(10)
    , Level2Agent char(10)
    , Level3Agent char(10)
    , Level4Agent char(10)
    , Level5Agent char(10)
)

insert #Agents
select '1122334455', '1122334499', '1122334488', '1122334477', '1122334466', '1122334455'

DECLARE @level INT = 3;
DECLARE @agent CHAR(10) = 1122334455;
DECLARE @colname NVARCHAR(11) = CONCAT('Level',@level,'Agent');

declare @agentout char(10)

DECLARE @qry NVARCHAR(300) = 'SELECT @agent_out = ' + quotename(@colname) + ' FROM #Agents WHERE AgentNumber = @agentin';

EXECUTE sp_executesql @qry, N'@agentin char(10), @agent_out char(10) output', @agentin = @agent, @agent_out = @agentout output

select @agentout

Stored procedure EXEC vs sp_executesql difference?

Your sp_executesql SQL should probably be;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=@eStatus'

This will allow you to call sp_executesql with @eStatus as a parameter instead of embedding it into the SQL. That will give the advantage that @eStatus can contain any characters and it will be properly escaped automatically by the database if required to be secure.

Contrast that to the SQL required for EXEC;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=' + char(39) + @Status + char(39)

...where a char(39) embedded in @Status will make your SQL invalid and possibly create an SQL injection possibility. For example, if @Status is set to O'Reilly, your resulting SQL would be;

select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'

exec sp_executesql @sql and exec (@sql) SQL Server

Because EXEC sp_executesql will cache the query plan -- EXEC will not. For more info, and a very good read, see:

• The Curse and Blessings of Dynamic SQL

Caching a query means that the logistics to the query are temporarily stored, and make running the query later on faster for it.

SQL Server use EXEC/sp_executesql or just plain sql in stored procedure?

See no reason to use dynamic SQL here. When you do need to use dynamic SQL, you should consider sp_executesql higher in preference than EXEC(). There are a variety of reasons, including:

1. sp_executesql is more likely to reuse query plans (see Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL));

2. it is much easier to pass strongly-typed parameters into sp_executesql (thwarting SQL injection better than concatenating a string); and,

3. you can also get variables from within the dynamic SQL scope back out to the calling scope, for example:

DECLARE @i INT, @sql NVARCHAR(MAX), @dbname SYSNAME = N'model';

SET @sql = N'SELECT @i = COUNT(*) FROM ' 
    + @dbname + '.sys.tables;' 

EXEC sp_executesql @sql, N'@i INT OUTPUT', @i = @i OUTPUT;

PRINT @i;

That's not a very useful example, but it is a common problem when executing dynamic strings. But more to the point, you should only be considering dynamic SQL when you have to, not as a first resort.

Does sp_executesql support multiple values in one parameter and return multiple records?

I was trying to retrive the rows whose id matches within the IN clause.

SET @INClauseIds='''' + replace(@Ids, ',', ''',''') + ''''  

Above statement would convert the ID's ='1,2,3' to '1','2','3' which i can directly place in the IN clause.

SET @SQLText1 ='EXEC(''SELECT  Name,SEOFriendlyName FROM SKU Where Id IN ( ''+ @Ids+'' ) )'             

 EXEC sp_executesql @SQLText1 ,N'@INClauseIds nvarchar(max)',@Ids=@INClauseIds 

If you want to avoid the usage of Temp Table which would add extra caliculation time. you can you the above strategy to retrive n number of records. Safe with strongly coupled with sp_executesql and without any sql injection.

---

Related Topics