T-SQL INSERT using CASE WHEN function as a value

Thanks Jim!  As I'm looking at my code to add this, it looks like what I should do is put the CASE/WHEN in my Parameters.Add list.  Then the INSERT statement only has another Parameter to INSERT and doesn't even have to "worry" about CASE/WHEN syntax as long as I get the syntax right in the Parameters.Add.  Does that make more sense to do?

In what language are you referring to with 'Parameters.Add'?  That zone should be added to this question, as I'm mostly a SQL Server developer and don't answer front-end questions.

Also, not sure what you mean there, so I recommend providing us some sample code using a code block.

Oh, sorry, Jim.  That has taken this question beyond the initial area.  The front end is in VB.Net.

An example of two Parameter.Add statements, one simple and one inside an IF/THEN may be something like this:

            DBCmd.Parameters.Add("@ReviewType", SqlDbType.VarChar).Value = ddlReviewType.Text
            If txtReviewDate.Text.Length <> 0 Then
                DBCmd.Parameters.Add("@ReviewDate", SqlDbType.VarChar).Value = DateTime.Parse(txtReviewDate.Text)
            Else
                DBCmd.Parameters.Add("@ReviewDate", SqlDbType.VarChar).Value = DBNull.Value
            End If

Select allOpen in new window

Then the INSERT Statement would be something like this:

INSERT INTO Applicants (ReviewType, ReviewDate) VALUES (@ReviewType, @ReviewDate)

Select allOpen in new window

I would just use the CASE/WHEN like the IF/THEN in the @ReviewDate parameter above.

I don't see a ReviewType or ReviewDate referenced in the above VB.NET code in the original T-SQL, so you'll have to explain exactly what you are trying to pull off here.  Preferably with sample data.

Thanks.  I think using the Parameter solution is the way to go.  Oh, in my hurry to type this up I failed to make it clear that "Zip", rather than being a column, is an input field on the form.  It should be something like "txtZip.Text", at least in the Parameter definition.

Going back to Jim's original statement it's better to keep the list of zipcodes in a table, that way you can manage it and not have to change the code to handle data changes.

Sorry.  Here's what I'm proposing:

INSERT INTO Applicants (Name, OrientationLocation, Program) VALUES (@Name, @OrientationLocation, @Program)

DBCmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text

CASE 
   WHEN txtZip.Text IN ('33311', '33309') THEN
                  DBCmd.Parameters.Add("@OrientationLocation", SqlDbType.VarChar).Value = 'Dillard High School'
   WHEN txtZip.Text IN ('33005', '33004') THEN 
                  DBCmd.Parameters.Add("@OrientationLocation", SqlDbType.VarChar).Value = 'Hallandale High School'
   WHEN txtZip.Text IN ('34111', '34112') THEN 
                  DBCmd.Parameters.Add("@OrientationLocation", SqlDbType.VarChar).Value = 'Pompano Beach High School'
   ELSE NULL END

DBCmd.Parameters.Add("@Program", SqlDbType.VarChar).Value = txtProgram.Text

Select allOpen in new window

you're mixing VB and sql there.

but here's what I'm proposing:
SQL:

create procedure iApplicant
@name varchar(100),
@zip varchar(10),
@program varchar(500)
as

declare @OL varchar(1000)
-- new table, 2 columns zipcode, orientationlocation
select @ol = orientationLocation from orientationLocations where zipcode = @zip

INSERT INTO Applicants (Name, OrientationLocation, Program) VALUES (@Name, @ol, @Program)

Select allOpen in new window

VB.net:

DBCmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text
DBCmd.Parameters.Add("@zip", txtZip.Text)
DBCmd.Parameters.Add("@Program", SqlDbType.VarChar).Value = txtProgram.Text

dbCmd.ExecuteNonScalar()

Select allOpen in new window

Hi Kyle,  Thanks for the input.  You're right.  I to have the zipcodes in a table.  The reality is always more involved than the examples.  The database where this information ultimately ends up uses Zip Code "codes", for example "33309" in their Table is Zip Code code "39."  So, I've got a table to convert from ZipCode to ZipCode "code" and visa versa.  Yeah, ug.  For the list of zip codes I display to the user there's a drop-down-list pulling the ListItems from the db table.

Yeah, the INSERT should start with DBCmd = New SqlCommand("INSERT INTO...

It's all in the .vb code.  I was originally just trying to get the sql syntax correct.  Keeping the vb out of the question for simplicity.  [FAIL]  ;-)

Okay.  I already have one stored proc for the same form.  Would it be better to add this to that SP or create a second SP and call it too?  I'm just concerned with making multiple trips to the database from a single form submission.

how complex is the form?  Are the objects related?  

Stored proc or direct insert you're already making another trip.  It's just how you make the trip.

I'll set up the sp in the morning.  I have to run out right now.  (I won't forget to close this question)  ;-)  Thanks for your help today.  Both of you.  :-)

Yeah, the form has a good 60 or so question.  Has one inline INSERT and the above mentioned SP to update the ZipCode with from the ZipCodeCode table and a couple of other things like that.  (I know.  I'm a terrible programmer.)

[ The reason for the extra "ZipCodeCode" update is the original table only stored the short "39" code for the zips.  It got to be a pain to view reports like that or do lookups in the reports every time someone wanted an ad hock report so I added a column for the "real" zip code.  I could have done it a better way, but 5 years ago when I made this form I was a REALLY terrible programmer.  ;-)

Thank you both for your help.

Thanks for the split.  Good luck with your code.  -Jim