Dynamic sql tips

@sqlCommand must be nvarchar!!!

Getting result of dynamic SQL into a variable for sql-server

DECLARE @sqlCommand nvarchar(1000)
DECLARE @city varchar(75)
declare @counts int
SET @city = 'New York'
SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75),@cnt int OUTPUT', @city = @city, @cnt=@counts OUTPUT
select @counts as Counts

for removing identity definition, we need to use union all select * from table where 1 = 0

timestamp filed cannot be inserted.

timestamp field cannot be altered to a different data type. Note that, one table can only have one timestamp filed. We need to drop the column and recreate it as varbinary. note that, varchar will be empty string.

add column will adding column at the end. selec * will not work. what I did is to use one temp table for holding  id and timestamp (convert to varbinary) and another temp table for all columns except for timestamp. using select * and drop timestamp column after that.

Insert into … Select *, how to ignore identity?

Insert into … Select *, how to ignore identity?

See answers here and here:

select * into without_id from with_id
union all
select * from with_id where 1 = 0

Reason:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

• The SELECT statement contains a join, GROUP BY clause, or aggregate function.
• Multiple SELECT statements are joined by using UNION.
• The identity column is listed more than one time in the select list.
• The identity column is part of an expression.
• The identity column is from a remote data source.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

All credit goes to Eric Humphrey and bernd_k

How to use table variable in a dynamic sql statement?

The approach is similar to pass table variable to stored procedure. Type needs to create table structure. As some case like schema change, it is better to use select into. 

How to use table variable in a dynamic sql statement?

On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don't need to update the values in the table itself.

So from the code you posted you could use this approach for @TSku but not for @RelPro

Example syntax below.

CREATE TYPE MyTable AS TABLE 
( 
Foo int,
Bar int
);
GO


DECLARE @T AS MyTable;

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

SELECT *,
        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
FROM @T

EXEC sp_executesql
  N'SELECT *,
        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
    FROM @T',
  N'@T MyTable READONLY',
  @T=@T 

The physloc column is included just to demonstrate that the table variable referenced in the child scope is definitely the same one as the outer scope rather than a copy.