June 8, 2016 at 10:36 am
Good day,
I am having difficulty understanding how to make available a parameter set with dynamic T-SQL.
I expect value of the [font="Courier New"]@s_MaxOrderOut[/font] to be already defined in the [font="Courier New"]SELECT @s_MaxOrderOut = MAX(...
[/font]
But the last line of the [font="Courier New"]sp_excutesql [/font]statement appears to replace this parameter's value with the (undefined, thus NULL) value of the "destination" parameter defined outside of the list of parameters defined for the sp_executesql:
[font="Courier New"]...@s_MaxOrderOut = @s_Max_SO_No OUTPUT; ???[/font]
Instead of copying the [font="Courier New"]@s_MaxOrderOut[/font] value to the destination parameter [font="Courier New"]@s_Max_SO_No OUTPUT[/font].
How does that T-SQL statement read ? - sort of right-argument equal sign left-argument ?
CREATE TABLE #t
(
SalesOrderID [int] IDENTITY(1,1) NOT NULL,
CustomerID int,
SalesOrderNumber varchar (25)
) ;
INSERT INTO #t (CustomerID, SalesOrderNumber)
VALUES (100, 12300), (100, 12399), (101, 12350) ;
DECLARE @n_SQL nvarchar(MAX) = N'SELECT @s_MaxOrderOut = MAX(SalesOrderNumber) FROM #t WHERE CustomerID = @i_CID' ;
DECLARE @n_Params nvarchar(MAX) = N'@i_CID int, @s_MaxOrderOut varchar(25) OUTPUT' ;
DECLARE @i_Cust int = 100 ;
DECLARE @s_Max_SO_No varchar(25) ;
EXECUTE sp_executesql @n_SQL, @n_Params, @i_Cust, @s_MaxOrderOut = @s_Max_SO_No OUTPUT;
SELECT @s_Max_SO_No;
June 8, 2016 at 10:47 am
j-1064772 (6/8/2016)
Good day,I am having difficulty understanding how to make available a parameter set with dynamic T-SQL.
I expect value of the [font="Courier New"]@s_MaxOrderOut[/font] to be already defined in the [font="Courier New"]SELECT @s_MaxOrderOut = MAX(...
[/font]
But the last line of the [font="Courier New"]sp_excutesql [/font]statement appears to replace this parameter's value with the (undefined, thus NULL) value of the "destination" parameter defined outside of the list of parameters defined for the sp_executesql:
[font="Courier New"]...@s_MaxOrderOut = @s_Max_SO_No OUTPUT; ???[/font]
Instead of copying the [font="Courier New"]@s_MaxOrderOut[/font] value to the destination parameter [font="Courier New"]@s_Max_SO_No OUTPUT[/font].
How does that T-SQL statement read ? - sort of right-argument equal sign left-argument ?
CREATE TABLE #t(
SalesOrderID [int] IDENTITY(1,1) NOT NULL,
CustomerID int,
SalesOrderNumber varchar (25)
) ;
INSERT INTO #t (CustomerID, SalesOrderNumber)
VALUES (100, 12300), (100, 12399), (101, 12350) ;
DECLARE @n_SQL nvarchar(MAX) = N'SELECT @s_MaxOrderOut = MAX(SalesOrderNumber) FROM #t WHERE CustomerID = @i_CID' ;
DECLARE @n_Params nvarchar(MAX) = N'@i_CID int, @s_MaxOrderOut varchar(25) OUTPUT' ;
DECLARE @i_Cust int = 100 ;
DECLARE @s_Max_SO_No varchar(25) ;
EXECUTE sp_executesql @n_SQL, @n_Params, @i_Cust, @s_MaxOrderOut = @s_Max_SO_No OUTPUT;
SELECT @s_Max_SO_No;
Why are you using dynamic sql at all? From what you posted there is no need whatsoever for dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 8, 2016 at 11:04 am
j-1064772 (6/8/2016)
Good day,I am having difficulty understanding how to make available a parameter set with dynamic T-SQL.
I expect value of the [font="Courier New"]@s_MaxOrderOut[/font] to be already defined in the [font="Courier New"]SELECT @s_MaxOrderOut = MAX(...
[/font]
But the last line of the [font="Courier New"]sp_excutesql [/font]statement appears to replace this parameter's value with the (undefined, thus NULL) value of the "destination" parameter defined outside of the list of parameters defined for the sp_executesql:
[font="Courier New"]...@s_MaxOrderOut = @s_Max_SO_No OUTPUT; ???[/font]
Instead of copying the [font="Courier New"]@s_MaxOrderOut[/font] value to the destination parameter [font="Courier New"]@s_Max_SO_No OUTPUT[/font].
How does that T-SQL statement read ? - sort of right-argument equal sign left-argument ?
CREATE TABLE #t(
SalesOrderID [int] IDENTITY(1,1) NOT NULL,
CustomerID int,
SalesOrderNumber varchar (25)
) ;
INSERT INTO #t (CustomerID, SalesOrderNumber)
VALUES (100, 12300), (100, 12399), (101, 12350) ;
DECLARE @n_SQL nvarchar(MAX) = N'SELECT @s_MaxOrderOut = MAX(SalesOrderNumber) FROM #t WHERE CustomerID = @i_CID' ;
DECLARE @n_Params nvarchar(MAX) = N'@i_CID int, @s_MaxOrderOut varchar(25) OUTPUT' ;
DECLARE @i_Cust int = 100 ;
DECLARE @s_Max_SO_No varchar(25) ;
EXECUTE sp_executesql @n_SQL, @n_Params, @i_Cust, @s_MaxOrderOut = @s_Max_SO_No OUTPUT;
SELECT @s_Max_SO_No;
Not sure what the problem is with your code, other than what Sean said about not needing to use dynamic SQL based on what you posted. Yes, it looks funning, but you should being seeing the correct value when the select @s_Max_SO_No; query runs.
June 8, 2016 at 11:36 am
I have used a trivial example - should have considered that the normal reaction would be that dynamic T-SQL is totally unwarranted for something so silly. I agree.
I already checked prior to posting it that it does work - I just don't understand that code and for a warranted application of dynamic T-SQL, I would end up writing code I know works but not fully understand.
So I've used a dumbed down example to avoid obscuring the point of my question - I read [font="Courier New"]... param#1 = param#2 OUTPUT[/font] as set param #1 (which already has a value) to the undefined value of param #2. In spite of the general form [font="Courier New"]<LEFT> EQUAL SIGN <RIGHT> [/font] somehow magically the original value of param #1 is preserved and on top of that the original value is reproduced in param #2.
Any thoughts on how to read that T-SQL ?
June 8, 2016 at 11:49 am
j-1064772 (6/8/2016)
I have used a trivial example - should have considered that the normal reaction would be that dynamic T-SQL is totally unwarranted for something so silly. I agree.I already checked prior to posting it that it does work - I just don't understand that code and for a warranted application of dynamic T-SQL, I would end up writing code I know works but not fully understand.
So I've used a dumbed down example to avoid obscuring the point of my question - I read [font="Courier New"]... param#1 = param#2 OUTPUT[/font] as set param #1 (which already has a value) to the undefined value of param #2. In spite of the general form [font="Courier New"]<LEFT> EQUAL SIGN <RIGHT> [/font] somehow magically the original value of param #1 is preserved and on top of that the original value is reproduced in param #2.
Any thoughts on how to read that T-SQL ?
Actually, it works the same way that an OUTPUT parameter in a stored procedure works. Instead of the normal left = right, is right = left when execution completes.
June 8, 2016 at 11:55 am
OK, thanks.
[font="Comic Sans MS"](but it still looks weird...)[/font]
June 8, 2016 at 11:58 am
j-1064772 (6/8/2016)
OK, thanks.[font="Comic Sans MS"](but it still looks weird...)[/font]
Won't deny that, looks even weirder when you use the same variable name on each side. I have done it and it worked, but haven't tried lately with newer versions of SQL Server.
June 8, 2016 at 12:11 pm
Lynn Pettis (6/8/2016)
j-1064772 (6/8/2016)
OK, thanks.[font="Comic Sans MS"](but it still looks weird...)[/font]
Won't deny that, looks even weirder when you use the same variable name on each side. I have done it and it worked, but haven't tried lately with newer versions of SQL Server.
It still works with 2014. It is a very strange look though.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply