Insert statemtent - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Insert statemtent

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-09 : 18:55:26

I am using the bleow sp to insert only the records that the the combination of invtid and siteid for the given siteid in itemsite does not exist in XRItemSiteCEP table...

will my log work?


alter proc ust_InsertNewInv ( @siteid varchar(6) )
AS

Begin Tran
 Insert Into XRItemSiteCEP

	Select	'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
		1,
		'','','','',
		InvtID,
		'',
		LeadTime,
		'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
		PrimVendID,
		'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
		SiteID,rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)),
		'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',null
	From ItemSite
	Where 
     SiteID = @siteid 
     and 
     rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)) Not in ( select rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)) from XRItemSiteCEP where siteid = @siteid )

IF @@ERROR <> 0
BEGIN

    Rollback 
    PRINT @@ERROR
    RETURN -1
END
  ELSE
  BEGIN
    Commit
    RETURN 0
 END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 19:02:33

Have you tried?

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 19:26:42

You don't need a transaction here as it's implied since you've got only one DML statement.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-09 : 19:36:00

quote:
Originally posted by tkizer

You don't need a transaction here as it's implied since you've got only one DML statement.

Tara Kizer

I am trying ot rollback if there is an error with -1
or commit and return 0...

How can I do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 19:40:13

Just test @@ERROR. You don't need an explicit transaction with one insert statement. Just get rid of BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 19:42:24

You don't have to use transaction, as Tara said.

alter proc ust_InsertNewInv ( @siteid varchar(6) )
AS

 Insert Into XRItemSiteCEP

	Select	'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
		1,
		'','','','',
		InvtID,
		'',
		LeadTime,
		'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
		PrimVendID,
		'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
		SiteID,rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)),
		'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',null
	From ItemSite
	Where 
     SiteID = @siteid 
     and 
     rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)) Not in ( select rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)) from XRItemSiteCEP where siteid = @siteid )

IF @@ROWCOUNT > 0
    RETURN 0
  ELSE
  BEGIN
PRINT @@ERROR
    RETURN -1
 END

Peter Larsson
Helsingborg, Sweden

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-10 : 10:11:05

thanks, but it ruturns 0 even though there are o rows affected..

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-10 : 10:23:38

I am getting the below result.. no row affected but returns 0 .. where is 0 comes from? it should be -1.... right?
(0 row(s) affected)

0

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-10 : 10:31:45

The 0 which you are getting is the output of PRINT statement:

PRINT @@ERROR

You won't get -1 printed since it is a return value which is returned to the caller of the SP.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-10 : 10:42:19

thanks, would this work then? return 1 to a caller of SP if there are more than 0 rows got inserted, otherwise return -1..

alter proc usp_InsertNewInv ( @siteid varchar(6) )
AS

Insert Into XRITEMSiteCEP
Select '','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
1,
'','','','',
InvtID,
'',
LeadTime,
'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
PrimVendID,
'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
SiteID,
'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',null
From ItemSite
Where
SiteID = @siteid
and
rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)) not in ( select rtrim(ltrim(invtid)) + rtrim(ltrim(siteid)) from XRItemSiteCEP where siteid = @siteid )

IF @@ROWCOUNT > 0
RETURN 1
ELSE

RETURN -1

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-10 : 12:15:22

If you consider 0 rows affected a user error, then interogate @@ROWCOUNT

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources