Pass declare table variable to another stored procedure
I am sure the answer is NO, but I'll ask the expert anyway ;)
I've declared a table variable in my stored procedure.
DECLARE @OrderMapIds TABLE
(
OrderId INT NOT NULL,
NewOrderId INT NOT NULL
);
INSERT INTO @OrderMapIds (OrderId, NewOrderId)
SELECT [OrderId], [OrderId] FROM [tblOrder]
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds; --I need to move order ids based on the mapped id
I need to pass @OrderMapIds to [AS.uspOrder_MoveOrder]. The question is how?
CREATE PROCEDURE [AS.uspOrderItem_CopyRecord]
(
@OrderMapIds AS TABLE -- This thrown error
)
AS
BEGIN
...
...
...
END;
Now, I can accomplish this problem using Table-Valued Parameter (TVP). But if I could pass it without TVP, then it will be better (so I don't have to create TVP for small stuff).
Now, after looking at Google, I am sure the answer is NO (ie. I need to create TVP to accomplish task above). But I thought to ask the question in hope I might have missed something.
Any help is greatly appreciated.
Thanks
sql
sql-server stored-procedures asked Nov 23 at 7:48
Sam
493524
add a comment |
I am sure the answer is NO, but I'll ask the expert anyway ;)
I've declared a table variable in my stored procedure.
DECLARE @OrderMapIds TABLE
(
OrderId INT NOT NULL,
NewOrderId INT NOT NULL
);
INSERT INTO @OrderMapIds (OrderId, NewOrderId)
SELECT [OrderId], [OrderId] FROM [tblOrder]
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds; --I need to move order ids based on the mapped id
I need to pass @OrderMapIds to [AS.uspOrder_MoveOrder]. The question is how?
CREATE PROCEDURE [AS.uspOrderItem_CopyRecord]
(
@OrderMapIds AS TABLE -- This thrown error
)
AS
BEGIN
...
...
...
END;
Now, I can accomplish this problem using Table-Valued Parameter (TVP). But if I could pass it without TVP, then it will be better (so I don't have to create TVP for small stuff).
Now, after looking at Google, I am sure the answer is NO (ie. I need to create TVP to accomplish task above). But I thought to ask the question in hope I might have missed something.
Any help is greatly appreciated.
Thanks
sql
sql-server stored-procedures asked Nov 23 at 7:48
Sam
493524
add a comment |
I am sure the answer is NO, but I'll ask the expert anyway ;)
I've declared a table variable in my stored procedure.
DECLARE @OrderMapIds TABLE
(
OrderId INT NOT NULL,
NewOrderId INT NOT NULL
);
INSERT INTO @OrderMapIds (OrderId, NewOrderId)
SELECT [OrderId], [OrderId] FROM [tblOrder]
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds; --I need to move order ids based on the mapped id
I need to pass @OrderMapIds to [AS.uspOrder_MoveOrder]. The question is how?
CREATE PROCEDURE [AS.uspOrderItem_CopyRecord]
(
@OrderMapIds AS TABLE -- This thrown error
)
AS
BEGIN
...
...
...
END;
Now, I can accomplish this problem using Table-Valued Parameter (TVP). But if I could pass it without TVP, then it will be better (so I don't have to create TVP for small stuff).
Now, after looking at Google, I am sure the answer is NO (ie. I need to create TVP to accomplish task above). But I thought to ask the question in hope I might have missed something.
Any help is greatly appreciated.
Thanks
sql
sql-server stored-procedures asked Nov 23 at 7:48
Sam
493524
I am sure the answer is NO, but I'll ask the expert anyway ;)
I've declared a table variable in my stored procedure.
DECLARE @OrderMapIds TABLE
(
OrderId INT NOT NULL,
NewOrderId INT NOT NULL
);
INSERT INTO @OrderMapIds (OrderId, NewOrderId)
SELECT [OrderId], [OrderId] FROM [tblOrder]
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds; --I need to move order ids based on the mapped id
I need to pass @OrderMapIds to [AS.uspOrder_MoveOrder]. The question is how?
CREATE PROCEDURE [AS.uspOrderItem_CopyRecord]
(
@OrderMapIds AS TABLE -- This thrown error
)
AS
BEGIN
...
...
...
END;
Now, I can accomplish this problem using Table-Valued Parameter (TVP). But if I could pass it without TVP, then it will be better (so I don't have to create TVP for small stuff).
Now, after looking at Google, I am sure the answer is NO (ie. I need to create TVP to accomplish task above). But I thought to ask the question in hope I might have missed something.
Any help is greatly appreciated.
Thanks
sql
sql-server stored-procedures sql
sql-server stored-procedures asked Nov 23 at 7:48
Sam
493524
asked Nov 23 at 7:48
Sam
493524
asked Nov 23 at 7:48
Sam
493524
asked Nov 23 at 7:48
Sam
493524
asked Nov 23 at 7:48
Sam
493524
493524
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You are correct. The answer is indeed No.
In SQL Server, the only way to pass a table to a stored procedure is using a user defined table type.
You do have some confusion in the terms, though.
TVP is the parameter itself - so even if you could just pass any table variable a stored procedure - it would still be a Table Valued Parameter.
What you want to avoid (but can't) is a User Defined Table Type.
If this was allowed, you would end up with a stored procedure that takes in a table valued parameter with an unknown structure - And this could lead to errors, extremely cumbersome code, and worst of all - silently using the wrong data.
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
add a comment |
An interesting alternative, especially when the table structure is variable/dependent will be to convert data as JSON/XML ( data type for parameter will be NVARCHAR).
Way to go in your example will be
DECLARE @OrderMapIds NVARCHAR(MAX)=
(
SELECT [OrderId], [OrderId] FROM [tblOrder] FOR JSON PATH
);
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds;
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
very interesting. +1 for sharing... But how would you decode@OrderMapIdsinside procedure[AS.uspOrder_MoveOrder]?.
– Sam
Nov 25 at 2:40
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53442564%2fpass-declare-table-variable-to-another-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You are correct. The answer is indeed No.
In SQL Server, the only way to pass a table to a stored procedure is using a user defined table type.
You do have some confusion in the terms, though.
TVP is the parameter itself - so even if you could just pass any table variable a stored procedure - it would still be a Table Valued Parameter.
What you want to avoid (but can't) is a User Defined Table Type.
If this was allowed, you would end up with a stored procedure that takes in a table valued parameter with an unknown structure - And this could lead to errors, extremely cumbersome code, and worst of all - silently using the wrong data.
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
add a comment |
You are correct. The answer is indeed No.
In SQL Server, the only way to pass a table to a stored procedure is using a user defined table type.
You do have some confusion in the terms, though.
TVP is the parameter itself - so even if you could just pass any table variable a stored procedure - it would still be a Table Valued Parameter.
What you want to avoid (but can't) is a User Defined Table Type.
If this was allowed, you would end up with a stored procedure that takes in a table valued parameter with an unknown structure - And this could lead to errors, extremely cumbersome code, and worst of all - silently using the wrong data.
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
add a comment |
You are correct. The answer is indeed No.
In SQL Server, the only way to pass a table to a stored procedure is using a user defined table type.
You do have some confusion in the terms, though.
TVP is the parameter itself - so even if you could just pass any table variable a stored procedure - it would still be a Table Valued Parameter.
What you want to avoid (but can't) is a User Defined Table Type.
If this was allowed, you would end up with a stored procedure that takes in a table valued parameter with an unknown structure - And this could lead to errors, extremely cumbersome code, and worst of all - silently using the wrong data.
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
You are correct. The answer is indeed No.
In SQL Server, the only way to pass a table to a stored procedure is using a user defined table type.
You do have some confusion in the terms, though.
TVP is the parameter itself - so even if you could just pass any table variable a stored procedure - it would still be a Table Valued Parameter.
What you want to avoid (but can't) is a User Defined Table Type.
If this was allowed, you would end up with a stored procedure that takes in a table valued parameter with an unknown structure - And this could lead to errors, extremely cumbersome code, and worst of all - silently using the wrong data.
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
answered Nov 23 at 8:09
Zohar Peled
52.4k73273
52.4k73273
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
add a comment |
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
Glad to help :-)
– Zohar Peled
Nov 25 at 4:57
add a comment |
An interesting alternative, especially when the table structure is variable/dependent will be to convert data as JSON/XML ( data type for parameter will be NVARCHAR).
Way to go in your example will be
DECLARE @OrderMapIds NVARCHAR(MAX)=
(
SELECT [OrderId], [OrderId] FROM [tblOrder] FOR JSON PATH
);
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds;
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
very interesting. +1 for sharing... But how would you decode@OrderMapIdsinside procedure[AS.uspOrder_MoveOrder]?.
– Sam
Nov 25 at 2:40
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
add a comment |
An interesting alternative, especially when the table structure is variable/dependent will be to convert data as JSON/XML ( data type for parameter will be NVARCHAR).
Way to go in your example will be
DECLARE @OrderMapIds NVARCHAR(MAX)=
(
SELECT [OrderId], [OrderId] FROM [tblOrder] FOR JSON PATH
);
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds;
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
very interesting. +1 for sharing... But how would you decode@OrderMapIdsinside procedure[AS.uspOrder_MoveOrder]?.
– Sam
Nov 25 at 2:40
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
add a comment |
An interesting alternative, especially when the table structure is variable/dependent will be to convert data as JSON/XML ( data type for parameter will be NVARCHAR).
Way to go in your example will be
DECLARE @OrderMapIds NVARCHAR(MAX)=
(
SELECT [OrderId], [OrderId] FROM [tblOrder] FOR JSON PATH
);
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds;
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
An interesting alternative, especially when the table structure is variable/dependent will be to convert data as JSON/XML ( data type for parameter will be NVARCHAR).
Way to go in your example will be
DECLARE @OrderMapIds NVARCHAR(MAX)=
(
SELECT [OrderId], [OrderId] FROM [tblOrder] FOR JSON PATH
);
...
...
...
EXEC [AS.uspOrder_MoveOrder] @OrderMapIds = @OrderMapIds;
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
answered Nov 23 at 8:39
DhruvJoshi
12.1k62644
12.1k62644
very interesting. +1 for sharing... But how would you decode@OrderMapIdsinside procedure[AS.uspOrder_MoveOrder]?.
– Sam
Nov 25 at 2:40
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
add a comment |
very interesting. +1 for sharing... But how would you decode@OrderMapIdsinside procedure[AS.uspOrder_MoveOrder]?.
– Sam
Nov 25 at 2:40
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
very interesting. +1 for sharing... But how would you decode
@OrderMapIds inside procedure [AS.uspOrder_MoveOrder]?.– Sam
Nov 25 at 2:40
very interesting. +1 for sharing... But how would you decode
@OrderMapIds inside procedure [AS.uspOrder_MoveOrder]?.– Sam
Nov 25 at 2:40
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
SQL Server has built in methods to deal with xml, and since 2016 version also to deal with json. This is a good alternative to deal with an unknown data structure.
– Zohar Peled
Nov 25 at 5:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53442564%2fpass-declare-table-variable-to-another-stored-procedure%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
