Trying to pass a value to a variable from dynamic sql

Question

Post reply

Trying to pass a value to a variable from dynamic sql

Roust_m

SSCoach

Points: 17410
More actions
May 4, 2015 at 12:37 am

#303590

Hi,
I am trying to do the following:
DECLARE @MinBoundary DateTime, @s nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s = '
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
EXECUTE sp_executesql @s, N'@MB DateTime OUTPUT', @MB=@MinBoundary OUTPUT
PRINT @MinBoundary
@Minboundary variable turns out to be null in the end.
It works if I print @s-2 query and run it this way, so the query is correct):
DECLARE @MinBoundary DateTime
SELECT @MinBoundary = 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_MyTable_pf')
select @MinBoundary
What could be wrong?
Thanks.

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

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

please refer to Eirikur response, i need more coffee i guess 🙂

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

Roust_m (5/4/2015)
Hi,
I am trying to do the following:
DECLARE @MinBoundary DateTime, @s nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s = '
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
EXECUTE sp_executesql @s, 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 nvarchar(max)

declare @DBName sysname, @TableName sysname

select @DBName = 'MyDB', @TableName = 'MyTable'

select @s = '

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

EXECUTE sp_executesql @s, 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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Quick questions:

1. Why are you using Dynamic SQL when a normal query would be enough?

2. Why aren't you parametrizing your Dynamic SQL leaving it open to SQL Injection?

3. Why are you using IN for a single value?

Static SQL example:

SELECT @MinBoundary = 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 = 'WBC_' + @TableName + '_pf'

PRINT @MinBoundary

Dynamic SQL example parametrized query:

DECLARE @MinBoundary DateTime, @s nvarchar(max)

declare @DBName sysname, @TableName sysname

select @DBName = 'MyDB', @TableName = 'MyTable'

select @s = '

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_'' + @TN + ''_pf'')'

print @s

EXECUTE sp_executesql @s, N'@MB DateTime OUTPUT, @TN sysname', @MB=@MinBoundary OUTPUT, @TN=@TableName

PRINT @MinBoundary

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Roust_m SSCoach Points: 17410 More actions · Answer 4

Eirikur Eiriksson (5/4/2015)
The error is that the variable @MB is not used in the dynamic query, it is missing!
😎

Thanks!

Roust_m SSCoach Points: 17410 More actions · Answer 5

Luis Cazares (5/4/2015)
Quick questions:
1. Why are you using Dynamic SQL when a normal query would be enough?
2. Why aren't you parametrizing your Dynamic SQL leaving it open to SQL Injection?
3. Why are you using IN for a single value?
Static SQL example:
SELECT @MinBoundary = 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 = 'WBC_' + @TableName + '_pf'
PRINT @MinBoundary
Dynamic SQL example parametrized query:
DECLARE @MinBoundary DateTime, @s nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s = '
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_'' + @TN + ''_pf'')'
print @s
EXECUTE sp_executesql @s, N'@MB DateTime OUTPUT, @TN sysname', @MB=@MinBoundary OUTPUT, @TN=@TableName
PRINT @MinBoundary

Thanks for your input. All valid questions.

1. I used dynamic sql because the table name is changing, but after closer look, you are right, static query should be enough.

2. Definitely need to improve on this.

3. The code was taken from another script, which I developed earlier. There may be multiple values if the logic changes.

Xedni SSCertifiable Points: 6951 More actions · Answer 6

If you've got multiple values, I'd throw them in a temp table and join out to that, rather than concatenated them and use DSQL.

Executive Junior Cowboy Developer, Esq.[/url]