Roust_m (5/4/2015)
Hi,I am trying to do the following:
DECLARE @MinBoundary DateTime, @s-2 nvarchar(max)declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s-2 = '
SELECT Convert(DateTime, MIN(PRV.value))
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
WHERE PF.name IN (''WBC_' + @TableName + '_pf'')'
print @s-2
EXECUTE sp_executesql @s-2, N'@MB DateTime OUTPUT', @MB=@MinBoundary OUTPUT
PRINT @MinBoundary
What could be wrong?
Thanks.
The error is that the variable @MB is not used in the dynamic query, it is missing!
😎
DECLARE @MinBoundary DateTime, @s-2 nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s-2 = '
SELECT @MB = Convert(DateTime, MIN(PRV.value))
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
WHERE PF.name IN (''WBC_' + @TableName + '_pf'')'
print @s-2
EXECUTE sp_executesql @s-2, N'@MB DateTime OUTPUT', @MB=@MinBoundary OUTPUT
select @MinBoundary
If there is no partition function for the query predicates then it will of course return NULL.