Set in dynamic query value of variable declared outside dynamic queryScript to query multiple instancesHandling exceptions in stored procedures called using insert-exec blocksRun Multiple Remote JobsOracle GoldenGate add trandata errorsIs there any way to access the variables in a dynamic sql which is declared outside the dynamic sqlRound-robin T-SQL problem with a twistGroup By With Rollup results table has totals at the topRetrieve value of select query in a table columnInvestigating errors from strange queryHow to have more than 100 entries in case statement as a variable
What is the English pronunciation of "pain au chocolat"?
What happens if you are holding an Iron Flask with a demon inside and walk into an Antimagic Field?
What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?
Why is the "ls" command showing permissions of files in a FAT32 partition?
What exact color does ozone gas have?
Why does AES have exactly 10 rounds for a 128-bit key, 12 for 192 bits and 14 for a 256-bit key size?
Keeping a ball lost forever
Moving brute-force search to FPGA
Is this toilet slogan correct usage of the English language?
How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?
Fear of getting stuck on one programming language / technology that is not used in my country
How much character growth crosses the line into breaking the character
Why does a simple loop result in ASYNC_NETWORK_IO waits?
When were female captains banned from Starfleet?
The IT department bottlenecks progress. How should I handle this?
Yosemite Fire Rings - What to Expect?
How can "mimic phobia" be cured or prevented?
Why "had" in "[something] we would have made had we used [something]"?
Does Doodling or Improvising on the Piano Have Any Benefits?
Pre-mixing cryogenic fuels and using only one fuel tank
A social experiment. What is the worst that can happen?
Can I still be respawned if I die by falling off the map?
Mimic lecturing on blackboard, facing audience
Has any country ever had 2 former presidents in jail simultaneously?
Set in dynamic query value of variable declared outside dynamic query
Script to query multiple instancesHandling exceptions in stored procedures called using insert-exec blocksRun Multiple Remote JobsOracle GoldenGate add trandata errorsIs there any way to access the variables in a dynamic sql which is declared outside the dynamic sqlRound-robin T-SQL problem with a twistGroup By With Rollup results table has totals at the topRetrieve value of select query in a table columnInvestigating errors from strange queryHow to have more than 100 entries in case statement as a variable
I have a stored procedure which i simplified this way :
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET '+CAST(@variable1 AS VARCHAR)+' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)
But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.
thanks for help !
EDIT :
WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)
STORE PROC RETURN A VALUE
SET @variable1 with return value of store proc
and use it in second loop, third loop...
'
EXEC(@SQL)
END
sql-server t-sql
|
show 4 more comments
I have a stored procedure which i simplified this way :
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET '+CAST(@variable1 AS VARCHAR)+' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)
But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.
thanks for help !
EDIT :
WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)
STORE PROC RETURN A VALUE
SET @variable1 with return value of store proc
and use it in second loop, third loop...
'
EXEC(@SQL)
END
sql-server t-sql
2
Your@variable1
is null in this script; so, this :SET '+CAST(@variable1 AS VARCHAR)+
... will be null ; ps: CAST(AS VARCHAR) should be alwaysCAST(AS VARCHAR(XX))
– Sabin Bio
yesterday
ok but is there a way to go over that ?
– Matthieu RGX
yesterday
you should provide more details; you could initialize it with a value :DECLARE @variable1 INT =0
– Sabin Bio
yesterday
you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
– Matthieu RGX
yesterday
1
i've edit my question !
– Matthieu RGX
yesterday
|
show 4 more comments
I have a stored procedure which i simplified this way :
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET '+CAST(@variable1 AS VARCHAR)+' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)
But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.
thanks for help !
EDIT :
WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)
STORE PROC RETURN A VALUE
SET @variable1 with return value of store proc
and use it in second loop, third loop...
'
EXEC(@SQL)
END
sql-server t-sql
I have a stored procedure which i simplified this way :
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET '+CAST(@variable1 AS VARCHAR)+' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)
But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.
thanks for help !
EDIT :
WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)
STORE PROC RETURN A VALUE
SET @variable1 with return value of store proc
and use it in second loop, third loop...
'
EXEC(@SQL)
END
sql-server t-sql
sql-server t-sql
edited yesterday
Matthieu RGX
asked yesterday
Matthieu RGXMatthieu RGX
297
297
2
Your@variable1
is null in this script; so, this :SET '+CAST(@variable1 AS VARCHAR)+
... will be null ; ps: CAST(AS VARCHAR) should be alwaysCAST(AS VARCHAR(XX))
– Sabin Bio
yesterday
ok but is there a way to go over that ?
– Matthieu RGX
yesterday
you should provide more details; you could initialize it with a value :DECLARE @variable1 INT =0
– Sabin Bio
yesterday
you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
– Matthieu RGX
yesterday
1
i've edit my question !
– Matthieu RGX
yesterday
|
show 4 more comments
2
Your@variable1
is null in this script; so, this :SET '+CAST(@variable1 AS VARCHAR)+
... will be null ; ps: CAST(AS VARCHAR) should be alwaysCAST(AS VARCHAR(XX))
– Sabin Bio
yesterday
ok but is there a way to go over that ?
– Matthieu RGX
yesterday
you should provide more details; you could initialize it with a value :DECLARE @variable1 INT =0
– Sabin Bio
yesterday
you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
– Matthieu RGX
yesterday
1
i've edit my question !
– Matthieu RGX
yesterday
2
2
Your
@variable1
is null in this script; so, this : SET '+CAST(@variable1 AS VARCHAR)+
... will be null ; ps: CAST(AS VARCHAR) should be always CAST(AS VARCHAR(XX))
– Sabin Bio
yesterday
Your
@variable1
is null in this script; so, this : SET '+CAST(@variable1 AS VARCHAR)+
... will be null ; ps: CAST(AS VARCHAR) should be always CAST(AS VARCHAR(XX))
– Sabin Bio
yesterday
ok but is there a way to go over that ?
– Matthieu RGX
yesterday
ok but is there a way to go over that ?
– Matthieu RGX
yesterday
you should provide more details; you could initialize it with a value :
DECLARE @variable1 INT =0
– Sabin Bio
yesterday
you should provide more details; you could initialize it with a value :
DECLARE @variable1 INT =0
– Sabin Bio
yesterday
you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
– Matthieu RGX
yesterday
you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
– Matthieu RGX
yesterday
1
1
i've edit my question !
– Matthieu RGX
yesterday
i've edit my question !
– Matthieu RGX
yesterday
|
show 4 more comments
1 Answer
1
active
oldest
votes
Three things worth mentioning:
Always use
PRINT
to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holdingNULL
.DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
-- EXEC(@SQL)
The reason because the dynamic SQL is
NULL
is because you are concatenating a NULL value which is the@variable1
contents. I believe you wanted to write down the text'@variable1'
as literal:DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET @variable1 = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
- Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read
@variable1
since it's not declare anywhere. If we execute the dynamic SQL:
The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT
option. This will require to use the SP sp_executesql rathen than a direct EXEC
:
DECLARE @externalVariable INT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT = 1
SET @resultVariable = @variable2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'@resultVariable INT OUTPUT', -- Declare the "input" parameters for the dynamic SQL
@resultVariable = @externalVariable OUTPUT -- Supply the "input" parameters for the dynamic SQL
SELECT
Result = @externalVariable -- Read the updated value
Note that I changed data types to NVARCHAR
since sp_executesql
works with unicode inputs.
Another example with more parameters:
DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT
DECLARE @SQL NVARCHAR(MAX) = '
SET @multiplicationResult = @inputFactor1 * @inputFactor2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'
@multiplicationResult INT OUTPUT,
@inputFactor1 INT,
@inputFactor2 INT',
@multiplicationResult = @result OUTPUT,
@inputFactor1 = @firstNumber,
@inputFactor2 = @secondNumber
SELECT
Result = @result -- 45!
If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL
and literal values:
DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14
DECLARE @DynamicSQL VARCHAR(MAX) = '
SELECT
DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '
PRINT(@DynamicSQL)
EXEC(@DynamicSQL)
Printed:
SELECT
DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019 3:27PM'),
StringVariableContents = '',
FloatVariableContents = CONVERT(FLOAT, '15.124')
Result:
DateVariableContents StringVariableContents FloatVariableContents
2019-03-21 15:28:00.000 15.124
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232722%2fset-in-dynamic-query-value-of-variable-declared-outside-dynamic-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Three things worth mentioning:
Always use
PRINT
to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holdingNULL
.DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
-- EXEC(@SQL)
The reason because the dynamic SQL is
NULL
is because you are concatenating a NULL value which is the@variable1
contents. I believe you wanted to write down the text'@variable1'
as literal:DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET @variable1 = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
- Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read
@variable1
since it's not declare anywhere. If we execute the dynamic SQL:
The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT
option. This will require to use the SP sp_executesql rathen than a direct EXEC
:
DECLARE @externalVariable INT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT = 1
SET @resultVariable = @variable2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'@resultVariable INT OUTPUT', -- Declare the "input" parameters for the dynamic SQL
@resultVariable = @externalVariable OUTPUT -- Supply the "input" parameters for the dynamic SQL
SELECT
Result = @externalVariable -- Read the updated value
Note that I changed data types to NVARCHAR
since sp_executesql
works with unicode inputs.
Another example with more parameters:
DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT
DECLARE @SQL NVARCHAR(MAX) = '
SET @multiplicationResult = @inputFactor1 * @inputFactor2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'
@multiplicationResult INT OUTPUT,
@inputFactor1 INT,
@inputFactor2 INT',
@multiplicationResult = @result OUTPUT,
@inputFactor1 = @firstNumber,
@inputFactor2 = @secondNumber
SELECT
Result = @result -- 45!
If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL
and literal values:
DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14
DECLARE @DynamicSQL VARCHAR(MAX) = '
SELECT
DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '
PRINT(@DynamicSQL)
EXEC(@DynamicSQL)
Printed:
SELECT
DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019 3:27PM'),
StringVariableContents = '',
FloatVariableContents = CONVERT(FLOAT, '15.124')
Result:
DateVariableContents StringVariableContents FloatVariableContents
2019-03-21 15:28:00.000 15.124
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
add a comment |
Three things worth mentioning:
Always use
PRINT
to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holdingNULL
.DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
-- EXEC(@SQL)
The reason because the dynamic SQL is
NULL
is because you are concatenating a NULL value which is the@variable1
contents. I believe you wanted to write down the text'@variable1'
as literal:DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET @variable1 = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
- Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read
@variable1
since it's not declare anywhere. If we execute the dynamic SQL:
The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT
option. This will require to use the SP sp_executesql rathen than a direct EXEC
:
DECLARE @externalVariable INT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT = 1
SET @resultVariable = @variable2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'@resultVariable INT OUTPUT', -- Declare the "input" parameters for the dynamic SQL
@resultVariable = @externalVariable OUTPUT -- Supply the "input" parameters for the dynamic SQL
SELECT
Result = @externalVariable -- Read the updated value
Note that I changed data types to NVARCHAR
since sp_executesql
works with unicode inputs.
Another example with more parameters:
DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT
DECLARE @SQL NVARCHAR(MAX) = '
SET @multiplicationResult = @inputFactor1 * @inputFactor2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'
@multiplicationResult INT OUTPUT,
@inputFactor1 INT,
@inputFactor2 INT',
@multiplicationResult = @result OUTPUT,
@inputFactor1 = @firstNumber,
@inputFactor2 = @secondNumber
SELECT
Result = @result -- 45!
If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL
and literal values:
DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14
DECLARE @DynamicSQL VARCHAR(MAX) = '
SELECT
DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '
PRINT(@DynamicSQL)
EXEC(@DynamicSQL)
Printed:
SELECT
DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019 3:27PM'),
StringVariableContents = '',
FloatVariableContents = CONVERT(FLOAT, '15.124')
Result:
DateVariableContents StringVariableContents FloatVariableContents
2019-03-21 15:28:00.000 15.124
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
add a comment |
Three things worth mentioning:
Always use
PRINT
to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holdingNULL
.DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
-- EXEC(@SQL)
The reason because the dynamic SQL is
NULL
is because you are concatenating a NULL value which is the@variable1
contents. I believe you wanted to write down the text'@variable1'
as literal:DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET @variable1 = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
- Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read
@variable1
since it's not declare anywhere. If we execute the dynamic SQL:
The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT
option. This will require to use the SP sp_executesql rathen than a direct EXEC
:
DECLARE @externalVariable INT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT = 1
SET @resultVariable = @variable2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'@resultVariable INT OUTPUT', -- Declare the "input" parameters for the dynamic SQL
@resultVariable = @externalVariable OUTPUT -- Supply the "input" parameters for the dynamic SQL
SELECT
Result = @externalVariable -- Read the updated value
Note that I changed data types to NVARCHAR
since sp_executesql
works with unicode inputs.
Another example with more parameters:
DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT
DECLARE @SQL NVARCHAR(MAX) = '
SET @multiplicationResult = @inputFactor1 * @inputFactor2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'
@multiplicationResult INT OUTPUT,
@inputFactor1 INT,
@inputFactor2 INT',
@multiplicationResult = @result OUTPUT,
@inputFactor1 = @firstNumber,
@inputFactor2 = @secondNumber
SELECT
Result = @result -- 45!
If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL
and literal values:
DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14
DECLARE @DynamicSQL VARCHAR(MAX) = '
SELECT
DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '
PRINT(@DynamicSQL)
EXEC(@DynamicSQL)
Printed:
SELECT
DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019 3:27PM'),
StringVariableContents = '',
FloatVariableContents = CONVERT(FLOAT, '15.124')
Result:
DateVariableContents StringVariableContents FloatVariableContents
2019-03-21 15:28:00.000 15.124
Three things worth mentioning:
Always use
PRINT
to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holdingNULL
.DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
-- EXEC(@SQL)
The reason because the dynamic SQL is
NULL
is because you are concatenating a NULL value which is the@variable1
contents. I believe you wanted to write down the text'@variable1'
as literal:DECLARE @variable1 INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT
SET @variable2 = 1
SET @variable1 = @variable2
SELECT @variable1 as V1, @variable2 as V2
'
PRINT(@SQL)
- Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read
@variable1
since it's not declare anywhere. If we execute the dynamic SQL:
The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT
option. This will require to use the SP sp_executesql rathen than a direct EXEC
:
DECLARE @externalVariable INT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
DECLARE @variable2 INT = 1
SET @resultVariable = @variable2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'@resultVariable INT OUTPUT', -- Declare the "input" parameters for the dynamic SQL
@resultVariable = @externalVariable OUTPUT -- Supply the "input" parameters for the dynamic SQL
SELECT
Result = @externalVariable -- Read the updated value
Note that I changed data types to NVARCHAR
since sp_executesql
works with unicode inputs.
Another example with more parameters:
DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT
DECLARE @SQL NVARCHAR(MAX) = '
SET @multiplicationResult = @inputFactor1 * @inputFactor2'
EXEC sp_executesql
@stmt = @SQL,
@params = N'
@multiplicationResult INT OUTPUT,
@inputFactor1 INT,
@inputFactor2 INT',
@multiplicationResult = @result OUTPUT,
@inputFactor1 = @firstNumber,
@inputFactor2 = @secondNumber
SELECT
Result = @result -- 45!
If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL
and literal values:
DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14
DECLARE @DynamicSQL VARCHAR(MAX) = '
SELECT
DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '
PRINT(@DynamicSQL)
EXEC(@DynamicSQL)
Printed:
SELECT
DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019 3:27PM'),
StringVariableContents = '',
FloatVariableContents = CONVERT(FLOAT, '15.124')
Result:
DateVariableContents StringVariableContents FloatVariableContents
2019-03-21 15:28:00.000 15.124
edited yesterday
answered yesterday
EzLoEzLo
2,6641521
2,6641521
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
add a comment |
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
thanks a lot for your answer ! i will check it out and come back to you !
– Matthieu RGX
yesterday
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232722%2fset-in-dynamic-query-value-of-variable-declared-outside-dynamic-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Your
@variable1
is null in this script; so, this :SET '+CAST(@variable1 AS VARCHAR)+
... will be null ; ps: CAST(AS VARCHAR) should be alwaysCAST(AS VARCHAR(XX))
– Sabin Bio
yesterday
ok but is there a way to go over that ?
– Matthieu RGX
yesterday
you should provide more details; you could initialize it with a value :
DECLARE @variable1 INT =0
– Sabin Bio
yesterday
you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
– Matthieu RGX
yesterday
1
i've edit my question !
– Matthieu RGX
yesterday