Dynamic SQL to Insert Datetime or Binary Data

  

  --sample table
create table test (FileName NVARCHAR(50), CreateDate DateTime, [File]  Varbinary(MAX) )

--sample 1
--dynamic insert data (datetime)
Create PROCEDURE [dbo].[SetFilestreamData_datetime]  
	@TableName NVARCHAR(100) 
	, @FileName NVARCHAR(50)
	, @CreatedDate DateTime 
	as 
Begin
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO [dbo].' + quotename(@TableName) +  
            + '([FileName], [CreateDate]) VALUES (@FileName, @CreatedDate)'

EXEC sp_executesql @sql, N'@FileName NVARCHAR(50), @CreatedDate DateTime'
,@FileName=@FileName, @CreatedDate =@CreatedDate  
END

--execute sp
declare @CreatedDate DateTime = getdate()
EXEC [dbo].[SetFilestreamData_datetime] 'Test', 'Test1',@CreatedDate

--Sample 2
--dynamic insert data (binary)
Create PROCEDURE [dbo].[SetFilestreamData_varbinary]  
	@TableName NVARCHAR(100) 
	, @FileName NVARCHAR(50)
	, @FileData Varbinary(MAX)
	as 
Begin
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO [dbo].' + quotename(@TableName) +  
            + '([FileName], [File]) VALUES (@FileName, @FileData)'

EXEC sp_executesql @sql, N'@FileName NVARCHAR(50), @FileData Varbinary(MAX)'
,@FileName=@FileName, @FileData =@FileData  

END

--execute sp
declare @FileData Varbinary(MAX)=Cast('aaa' as Varbinary(MAX)) 
EXEC   [SetFilestreamData_varbinary] 'Test', 'testfilename',@FileData
 
Advertisements

Dynamic Count ModifiedDate Rows

  
   --*********begin of sample table and data
 create table LZO_a (id int, MODIFIEDAT date)
insert into LZO_a values (1, '2019-03-01'),(2, '2019-03-02'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-02'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-12'),(11, '2019-03-13'),(12, '2019-03-14') 

create table LZO_b (id int, MODIFIEDAT date)


insert into LZO_b values (1, '2019-03-01'),(2, '2019-03-01'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-03'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-05'),(11, '2019-03-05'),(12, '2019-03-14') 

 --************end of sample table and data

 


DECLARE @RunDate date = '2019-03-02' --change date parameter
 
DECLARE @sql1 nvarchar(4000)=N'' 
DECLARE @ColsPivot as NVarchar(4000)=null




 Select @sql1= 'Select tname,  cname , count(*) RowCnt into tmp_BUILDMODIFIED FROM ('+  Stuff((
SELECT  ' UNION ALL '+ char(13) + char(10)  + 'Select    ''' + quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME] ) 
+ ''' as tname, '  + quotename(c.COLUMN_NAME) +'  as cname, MODIFIEDAT   FROM   '
+ quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME]) + ' WHERE 1=1 and MODIFIEDAT >='''  + CONVERT(NVARCHAR(10),@RunDate)+''''
 + char(13) + char(10)  
 FROM [INFORMATION_SCHEMA].[TABLES]  t join [INFORMATION_SCHEMA].[COLUMNS] c 
  on t.[TABLE_SCHEMA] =c.[TABLE_SCHEMA] and t.[TABLE_NAME]=c.[TABLE_NAME]  
  WHERE TABLE_TYPE='BASE TABLE' and [DATA_TYPE] Like '%date%' and t.[TABLE_NAME] like'LZO_%' 
  and   t.[TABLE_NAME] NOT LIKE '%DJW%' AND t.[TABLE_NAME] NOT LIKE 'LZO_TRUST%'
  and c.COLUMN_NAME='MODIFIEDAT'
FOR XML PATH(''), type ).value('.', 'varchar(max)'),1,10,'')
   + ' ) t  group by tname,cname'

--create the temp table
EXEC sp_executesql @sql1 
 
 
Select @ColsPivot =N' Select tname,   '+STUFF( (SELECT distinct ',' + '  sum(CASE WHEN cname=' + quotename(cname,'''') + ' THEN RowCnt else 0 end ) as ' + quotename(cname,'[')  + char(10)+char(13)
                             FROM tmp_BUILDMODIFIED
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
							+ ' from tmp_BUILDMODIFIED group by tname '

--print @ColsPivot
EXEC sp_executesql @ColsPivot 

--clean up
IF OBJECT_ID('dbo.tmp_BUILDMODIFIED','U') IS NOT NULL 
DROP TABLE tmp_BUILDMODIFIED


 --drop sample tables
  drop table LZO_b,LZO_a

 

 
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aa0c4f82-cfd6-4922-9f64-4985fedf8a4c/how-do-i-pivot-date-rows-into-columns?forum=transactsql