Please use commit after insert and update query because dml are not auto commit. Just write commit; after insert and update query On Dec 28, 2012 10:16 AM, "akki jatav" <[email protected]> wrote:
> Hi X jojo, > > use following code > > > CREATE > > PROCEDURE dbo.usp_insUpdt > > @fname > > varchar(20), > > @lname > > varchar(20), > > @position > > varchar(20), > > @dept_name > > varchar(20) > > AS > > BEGIN > > SET > > NOCOUNT ON > > Declare > > @t1_id int > > IF > > EXISTS (SELECT lname FROM t1 WHERE lname = @lname) > > BEGIN > > UPDATE t1 > > SET @fname = fname, @lname = lname, @position = position > > WHERE lname = @lname > > --here we are updating dept for table 2 > > update t2 set dept_name = @dept_name where fk_t1_id in (SELECT t1_id FROMt1 > WHERE lname = @lname) > > END > > ELSE > > BEGIN > > INSERT INTO t1(lname, fname,position) > > VALUES(@fname, @lname, @position) > > INSERT INTO t2(fk_t1_id,dept_name) > > VALUES(@@IDENTITY,@dept_name) > > END > > END > > > Regards, > Akhilesh Kumar Jatav > Contact Nos. 07428245993 > > > On Thu, Dec 20, 2012 at 4:04 PM, X <[email protected]> wrote: > >> Hey guys, >> I'm working on a select/update Stored Procedure where I want to take a >> primary key from one table and insert it into another table as a foreign >> key in the insert statement. >> >> Table t1 ( >> t1_id int IDENTITY (1,1) NOT NULL, >> fname varchar(20) NULL, >> lname varchar(20) NULL, >> position varchar(20) NULL >> ) >> >> Table t2( >> t2_id int IDENTITY (1,1) NOT NULL, >> fk_t1_id int NULL, >> dept_name varchar (20) NULL >> >> >> SPROC >> >> CREATE PROCEDURE dbo.usp_insUpdt >> @fname varchar(20), >> @lname varchar(20), >> @position varchar(20), >> @dept_name varchar(20) >> >> AS >> >> BEGIN >> >> SET NOCOUNT ON >> >> IF EXISTS (SELECT @lname FROM t1 WHERE @lname = @lname) >> UPDATE t1 >> SET @fname = fname, @lname = lname, @position = position >> WHERE lname = @lname >> >> ELSE >> INSERT INTO t1(lname, fname,position) >> VALUES(@fname, @lname, @position) >> END >> --The pk from t1 above should be inserted into fk t2 >> >> >> Any suggestions? >> >> Thanks in advance >> >> >> -- >> You received this message because you are subscribed to the Google >> Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML >> Web Services,.NET Remoting" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en >> or visit the group website at http://megasolutions.net >> > > -- > You received this message because you are subscribed to the Google > Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML > Web Services,.NET Remoting" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en > or visit the group website at http://megasolutions.net > -- You received this message because you are subscribed to the Google Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web Services,.NET Remoting" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en or visit the group website at http://megasolutions.net
