Saturday, 12 July 2014

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)  


No comments:

Post a Comment