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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy