March 4, 2010 at 2:13 pm
What I have looks to me like it should be simple to do, but I can't get it to work. I have a page that submits form data that should be added to one or two tables, with the auto increment field from the first table getting added into the second one with one or two pieces of data from the form (call these A and B). I set up a stored procedure that works fine to add all the necessary data into the first table. The new ID for that table is successfully retrieved and ready for use in the second table. I need a record written to the second table only if there's a value for A or B or both. I can't seem to pull this off. I'd like to do this in one stored procedure, rather than running one to write to the first table then another one to write to the second table if needed, but I'm about out of things to try. How do you do this in a stored procedure?
March 4, 2010 at 2:25 pm
I'm sure other may have better suggestions, but I'd think about using optional parameters. Pass in your data for table 1 and pass in A and B optionally. if A and B are specified run your second insert statment. If not, then don't. Here's some psuedocode that might help...
Also check the CREATE Procedure section of BOL for details on Defaults in stored procedures.
Declare mySproc @paramsForTable1, @a int = NULL, @b-2 int = NULL
AS
Check for valid values...
Insert into Table1
If @a IS NOT NULL and @b-2 IS NOT NULL
Begin
Insert into table 2....
End
-Luke.
March 4, 2010 at 2:50 pm
That definitely doesn't work. Putting it in like this:
If @a Is Not Null OR @b-2 Is Not Null
Results in a new record written into the second table with the new id from the first table. The condition for writing to the second table is "or" rather than "and" since there should be a record written if there's data in either A or B.
There just has to be a way of doing this, doesn't there?
March 4, 2010 at 6:51 pm
info 54357 (3/4/2010)
I'm not so smart today. I should have said that form data bits A and B are small text strings, which I'd set up like this: @a ntext, @b-2 ntext. Does the advice above apply if we're dealing with text instead of int?
I strongly advise against this. Text and NText are deprecated features of SQL Server, and are awful in any event. You would be much better off using NVarchar() instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 7:51 pm
RBarryYoung (3/4/2010)
info 54357 (3/4/2010)
I'm not so smart today. I should have said that form data bits A and B are small text strings, which I'd set up like this: @a ntext, @b-2 ntext. Does the advice above apply if we're dealing with text instead of int?I strongly advise against this. Text and NText are deprecated features of SQL Server, and are awful in any event. You would be much better off using NVarchar() instead.
I'm not stuck on leaving the field types as ntext. I can easily change that, but this doesn't work if the fields are nvarchar() either. I've tried both.
March 4, 2010 at 9:20 pm
you'll need to show us your actual SQL code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply