[RESOLVED] need string to read out in INSERT statement

[theguyinthehat]

I use a series of if-then statements to decide which columns to export data to. These column names are saved in strings. I need the string to read out, so that the INSERT statement retrieves the column name from the string and saves in there. I've been reading on string functions but without luck. Anyone know how to do this?

INSERT INTO [FPAD Process Log] ([Lot Number], [Assigned Engineer], [Engineer Log], [Tool Type], Process, [Wafer/Slice], Quantity, [Job Entry Log], @str1, @str2, @str3, @str4, @str5]) VALUES (@LotNumber, @ChargeNumber, @AssignedEngineer, @Engineer_Log, @Tool_Type, @Process, @Wafer, @Quantity, @time,@txt1, @txt2, @txt3, @txt4, @txt5)

[Hack]

Since you are doing this in VB6

Dim sSQL As String
sSQL = "INSERT INTO [FPAD Process Log] ([Lot Number], [Assigned Engineer], [Engineer Log], [Tool Type], Process, [Wafer/Slice], Quantity, [Job Entry Log], @str1, @str2, @str3, @str4, @str5]) "
sSQL = sSQL & "VALUES (@LotNumber, @ChargeNumber, @AssignedEngineer, @Engineer_Log, @Tool_Type, @Process, @Wafer, @Quantity, @time,@txt1, @txt2, @txt3, @txt4, @txt5) "
DEBUG.PRINT sSQL

The Immediate Window will contain what would be getting passed back to the database.

[theguyinthehat]

I'm doing this in SQLServer 2005. It's running in a stored procedure, communicated to by a VB.NET 2005 application.

SQL won't compile because it can't find columns named @str1, @str2, et cetera. I want it to use the contents of the variable for the column name. Otherwise I have to write a bunch of INSERT statements in nested conditionals

[si_the_geek]

I'm doing this in SQLServer 2005. It's running in a stored procedure, communicated to by a VB.NET 2005 application.

In that case the 'VB6 and Earlier' forum is a very odd choice... moved to 'Database Development' forum.

[theguyinthehat]

Oh, crap.

[brucevde]

Resolved? How.

I want it to use the contents of the variable for the column name. Otherwise I have to write a bunch of INSERT statements in nested conditionals

But why? Based on your previous thread a dynamic statement doesn't seem necessary. You seem to have 15 fields that will get a value based on the Tool_Type. Rather than determining which field to use from the Type, use a variable for each field and set their Value based on the type.

Here is a simplified example. The sproc places the value of @Txt1 in one of two fields depending on the @Tool_Type parameter. The Insert statement never changes.

Create Procedure DoSomething
  @LotNumber int,
  @Tool_Type varchar(20),
  @Txt1 varchar(20)
As
Declare @Val1 varchar(20)
Declare @Val2 varchar(20)

If @Tool_Type = 'A'
   Set @Val1 = @Txt1

If @Tool_Type = 'B'
   Set @Val2 = @Txt1

Insert Into SomeTable ([Lot Number], [Tool Type], [Target Thickness], [Target Temperature])
Values (@LotNumber, @Tool_Type, @Val1, @Val2)

[theguyinthehat]

Sorry, I resolved it because it was over. For the sake of closing the case publicly, I'll say that you can't store a column name in a varchar because it won't compile--there's no way to tell it to read the contents of the variable, nor is there a way to guarantee that the varchar will store a column name (except by coding it that way), and the compiler doesn't accept that.