Dynamic query in SQL

Dynamic query which nothing but conditional query which help to optimize query and improve performance of query.

Currently using  [AdventureWorksLT2008R2] datbase as following :

Try first

EXEC ('SELECT * FROM SalesLT.Product')

You can try following just for idea

DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024)

DECLARE @ListPrice money = 2000.0, @LastProduct varchar(64)

SET @SQL =       N'SELECT @pLastProduct = max(Name)

                   FROM SalesLT.Product

                   WHERE ListPrice >= @param1'

SET @ParmDefinition = N'@param1 money,

                        @pLastProduct varchar(64) OUTPUT'

EXECUTE sp_executeSQL      -- Dynamic T-SQL

            @SQL,

            @ParmDefinition,

            @param1 = @ListPrice,

            @pLastProduct=@LastProduct OUTPUT

            

SELECT [ListPrice >=]=@ListPrice, LastProduct=@LastProduct

Tips :

If you tried it, then also tried for Temp Table and Table variable for knowledge.

Query :

1)  DECLARE @T AS MyTable;

     INSERT INTO @T VALUES (1,2), (2,3)

     SELECT * FROM @T

     EXEC sp_executesql   N'SELECT *  FROM @T'

Error :  Declare Scalar variable @T

2)   Use  TYPE feature of SQL Server  for Table variable . Refer following query to execute sql statement with table variable

CREATE TYPE MyTable AS TABLE 

( 

Foo int,

Bar int

);

GO

DECLARE @T AS MyTable;

INSERT INTO @T VALUES (1,2), (2,3)

SELECT *  FROM @T

EXEC sp_executesql

  N'SELECT *  FROM @T',

 N'@T MyTable READONLY',

  @T=@T 

3)