UPDATE TABLE USING SP_EXECUTEQUERY

Question

Post reply

UPDATE TABLE USING SP_EXECUTEQUERY

taufikalfarisi2

SSC Veteran

Points: 275
More actions
August 21, 2016 at 1:42 am

#329465

Good afternoon,
I Have problem with this case,
anyone can help analyze or has another solution
DECLARE
@TypeCodevarchar(25),
@BomDateBvarchar(25),
@BomDateAvarchar(25),
@TbNamevarchar(25),
@SQLvarchar(max)
SET @TypeCode= 'PS-BPRG15AGW'
SET @TbName= 'z'+@TypeCode
SET @BomDateB= '8/19/2016'
SET @BomDateA= '8/20/2016'
SET @SQL= '
UPDATE [PMLite].[dbo].['+@TbName+']
SET [BOM Date] = '+@BomDateA+'
WHERE [BOM Date] = '+@BomDateB+'
'
EXEC sp_executesql @SQL

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

taufikalfarisi2 SSC Veteran Points: 275 More actions · Answer 1

it was solved with

DECLARE

@TypeCode varchar(25),

@BomDateB varchar(25),

@BomDateA varchar(25),

@TbName varchar(25),

@SQL nvarchar(max)

SET @TypeCode = 'PS-BPRG15AGW'

SET @TbName = 'z'+@TypeCode

SET @BomDateB = '8/19/2016'

SET @BomDateA = '8/20/2016'

SET @SQL = 'UPDATE [PMLite].[dbo].['+@TbName+']

SET [BOM Date] = '+''''+CONVERT(nvarchar(24), @BomDateA, 121)+''''+'

WHERE [BOM Date] = '+''''+CONVERT(nvarchar(24), @BomDateB, 121)+''''+'

'

EXEC sp_executesql @SQL

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 2

taufikalfarisi2 (8/21/2016)
it was solved with
DECLARE
@TypeCode varchar(25),
@BomDateB varchar(25),
@BomDateA varchar(25),
@TbName varchar(25),
@SQL nvarchar(max)
SET @TypeCode = 'PS-BPRG15AGW'
SET @TbName = 'z'+@TypeCode
SET @BomDateB = '8/19/2016'
SET @BomDateA = '8/20/2016'
SET @SQL = 'UPDATE [PMLite].[dbo].['+@TbName+']
SET [BOM Date] = '+''''+CONVERT(nvarchar(24), @BomDateA, 121)+''''+'
WHERE [BOM Date] = '+''''+CONVERT(nvarchar(24), @BomDateB, 121)+''''+'
'
EXEC sp_executesql @SQL

Although this may work it is not a good solution and does not benefit from using sp_executesql, below is an example of how to use the parameter option and the quotename function, suggest you look those up on BOL.

😎

DECLARE @BomDateB DATE = CONVERT(DATE,'8/19/2016',101);

DECLARE @BomDateA DATE = CONVERT(DATE,'8/20/2016',101);

DECLARE @TbName NVARCHAR(128) = N'PS-BPRG15AGW';

DECLARE @PARAM_STR NVARCHAR(MAX) = N'@BomDateA DATE, @BomDateB DATE';

DECLARE @SQL NVARCHAR(max) = N'UPDATE [PMLite].[dbo].' + QUOTENAME(@TbName) + N'

SET [BOM Date] = @BomDateA

WHERE [BOM Date] = @BomDateA

; '

EXEC sp_executesql @SQL, @PARAM_STR, @BomDateA, @BomDateB;

Edit: Typo