newbie - how to make an MS SQL sp to save a vb.net forms values? 1

[azarcomp]

I know this is both a T-Sql and a VB.net question, but did not know where to post...

SO I finally got hired and am rusty as can be. so much its embarrassing.

I need to make a sp that the vb.net forms controls will pass and updated values too.

so do i make one update sp for the form to call and pass all values too?

like I went to the view i created and went to scripts to clipboard as Update and it gives me a skeleton for every column. of course i know the PK will not be updated but the rest of the columns?

so if i did like this, what would i do if only 1 column needed to be updated and not all 50 of them.

i guess maybe the set line could have a parm?

set @columnToBeUpdated = @Value,
but then what if there are 6 or 8 to be updated?

As u can see, I desparately need to be pointed in the right direction.
any help greatly appreciated

 

[azarcomp]

Ok, well I am trying to narrow it down, this is not the dev tables or data.
just trying to see if someone thinks i am on the right track.

now I am trying to figure out if the only and best wat to update into 2 or more tables at the same time is to use 2 or more update queries.

like so:
1.make the updates for each table
update Customers
set Name = 'TwinBob'
where customerId = 1;
update dbo.address
set Street = 'newstreet Rd'
where CustomerID = 1

--Below select just to view my update changes--
select c.CustomerID,c.Name,a.AddressID,a.Street from Customers c left join address a
on c.CustomerID = a.CustomerID

am I on the right track???
2. Then make parameters for each column of each table and make the whole batch a stored procedure (T-sql).

3. Then in VB.net pass the vales of columns to be updated to the stored proc from vb.net.

 

[jshurst]

Yes, you'll need to use 2 (or more update queries). I suppose that you could do some joins and update multiples, but I've never done it that way. Multiple update querries can be in the same stored procedure if you want, or you can separate the SP's out to be more object like (usp_Update_Customers and usp_Update_Addresses, etc).

Also as far as updating many columns versus just one, what I usually do is write all of the columns out then just pass over null values if I want to leave the columns alone. This really depends on what you are attempting to do though. You might want to leave a colum like it is if you pass over a null, or you might want to actually set the column to a null value, you'll have to make that determination in code (in the SP or your application code). Let's say that you wanted to update the first name, but wanted to leave the last name alone.

--normally you won't declare these as the will be SP params
DECLARE @FirstName AS varchar(50)
, @LastName AS varchar(50)
, @CustomerID AS int

SET @FirstName = null
,@LastNAme = null
,@CustomerID = 1

UPDATE Customers
SET FirstName = @FirstName  --if the variable is null then the new value will be null (assuming the column allows nulls)
, LastName = IsNull(@LastName,LastName)  --if the variable is null then the new value will stay the same
WHERE CustomerID = @CustomerID

Regards,

J

 

[azarcomp]

thanks, just got fired. i had 6 months off and got rusty, i was practicing linux and sql dba not knowing what i may get hired for. well, i got 3.5 days pay. better than nothing