How to write mysql stored function that returns a whole table?

up vote
-1
down vote

favorite

screenshot is there any way to write this in stored function which returns table with values.

SELECT ClassNo,Type,Days,

CASE WHEN ClassAt09 IN ('ICT16') THEN ClassAt09

ELSE ''

END as 'ClassAt09',

CASE WHEN ClassAt10 IN  ('ICT16') THEN ClassAt10

ELSE ''

END as 'ClassAt10',

CASE WHEN ClassAt11 IN  ('ICT16') THEN ClassAt11

ELSE ''

END as 'ClassAt11',

CASE WHEN ClassAt12 IN  ('ICT16') THEN ClassAt12

ELSE ''

END as 'ClassAt12',

CASE WHEN ClassAt13 IN  ('ICT16') THEN ClassAt13

ELSE ''

END as 'ClassAt13',

CASE WHEN ClassAt14 IN  ('ICT16') THEN ClassAt14

ELSE ''

END as 'ClassAt14',

CASE WHEN ClassAt15 IN  ('ICT16') THEN ClassAt15

ELSE ''

END as 'ClassAt15',

CASE WHEN ClassAt16 IN  ('ICT16') THEN ClassAt16

ELSE ''

END as 'ClassAt16',

CASE WHEN ClassAt17 IN  ('ICT16') THEN ClassAt17

ELSE ''

END as 'ClassAt17'

from classTable

where ClassAt09="ICT16" or ClassAt10="ICT16" or ClassAt11="ICT16" or ClassAt12="ICT16" or ClassAt13="ICT16" or ClassAt14="ICT16" or ClassAt15="ICT16" or ClassAt16="ICT16" or ClassAt17="ICT16";

Please see the attached screenshot, that table will be returned from the above query.

I want to convert it to stored function so that I don't have to write ICT16 again and again. I can change the value at one place and it changes everywhere in the query.

Please help.

Thanks

edited Nov 22 at 16:05

asked Nov 22 at 14:56

nish17

2

People here generally don't like screenshots, post data and code as formatted text.
– HoneyBadger
Nov 22 at 15:01

2

What about a view ?
– dani herrera
Nov 22 at 15:30

1

Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. Please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 22 at 17:57

1

Please clarify via post edits, not comments. Are you supposed to use MySQL? Were you told tou use a "function" specifically? What exactly were you told to do? What did you learn from reading the manual & googling site:Stack Overflow? Right now this is a faq.
– philipxy
Nov 22 at 18:19

1

Possible duplicate of How to return table from MySQL function
– philipxy
Nov 22 at 18:19

|
show 4 more comments

up vote
-1
down vote

favorite

screenshot is there any way to write this in stored function which returns table with values.

SELECT ClassNo,Type,Days,

CASE WHEN ClassAt09 IN ('ICT16') THEN ClassAt09

ELSE ''

END as 'ClassAt09',

CASE WHEN ClassAt10 IN  ('ICT16') THEN ClassAt10

ELSE ''

END as 'ClassAt10',

CASE WHEN ClassAt11 IN  ('ICT16') THEN ClassAt11

ELSE ''

END as 'ClassAt11',

CASE WHEN ClassAt12 IN  ('ICT16') THEN ClassAt12

ELSE ''

END as 'ClassAt12',

CASE WHEN ClassAt13 IN  ('ICT16') THEN ClassAt13

ELSE ''

END as 'ClassAt13',

CASE WHEN ClassAt14 IN  ('ICT16') THEN ClassAt14

ELSE ''

END as 'ClassAt14',

CASE WHEN ClassAt15 IN  ('ICT16') THEN ClassAt15

ELSE ''

END as 'ClassAt15',

CASE WHEN ClassAt16 IN  ('ICT16') THEN ClassAt16

ELSE ''

END as 'ClassAt16',

CASE WHEN ClassAt17 IN  ('ICT16') THEN ClassAt17

ELSE ''

END as 'ClassAt17'

from classTable

where ClassAt09="ICT16" or ClassAt10="ICT16" or ClassAt11="ICT16" or ClassAt12="ICT16" or ClassAt13="ICT16" or ClassAt14="ICT16" or ClassAt15="ICT16" or ClassAt16="ICT16" or ClassAt17="ICT16";

Please see the attached screenshot, that table will be returned from the above query.

I want to convert it to stored function so that I don't have to write ICT16 again and again. I can change the value at one place and it changes everywhere in the query.

Please help.

Thanks

edited Nov 22 at 16:05

asked Nov 22 at 14:56

nish17

2

People here generally don't like screenshots, post data and code as formatted text.
– HoneyBadger
Nov 22 at 15:01

2

What about a view ?
– dani herrera
Nov 22 at 15:30

1

Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. Please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 22 at 17:57

1

Please clarify via post edits, not comments. Are you supposed to use MySQL? Were you told tou use a "function" specifically? What exactly were you told to do? What did you learn from reading the manual & googling site:Stack Overflow? Right now this is a faq.
– philipxy
Nov 22 at 18:19

1

Possible duplicate of How to return table from MySQL function
– philipxy
Nov 22 at 18:19

|
show 4 more comments

up vote
-1
down vote

favorite

screenshot is there any way to write this in stored function which returns table with values.

SELECT ClassNo,Type,Days,

CASE WHEN ClassAt09 IN ('ICT16') THEN ClassAt09

ELSE ''

END as 'ClassAt09',

CASE WHEN ClassAt10 IN  ('ICT16') THEN ClassAt10

ELSE ''

END as 'ClassAt10',

CASE WHEN ClassAt11 IN  ('ICT16') THEN ClassAt11

