ihcarp ihcarp - 1 year ago 51
C# Question

Creating Table in SQL2005 through .net program

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)
if exists(select * from sys.objects where object_id = OBJECT_ID('[dbo].[Temp]') AND type='U')
Drop Table [dbo].[Temp]

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


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 Source

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...


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.