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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s