Create a SQL Server Data Table During Program Execution

The SQL server sp_executesql stored procedure is used to create the table dynamically
7/26/2009 8:33:00 AM

A stored procedure was created which accepts two parameters @SerialNumber and @Description. Two local variables are declared @SQLstring1 and @S1. The SQL statement is created using character data and stored in @SQLstring1. This is where the parameter @SerialNumber is added to the SQL statement.

code snippet 1 The SQL statement verifies that a table with the name passed in through the @SerialNumber parameter does not already exist. If one does not exist it will create a table with the name passed into the procedure through @SerialNumber parameter. It will create three columns in the table; SaveDataID which is set as the primary key, SavedDataLocation and SavedData. The statement is then cast to a Unicode character string and stored in @S1.

The SQL statement @S1 is executed using the sp_executesql statement. The SQL statement stored in @S1 is not compiled until the sp_executesql statement is executed. This is what enables the table name to be set during the main application's program execution using this stored procedure.

After the table has been created it's name (@SerialNumber) and description (@Descriprion) are added to the SavedEEPROMdataList table. If the name is already in the table then the description is updated with the @Description parameter.

To use the AddNewDataTable stored procedure a CreateNewDataTable method (VB.NET) is used. The serialNumber and description are passed into the method as strings. The connection to the SQL database is set up and the CommandType is set to StoredProcedure. The two parameters @SerialNumber and @Description are added to the SqlCommand, the connection is opened and then executed using an ExecuteNonQuery command.

code snippet 2


Development Tools

  • Visual Studio 2005
  •   .NET Framework 2.0
  •   Visual C# 2005
  •   Visual Basic 2005
  •   SQL Server 2005
  •   C32 C Compiler
  •   MPASM Assembler