CODEWITHSUNDEEP
sqlsql-serverdynamic
Krystian Borysewicz
Krystian Borysewicz

Reputation: 779

Fully dynamic Stored SQL Procedures?

Is it possible to have fully dynamic SQL Stored procedures? I've read up on dynamic SQL for the past few hours but couldn't find the "fully dynamic" solution that I am looking for.

I want to make a procedure that allows for imports of all files of that type, just by simply passing the file location, table name, the rows data and value types. It shouldn't care how many columns there has to be, what datatypes are used etc.

Reason for this is that we are importing a lot of different types of .csv files, and having to make a new stored procedure and table for it sounds way more time consuming than just having to make the table and use a common procedure with some passed values.

Not a master of SQL, so I sketched out what I need in C# instead:

Table createAndFillTable(string tableName, string[] columnNames, string[] columnTypes, string[] rows)
{
    var t = CreateNewTable(tableName);
    for(int i = 0; i < columnNames; i++)
    {
        t.addColumn(columnNames[i], columnTypes[i]);
    }

    foreach(var r in rows)
    {
        //Needs parsing to correct data types
        t.AddRow(r);
    }

    return t;
}

The example is with creating new tables, but the optimal solution would be to just import to an existing table by passing 2 parameters, tableName and fileLocation and let the sql handle the variable types etc. No idea if that's a better solution though.

Thanks up front!

Upvotes: 4

Views: 807

Answers (1)

user3112728
user3112728

Reputation: 405

Take a look at bulk insert command line tool, it's designed for this scenario. Also, consider dynamic sql instead of dynamic stored procedure. You can use your C# to construct dynamic SQL statements that insert batches into the database. A long time a go, stored procedures were compiled more efficiently, but for a long time that same type of compilation is applied to regular SQL Statements.

The bulk insert command line tool already takes care of the batching stuff which can be helpful.

Upvotes: 1

Related Questions