Create a table user defined function in my DB from C#

Question

I have to create a User Defined Function that returns a table as result.

I need to create this function from my c# application, but when I try to run the code against my DB SQL server gives me an error, but, when I run the same code from Management Studio it gives me no error and works fine.

This is what I have in C#

private const string FN_AXDBINFO =@"                            
            CREATE FUNCTION FN_AXDBINFO ( )
            RETURNS @SYS_DBINFO TABLE
                (
                  SYSTEMTYPE VARCHAR(50) ,
                  COUNTRYCODE VARCHAR(10) ,
                  HLNUMBER VARCHAR(15) ,
                  VERSION VARCHAR(20) ,
                  DATO_SISTEMA VARCHAR(20)
                )
            AS
                BEGIN
                    INSERT  INTO @SYS_DBINFO
                            ( SYSTEMTYPE ,
                              COUNTRYCODE ,
                              HLNUMBER ,
                              VERSION ,
                              DATO_SISTEMA
                            )
                            SELECT TOP 1
                                    SYSTEMTYPE ,
                                    COUNTRYCODE ,
                                    HLNUMBER ,
                                    VERSION ,
                                    DATO_SISTEMA
                            FROM    AXDBINFO
                            ORDER BY LAST_UPDATE DESC
                    RETURN
                END
            ";

cmd = GetNewCommand(cc); //This function gets a new SQL COMMNAD 
                            //object with an open sql server connection


//Add the command text from de const    
cmd.CommandText =FN_AXDBINFO;
//Execute
cmd.ExecuteNonQuery(); 



/// <summary>
/// Obtiene un nuevo SqlCommand
/// </summary>
/// <returns></returns>
protected SqlCommand GetNewCommand(CustomConnection cc)
{
    SqlCommand cmd;
    SqlConnection conn = new SqlConnection(Utils.Utils.GetConnectionString(cc));

    cmd = conn.CreateCommand();
    if (conn.State != ConnectionState.Open)
        conn.Open();
    cmd.CommandType = CommandType.Text;

    return cmd;
}

And this is the error that i get when run the aplication:

Incorrect syntax near the keyword 'FUNCTION'. Must declare the table variable "@SYS_DBINFO".

The thing is that the same TSQL code from Management studio works just fine. I try the folowing:

Add a parameter called @SYS_DBINFO, the error changes to

incorrect syntax near the keyword 'FUNCTION'

Put @SYS_DBINFO beteween brackets, get the same error.
Try to create a UDF using SMO, but it is not compatible with previous version of SQL SERVER

Can anyone help me with this please?

Thanks.

To avoid confusion, I have edited the code, the error remains the same. I can not create a table-type UDF since SQL SERVER assumes that the table that results from the function whose name is @SYS_DBINFO is a scalar variable, and again, the same code from MANAGEMENT STUDIO works fine.

Why are you creating a function in C#??? A function should be created and then consumed by C#. Also, GO is not a t-sql statement. It is the default batch separator in SSMS. As such it will NOT work like this. And how would expect your C# to behave the second time it runs? — Sean Lange
– Sean Lange, Commented Jun 1, 2017 at 21:21
I need to create this function to initialize my database, The GO statemet is removed by my SQL QUERY parser, in the code that I pasted, I didn't put this.... — Rodrigo Fulanito
– Rodrigo Fulanito, Commented Jun 1, 2017 at 21:30
Yes yes, its a mess, I'm sorry, but to clarify a little...The code that I posted it's not complete, it's just a little snippet of all mjy code. — Rodrigo Fulanito
– Rodrigo Fulanito, Commented Jun 2, 2017 at 1:25

Dan Guzman · Accepted Answer · 2017-06-02 01:47:42Z

1

You need to execute the drop and create scripts separately. GO is not a T-SQL statement but a batch separator used by tools like SSMS. Also, you cannot parameterize CREATE FUNCTION DDL, nor is there a need to.

answered Jun 2, 2017 at 1:47

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rodrigo Fulanito Over a year ago

Thanks Dan Guzman, but, I'm already separate the batches and the errors is the same, and i'm not parametrizing the query that creates the function. I will edit the sinipet because it's confusing you and everyone else.

Dan Guzman Over a year ago

@RodrigoFulanito, post the code for 'GetNewCommand'. The error suggests a parameter was added to the command object by that method.

Rodrigo Fulanito Over a year ago

Dan Guzman the code for GetNewCommand function is in my post. I don't see anything weird

SMor Over a year ago

@RodrigoFulanito You need to debug your own code. No one can offer useful suggestions without the proper context - this is called "guessing". It would appear that your "batch" logic is not correct. And there is no good reason to use the multi-statement form of a TVF for the code you posted - the inline version would likely be better. And since your function has no arguments, the utility of such a TVF is doubtful. A view would likely be an improvement.

Rodrigo Fulanito Over a year ago

SMor if you try the above code, you will get the error, that code as is it's the same I'm using.

Rodrigo Fulanito · Accepted Answer · 2017-06-02 16:36:47Z

1

The problem was that I was using the same SQL COMMAND object to process all my query batches, the first batch needs parameters, the 2nd and 3rd not, but the parameters still there...so...clearing the parameters after first batch solves the problem.

answered Jun 2, 2017 at 16:36

Rodrigo Fulanito

838 bronze badges

Collectives™ on Stack Overflow

Create a table user defined function in my DB from C#

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related