ELSE ''

END as 'ClassAt11',

CASE WHEN ClassAt12 IN  ('ICT16') THEN ClassAt12

ELSE ''

END as 'ClassAt12',

CASE WHEN ClassAt13 IN  ('ICT16') THEN ClassAt13

ELSE ''

END as 'ClassAt13',

CASE WHEN ClassAt14 IN  ('ICT16') THEN ClassAt14

ELSE ''

END as 'ClassAt14',

CASE WHEN ClassAt15 IN  ('ICT16') THEN ClassAt15

ELSE ''

END as 'ClassAt15',

CASE WHEN ClassAt16 IN  ('ICT16') THEN ClassAt16

ELSE ''

END as 'ClassAt16',

CASE WHEN ClassAt17 IN  ('ICT16') THEN ClassAt17

ELSE ''

END as 'ClassAt17'

from classTable

where ClassAt09="ICT16" or ClassAt10="ICT16" or ClassAt11="ICT16" or ClassAt12="ICT16" or ClassAt13="ICT16" or ClassAt14="ICT16" or ClassAt15="ICT16" or ClassAt16="ICT16" or ClassAt17="ICT16";

Please see the attached screenshot, that table will be returned from the above query.

I want to convert it to stored function so that I don't have to write ICT16 again and again. I can change the value at one place and it changes everywhere in the query.

Please help.

Thanks

edited Nov 22 at 16:05

asked Nov 22 at 14:56

nish17

screenshot is there any way to write this in stored function which returns table with values.

SELECT ClassNo,Type,Days,

CASE WHEN ClassAt09 IN ('ICT16') THEN ClassAt09

ELSE ''

END as 'ClassAt09',

CASE WHEN ClassAt10 IN  ('ICT16') THEN ClassAt10

ELSE ''

END as 'ClassAt10',

CASE WHEN ClassAt11 IN  ('ICT16') THEN ClassAt11

ELSE ''

END as 'ClassAt11',

CASE WHEN ClassAt12 IN  ('ICT16') THEN ClassAt12

ELSE ''

END as 'ClassAt12',

CASE WHEN ClassAt13 IN  ('ICT16') THEN ClassAt13

ELSE ''

END as 'ClassAt13',

CASE WHEN ClassAt14 IN  ('ICT16') THEN ClassAt14

ELSE ''

END as 'ClassAt14',

CASE WHEN ClassAt15 IN  ('ICT16') THEN ClassAt15

ELSE ''

END as 'ClassAt15',

CASE WHEN ClassAt16 IN  ('ICT16') THEN ClassAt16

ELSE ''

END as 'ClassAt16',

CASE WHEN ClassAt17 IN  ('ICT16') THEN ClassAt17

ELSE ''

END as 'ClassAt17'

from classTable

where ClassAt09="ICT16" or ClassAt10="ICT16" or ClassAt11="ICT16" or ClassAt12="ICT16" or ClassAt13="ICT16" or ClassAt14="ICT16" or ClassAt15="ICT16" or ClassAt16="ICT16" or ClassAt17="ICT16";

Please see the attached screenshot, that table will be returned from the above query.

I want to convert it to stored function so that I don't have to write ICT16 again and again. I can change the value at one place and it changes everywhere in the query.

Please help.

Thanks

mysql sql mysql-workbench rdbms

edited Nov 22 at 16:05

asked Nov 22 at 14:56

nish17

edited Nov 22 at 16:05

asked Nov 22 at 14:56

nish17

edited Nov 22 at 16:05

asked Nov 22 at 14:56

nish17

asked Nov 22 at 14:56

nish17

asked Nov 22 at 14:56

nish17

2

People here generally don't like screenshots, post data and code as formatted text.
– HoneyBadger
Nov 22 at 15:01

2

What about a view ?
– dani herrera
Nov 22 at 15:30

1

Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. Please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 22 at 17:57

1

Please clarify via post edits, not comments. Are you supposed to use MySQL? Were you told tou use a "function" specifically? What exactly were you told to do? What did you learn from reading the manual & googling site:Stack Overflow? Right now this is a faq.
– philipxy
Nov 22 at 18:19

1

Possible duplicate of How to return table from MySQL function
– philipxy
Nov 22 at 18:19

|
show 4 more comments

2

People here generally don't like screenshots, post data and code as formatted text.
– HoneyBadger
Nov 22 at 15:01

2

What about a view ?
– dani herrera
Nov 22 at 15:30

1

Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. Please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 22 at 17:57

1

Please clarify via post edits, not comments. Are you supposed to use MySQL? Were you told tou use a "function" specifically? What exactly were you told to do? What did you learn from reading the manual & googling site:Stack Overflow? Right now this is a faq.
– philipxy
Nov 22 at 18:19

1

Possible duplicate of How to return table from MySQL function
– philipxy
Nov 22 at 18:19

People here generally don't like screenshots, post data and code as formatted text.
– HoneyBadger
Nov 22 at 15:01

What about a view ?
– dani herrera
Nov 22 at 15:30

Hi. Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. Please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 22 at 17:57

Please clarify via post edits, not comments. Are you supposed to use MySQL? Were you told tou use a "function" specifically? What exactly were you told to do? What did you learn from reading the manual & googling site:Stack Overflow? Right now this is a faq.
– philipxy
Nov 22 at 18:19

Possible duplicate of How to return table from MySQL function
– philipxy
Nov 22 at 18:19

|
show 4 more comments

active

oldest

votes

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',
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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433589%2fhow-to-write-mysql-stored-function-that-returns-a-whole-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

active

oldest

votes

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.

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Qfyilyi