Mixing
SAP HANA: Is there a way to define schema and table name as input parameters of a procedure?
1
I am creating a procedure to delete table rows duplicates.
Code works fine, i want to transform code into a procedure. I can not find a way to set [Schema] and [Table] as input parameters.
Create procedure DUPL_DELETE (in inp1 nvarchar(100), in inp2 nvarchar(200))
as
begin
-- Select the distinct rows into a New temporary Table
create local temporary table #Mytemp
LIKE "SCHEMA"."TABLE";
-- LIKE :inp2.:inp1 Desired code to be(does not work)
SELECT DISTINCT *
FROM "SCHEMA"."TABLE"
INTO "SCHEMA".#Mytemp;
-- Delete the rows from table
DELETE FROM "SCHEMA"."TABLE";
-- Insert disctinct data back in to the Table and drop the temporary table
INSERT INTO "SCHEMA"."TABLE" SELECT * FROM "SCHEMA".#Mytemp;
DROP TABLE "SCHEMA".#Mytemp;
end;
Help guys please 🙏🏼
sql sap procedure hana
edited Jan 1 at 11:49
Umar Abdullah
1
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
add a comment |
1
I am creating a procedure to delete table rows duplicates.
Code works fine, i want to transform code into a procedure. I can not find a way to set [Schema] and [Table] as input parameters.
Create procedure DUPL_DELETE (in inp1 nvarchar(100), in inp2 nvarchar(200))
as
begin
-- Select the distinct rows into a New temporary Table
create local temporary table #Mytemp
LIKE "SCHEMA"."TABLE";
-- LIKE :inp2.:inp1 Desired code to be(does not work)
SELECT DISTINCT *
FROM "SCHEMA"."TABLE"
INTO "SCHEMA".#Mytemp;
-- Delete the rows from table
DELETE FROM "SCHEMA"."TABLE";
-- Insert disctinct data back in to the Table and drop the temporary table
INSERT INTO "SCHEMA"."TABLE" SELECT * FROM "SCHEMA".#Mytemp;
DROP TABLE "SCHEMA".#Mytemp;
end;
Help guys please 🙏🏼
sql sap procedure hana
edited Jan 1 at 11:49
Umar Abdullah
1
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
1
You would need to use dynamic SQL for this.
– Gordon Linoff
Dec 30 '18 at 16:01
Thank you, but i think it's not recommanded by SAP HANA, I have already done a test based on your suggestion, it works, but there's a message like not recommanded to use dynamic SQL for such queries
– SonOfHarpy
Jan 1 at 13:31
This message is a warning message - which means your code still compiles and executes. It's designed to draw attention to what is very likely not a great choice for development in SQL/SQLScript. Trying to be "flexible" about tables, views and schemas is trying to circumvent the fact that SQL is statically typed (and tables are the main data types). With this "flexibility" all sorts of problems can arise, both in execution performance as well in maintainability. In many cases it is a lot easier/better to handle the "flexibility" in the application layer or the schema generation process.
– Lars Br.
Jan 7 at 22:46
add a comment |
1
1
1
I am creating a procedure to delete table rows duplicates.
Code works fine, i want to transform code into a procedure. I can not find a way to set [Schema] and [Table] as input parameters.
Create procedure DUPL_DELETE (in inp1 nvarchar(100), in inp2 nvarchar(200))
as
begin
-- Select the distinct rows into a New temporary Table
create local temporary table #Mytemp
LIKE "SCHEMA"."TABLE";
-- LIKE :inp2.:inp1 Desired code to be(does not work)
SELECT DISTINCT *
FROM "SCHEMA"."TABLE"
INTO "SCHEMA".#Mytemp;
-- Delete the rows from table
DELETE FROM "SCHEMA"."TABLE";
-- Insert disctinct data back in to the Table and drop the temporary table
INSERT INTO "SCHEMA"."TABLE" SELECT * FROM "SCHEMA".#Mytemp;
DROP TABLE "SCHEMA".#Mytemp;
end;
Help guys please 🙏🏼
sql sap procedure hana
edited Jan 1 at 11:49
Umar Abdullah
1
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
I am creating a procedure to delete table rows duplicates.
Code works fine, i want to transform code into a procedure. I can not find a way to set [Schema] and [Table] as input parameters.
Create procedure DUPL_DELETE (in inp1 nvarchar(100), in inp2 nvarchar(200))
as
begin
-- Select the distinct rows into a New temporary Table
create local temporary table #Mytemp
LIKE "SCHEMA"."TABLE";
-- LIKE :inp2.:inp1 Desired code to be(does not work)
SELECT DISTINCT *
FROM "SCHEMA"."TABLE"
INTO "SCHEMA".#Mytemp;
-- Delete the rows from table
DELETE FROM "SCHEMA"."TABLE";
-- Insert disctinct data back in to the Table and drop the temporary table
INSERT INTO "SCHEMA"."TABLE" SELECT * FROM "SCHEMA".#Mytemp;
DROP TABLE "SCHEMA".#Mytemp;
end;
Help guys please 🙏🏼
sql sap procedure hana
sql sap procedure hana
edited Jan 1 at 11:49
Umar Abdullah
1
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
edited Jan 1 at 11:49
Umar Abdullah
1
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
edited Jan 1 at 11:49
Umar Abdullah
1
edited Jan 1 at 11:49
Umar Abdullah
1
edited Jan 1 at 11:49
Umar Abdullah
1
1
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
asked Dec 30 '18 at 15:59
SonOfHarpySonOfHarpy
105
105
1
You would need to use dynamic SQL for this.
– Gordon Linoff
Dec 30 '18 at 16:01
Thank you, but i think it's not recommanded by SAP HANA, I have already done a test based on your suggestion, it works, but there's a message like not recommanded to use dynamic SQL for such queries
– SonOfHarpy
Jan 1 at 13:31
This message is a warning message - which means your code still compiles and executes. It's designed to draw attention to what is very likely not a great choice for development in SQL/SQLScript. Trying to be "flexible" about tables, views and schemas is trying to circumvent the fact that SQL is statically typed (and tables are the main data types). With this "flexibility" all sorts of problems can arise, both in execution performance as well in maintainability. In many cases it is a lot easier/better to handle the "flexibility" in the application layer or the schema generation process.
– Lars Br.
Jan 7 at 22:46
add a comment |
1
You would need to use dynamic SQL for this.
– Gordon Linoff
Dec 30 '18 at 16:01
Thank you, but i think it's not recommanded by SAP HANA, I have already done a test based on your suggestion, it works, but there's a message like not recommanded to use dynamic SQL for such queries
– SonOfHarpy
Jan 1 at 13:31
This message is a warning message - which means your code still compiles and executes. It's designed to draw attention to what is very likely not a great choice for development in SQL/SQLScript. Trying to be "flexible" about tables, views and schemas is trying to circumvent the fact that SQL is statically typed (and tables are the main data types). With this "flexibility" all sorts of problems can arise, both in execution performance as well in maintainability. In many cases it is a lot easier/better to handle the "flexibility" in the application layer or the schema generation process.
– Lars Br.
Jan 7 at 22:46
1
1
You would need to use dynamic SQL for this.
– Gordon Linoff
Dec 30 '18 at 16:01
You would need to use dynamic SQL for this.
– Gordon Linoff
Dec 30 '18 at 16:01
Thank you, but i think it's not recommanded by SAP HANA, I have already done a test based on your suggestion, it works, but there's a message like not recommanded to use dynamic SQL for such queries
– SonOfHarpy
Jan 1 at 13:31
Thank you, but i think it's not recommanded by SAP HANA, I have already done a test based on your suggestion, it works, but there's a message like not recommanded to use dynamic SQL for such queries
– SonOfHarpy
Jan 1 at 13:31
This message is a warning message - which means your code still compiles and executes. It's designed to draw attention to what is very likely not a great choice for development in SQL/SQLScript. Trying to be "flexible" about tables, views and schemas is trying to circumvent the fact that SQL is statically typed (and tables are the main data types). With this "flexibility" all sorts of problems can arise, both in execution performance as well in maintainability. In many cases it is a lot easier/better to handle the "flexibility" in the application layer or the schema generation process.
– Lars Br.
Jan 7 at 22:46
This message is a warning message - which means your code still compiles and executes. It's designed to draw attention to what is very likely not a great choice for development in SQL/SQLScript. Trying to be "flexible" about tables, views and schemas is trying to circumvent the fact that SQL is statically typed (and tables are the main data types). With this "flexibility" all sorts of problems can arise, both in execution performance as well in maintainability. In many cases it is a lot easier/better to handle the "flexibility" in the application layer or the schema generation process.
– Lars Br.
Jan 7 at 22:46
add a comment |
1 Answer
1
active
oldest
votes
0
You can try changing the schema using following sample command:
set schema OTHERSCHEMA;
But for table name, you need to build and run dynamic SQL statement using
execute immediate :sql;
answered Jan 2 at 6:04
EralperEralper
5,25011221
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
});
}
});
draft saved
draft discarded
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%2f53979128%2fsap-hana-is-there-a-way-to-define-schema-and-table-name-as-input-parameters-of%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
0
You can try changing the schema using following sample command:
set schema OTHERSCHEMA;
But for table name, you need to build and run dynamic SQL statement using
execute immediate :sql;
answered Jan 2 at 6:04
EralperEralper
5,25011221
add a comment |
0
You can try changing the schema using following sample command:
set schema OTHERSCHEMA;
But for table name, you need to build and run dynamic SQL statement using
execute immediate :sql;
answered Jan 2 at 6:04
EralperEralper
5,25011221
add a comment |
0
0
0
You can try changing the schema using following sample command:
set schema OTHERSCHEMA;
But for table name, you need to build and run dynamic SQL statement using
execute immediate :sql;
answered Jan 2 at 6:04
EralperEralper
5,25011221
You can try changing the schema using following sample command:
set schema OTHERSCHEMA;
But for table name, you need to build and run dynamic SQL statement using
execute immediate :sql;
answered Jan 2 at 6:04
EralperEralper
5,25011221
answered Jan 2 at 6:04
EralperEralper
5,25011221
answered Jan 2 at 6:04
EralperEralper
5,25011221
answered Jan 2 at 6:04
EralperEralper
5,25011221
5,25011221
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53979128%2fsap-hana-is-there-a-way-to-define-schema-and-table-name-as-input-parameters-of%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
1
You would need to use dynamic SQL for this.
– Gordon Linoff
Dec 30 '18 at 16:01
Thank you, but i think it's not recommanded by SAP HANA, I have already done a test based on your suggestion, it works, but there's a message like not recommanded to use dynamic SQL for such queries
– SonOfHarpy
Jan 1 at 13:31
This message is a warning message - which means your code still compiles and executes. It's designed to draw attention to what is very likely not a great choice for development in SQL/SQLScript. Trying to be "flexible" about tables, views and schemas is trying to circumvent the fact that SQL is statically typed (and tables are the main data types). With this "flexibility" all sorts of problems can arise, both in execution performance as well in maintainability. In many cases it is a lot easier/better to handle the "flexibility" in the application layer or the schema generation process.
– Lars Br.
Jan 7 at 22:46