A Stored Procedure to Add or Update a Data Table

An IF... ELSE SQL statement is dynamically built and executed
7/26/2009 4:09:00 PM

A stored procedure which accepts three parameters; @SerialNumber, @SaveddataLocation and @SavedData was created. Two local variables are declared @SQLstring1 and @S1. The SQL command is built in the character string @SQLstring1. It contains an IF... ELSE statement that checks to see if the @SavedDataLocation exists. If it exists then the @SavedData parameter for that location is updated, otherwise a new row is added with the @SavedDataLocation and @SavedData parameters.

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. Using the sp_executesql statement allows the SQL statement @S1 to be dynamically built since it will be run-time compiled.

code snippet 1 To use the SaveEEPROMData stored procedure a SaveEEPROMData method (VB.NET) is used. The serialNumber, dataLocation and data are passed into the method as strings. The connection to the SQL database is set up and the CommandType is set to StoredProcedure. The parameters @SerialNumber, @SaveDataLocation and @SavedData are added to the SqlCommand, the connection is opened and then executed using an ExecuteNonQuery command.

code snippet 1


Development Tools

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