January 22, 2019 at 8:50 am
Hi All,
I'm trying to integrate a STUFF statement to generate column names along with some sql command (CASE statement). My @ColNames variables outputs what I need and I would like to integrate that in the @sqlquery as part of the SELECT statement but I'm not getting the result of @ColNames but the actual string.
DECLARE @GUID nvarchar(32)
DECLARE @DBName nvarchar(50)
DECLARE @ColNames nvarchar(max)
DECLARE @TableName nvarchar(40)
DECLARE @TimeZoneID int
DECLARE @sqlquery nvarchar(max)
SET @DBName = 'Example'
SET @TableName = 'Val_E7864189756'
SET @TimeZoneID = 84
SET @GUID = 'E7864189756'
SELECT @ColNames = ' STUFF(
(
SELECT DISTINCT
'',''+QUOTENAME(c.TagName)+ '' = SUM(CASE WHEN TagName=''+QUOTENAME(c.TagName,'''''''')+'' THEN Value END)''
FROM [Example].[dbo].[RawData_'+Rtrim(Ltrim(cast(@GUID as nvarchar(32))))+'] c FOR XML PATH(''''), TYPE
).value(''.'', ''nvarchar(max)''), 1, 1, '''')'
SET @sqlquery =' INSERT INTO ['+ Ltrim(rTrim(@NomBDD)) +'].[dbo].['+@TableName+']' +
' SELECT Timestamp, '+ @ColNames+ 'GROUP BY Timestamp ORDER BY Timestamp'
+
' UPDATE '+ Ltrim(rTrim(@DBName)) +'].[dbo].['+@TableName+']' +
'SET TIMESTAMP = [TSqlToolbox].[DateTimeUtil].UDF_ConvertUtcToLocalByTimezoneId('+Ltrim(Rtrim(CAST(@TimeZoneID as NVARCHAR(50))))+',Timestamp), '
EXEC sp_executesql @sqlquery
Debugging on @sqlquery outputs :
INSERT INTO [Example].[dbo].[Val_E7864189756] SELECT Timestamp, STUFF(
(
SELECT DISTINCT
','+QUOTENAME(c.TagName)+ ' = SUM(CASE WHEN TagName='+QUOTENAME(c.TagName,'''')+' THEN Value END)'
nvarchar(max)
Any ideas of what I'm doing wrong ?
Thanks !
prefet
January 22, 2019 at 9:16 am
Cheers for the tip. It's trying to execute @ColNames as the literal string whereas I would like to feed the results of @ColNames. Maybe using something like an output ?
INSERT INTO [Example].[dbo].[Val_E7864189756]
SELECT Timestamp, STUFF(
(
SELECT DISTINCT
','+QUOTENAME(c.TagName)+ ' = SUM(CASE WHEN TagName='+QUOTENAME(c.TagName,'''')+' THEN Value END)'
FROM [HyperReader].[dbo].[Val_E7864189756] c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '')
GROUP BY Timestamp
ORDER BY Timestamp
UPDATE Example.[dbo].[Val_E7864189756]
SET TIMESTAMP = [TSqlToolbox].[DateTimeUtil].UDF_ConvertUtcToLocalByTimezoneId(94,Timestamp),
January 22, 2019 at 9:45 am
can you provide DDL, sample data and expected results? Otherwise, I would break each part down to make sure it is returning what you are expecting. Sounds like something is off
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 22, 2019 at 9:53 am
prefet - Tuesday, January 22, 2019 9:16 AMCheers for the tip. It's trying to execute @ColNames as the literal string whereas I would like to feed the results of @ColNames. Maybe using something like an output ?
INSERT INTO [Example].[dbo].[Val_E7864189756]
SELECT Timestamp, STUFF(
(
SELECT DISTINCT
','+QUOTENAME(c.TagName)+ ' = SUM(CASE WHEN TagName='+QUOTENAME(c.TagName,'''')+' THEN Value END)'
FROM [HyperReader].[dbo].[Val_E7864189756] c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '')
GROUP BY Timestamp
ORDER BY TimestampUPDATE Example.[dbo].[Val_E7864189756]
SET TIMESTAMP = [TSqlToolbox].[DateTimeUtil].UDF_ConvertUtcToLocalByTimezoneId(94,Timestamp),
The string contained in @sqlquery should be something that you can just paste into SSMS and run. If you can't do that it's not going to execute with sp_executesql .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply