Creating Table in SQL2005 through .net program

Question

I need to create table on the fly in C#.net program. Task is to export excel file to SQL Database. How to write store procedure for creating table if column name and table name are passed as parameters? I tried to write following SP.

ALTER PROCEDURE dbo.CreateTempTable
@Param1 VarChar(50),
@Param2 VarChar(50),
@Param3 VarChar(50),
@Param4 VarChar(50)
AS
if exists(select * from sys.objects where object_id = OBJECT_ID('[dbo].[Temp]') AND type='U')
begin
Drop Table [dbo].[Temp]
end

Create Table [dbo].[Temp]([@CountryID] [int],
[@Param1] [VarChar](150),
[@Param2] [DateTime],
[@Param3] [VarChar](50),
[@Param4] [VarChar](50));

RETURN

While executing if I give input as

@Param1 - 'CountryId'
@Param2 - 'CountryName'
@Param3 - 'CreateDate'
@Param4 - 'CreatedBy'

The column created as @Param1, @Param2, @Param3, @Param4 instead of CountryId... etc.

Answer 1

You may use the sp_executesql system stored procedure with a custom string that you have to build in your program.

Answer 2

The body of your sproc should be

exec ('CREATE TABLE [dbo.Temp] (' + @param1 + ' int,' + @param2 + ' [DateTime], ' + @Param3 + ' VarChar, ' + @Param4 + ' VarChar)')

Although, as you're calling it from .net, you might as well just build up a string in .net and execute that as inline SQL...

eg:

string sql = string.Format("CREATE TABLE [dbo.Temp] ({0} int, {1} [DateTime], {2} VarChar, {3} VarChar)", fieldName1, fieldName2, fieldName3, fieldName4);

And then just execute that as SQL.

Answer 3

You could do something like :

execute 'Create Table [dbo].Temp, ' +[@Param2]+' [DateTime], '+[@Param3]+' VarChar, '+[@Param4]+' VarChar);';