insert/update combination in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert/update combination in SP

Author

Topic

obrix
Starting Member

1 Post

Posted - 2007-06-03 : 00:42:42

im having a problem on how to create a stored procedure that behaves like in my previous code in vb6. it goes like this: say im going to insert or update a single record(header) and multiple records(detail).

--this is the header
strSQL="select * from myheadertable where myfield='" & myvariable & "'"
rs.open strSQL,cnConn..etc.

if rs.eof then
rs.addnew
rs!custid=txtcustid
endif
rs!name=txtname
rs!age=txtage
.
.
.
--this is the detail(say from grid)
strSQL="select * from mydetailtable where myfield='" & myvariable & "'"
rs2.open strSQL,cnConn..etc.

for i = 1 to .datarowcnt
.col=1:.row=i
.filter="rowid='" & trim(.text) & "'"
if rs2.eof then rs.addnew
.col=1:rs2!col1=.text
.col=2:rs2!col2=.text
.col=3:rs2!col3=.text
..
.
.
next i

--batchsave the two recordset(i have a function for batch saving..)
rs.update
rs2.update

my problems are:
1. how to convert this code if im using a stored procedure?
2. how to create a stored proc for this kind of code?

if anyone can help me please do so..thanks.:)

pootle_flump

1064 Posts

Posted - 2007-06-04 : 04:58:21

Hi obrix

Where are you starting from? Do you know the basics of sql dml (inserts, updates etc)? If not then you would do well to read through this first - it is a quick tutorial on the basics of sql syntax.
http://www.w3schools.com/sql/default.asp
Since you come from a procedural background it is worth keeping in mind you are dealing with a set based language now - there should be no need for looping and recordsets.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-06-05 : 11:30:17

while creating SP, add one more parameter for Mode. here u can pass the mode of the transaction u r going to perform. mean Insert or Update.

i.e.
If @Mode = 'INSERT'
Begin
Insert Into ...
End
Else
Begin
Update ...
End

let us know,

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources