ihcarp ihcarp - 2 months ago 11
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)
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

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.