Use If Else in SQL User Define function

Question

0.00/5 (No votes)

See more:

Hi All,
I am creating a SQL User Define function where I pass a input. Now condition is that if the input value is 1 then Create Table1 else input value is 2 then create Table2.

Can any one help me to create this.

Thanks to all

Posted 6-Jan-16 22:19pm

IndrajitDasgupat

Add a Solution

Comments

Dishi_Gupta 7-Jan-16 4:28am

What is the so far code you have used to make the function?

Herman<T>.Instance 7-Jan-16 4:34am

SELECT CASE <Input> WHEN 1 THEN (CREATE TABLE ....) WHEN 2 THEN (CREATE TABLE ...) END

Tomas Takac 7-Jan-16 4:39am

This won't work. You definitely cannot create table inside CASE-WHEN. If we are talking about SQL Server then there is IF-THEN-ELSE available.

Tomas Takac 7-Jan-16 4:37am

You cannot create tables in a UDF. Perhaps you want a Stored procedure instead?

Dishi_Gupta 7-Jan-16 4:56am

If You really want an option to create tables under conditions, instead of making two different tables create one table only, but specifying different column names for each case, assuming that the values are later stored in separate tables..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-01-06T22:42:00

You can't do it.
Look at the documentation: https://msdn.microsoft.com/en-us/library/aa175085%28v=sql.80%29.aspx?f=255&MSPPError=-2147217396[^] and you will see that:

The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

You can do these in a stored procedure, (but you can't call them from a UDF either) but you cannot CREATE, ALTER, or DROP tables within a function.