weird behaivior - 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)

weird behaivior

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 02:16:18

Hi

I have this stored procedure that works a little odd, some times it do what I want it to do which is....

- Insert orderinformation in tbl_Torderinfo table and then transfer all rows that are associated with a ordernumber from tbl_Ctemp to tbl_Torders

but sometimes it only insert data into the tbl_Orderinfo table and doesn't transfer the order details from tbl_Ctemp to tbl_TOrders even if there is rows to transfer. Any ideas of what might cause this odd behaivior?



p_AddOrder 
@AdressID Int,
@UserID Int,
@UserRealName nVarChar(255),
@Comment nVarChar(4000),
@OurReference nvarchar(255),
@YourReference nvarchar(255),
@DeliveryAdress nvarchar(4000),
@Buyer nvarchar(4000),
@DeliverBy nvarchar(255),
@DestCountry nvarchar(255),
@FreightMark nvarchar(255),

AS
DECLARE @iOrderID Int
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_TOrderInfo
(
AdressID,
UserID,
UserRealName,
DateOrdered,
Comment,
OurReference,
YourReference,
DeliveryAdress,
Buyer,
DeliverBy,
DestCountry,
FreightMark,
OrderID
)
VALUES
(
@AdressID,
@UserID,
@UserRealName,
GETDATE(),
@Comment,
@OurReference,
@YourReference,
@DeliveryAdress,
@Buyer,
@DeliverBy,
@DestCountry,
@FreightMark,
@OrderID
)
SELECT @@IDENTITY
SET @iOrderID = @@IDENTITY

--Insert New ID and Info into Orders table
INSERT INTO tbl_TOrders (TOrderID,ProductName,OwnBID)

SELECT @iOrderID, Product, OwnBID FROM tbl_CTemp WHERE UID = @OrderID
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 02:22:06

Your posted code doesnt have @OrderID parameter defined. Then where are you getting value of Orderid?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 02:24:12

At what instinct are you inserting the records in tbl_CTemp?

Chirag

http://www.chirikworld.com

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 02:25:10

Sorry I missed this one ...

@OrderID nVarChar(50) that goes below @freightmark

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 02:29:21

Hi Chirag

Forgive my poor english, but what do you mean?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 02:33:04

from your code, I can not see anywhere you are inserting the record in the table tbl_cTemp ?
when are you inserting the record in tbl_ctemp?

Chirag

http://www.chirikworld.com

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 02:39:36

There are allready data in the tbl_Ctemp table, so that part is not inside this stored procedure

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 02:45:40

From you stored procedure, I dont see any reason why isnt inserting the record in the table..
Probably you can test it out if there are records in the tbl_cTemp before inserting it...

Something like

If Not Exists ( Select 1 From Tbl_CTemp Where OrderId = @OrderId)
Print 'No Records Exists in the Temp Table'

Chirag

http://www.chirikworld.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 02:59:12

We are missing parts of the procedure.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 03:03:55

Also, not directly related to problem posted, but you could use SCOPE_IDENTITY instead of @@IDENTITY as @@IDENTITY will return last identity generated in current session and would not give you id generated for tbl_TOrderInfo always (for ex in case where a trigger exists on your table)

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 03:15:18

why would you say that there is missing parts in th sp?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 03:40:59

First, this part (I assume the header of procedure) is missing CREATE PROCEDURE and last parameter ends with a comma

create procedure p_AddOrder (
@AdressID Int,
@UserID Int,
@UserRealName nVarChar(255),
@Comment nVarChar(4000),
@OurReference nvarchar(255),
@YourReference nvarchar(255),
@DeliveryAdress nvarchar(4000),
@Buyer nvarchar(4000),
@DeliverBy nvarchar(255),
@DestCountry nvarchar(255),
@FreightMark nvarchar(255),
)
AS

Second, @OrderID is not defined anywhere in the original post.
But is used in last statement.

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 05:06:17

Sorry for the missing create procedure part, I also posted the missing @OrderID as you can see above. But the main problem still occurs, why does the sp work sometimes and sometimes not...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 05:17:00

See last row...

CREATE PROCEDURE dbo.usp_AddOrder
(
	@AdressID INT,
	@UserID INT,
	@UserRealName NVARCHAR(255),
	@Comment NVARCHAR(4000),
	@OurReference NVARCHAR(255),
	@YourReference NVARCHAR(255),
	@DeliveryAdress NVARCHAR(4000),
	@Buyer NVARCHAR(4000),
	@DeliverBy NVARCHAR(255),
	@DestCountry NVARCHAR(255),
	@FreightMark NVARCHAR(255),
	@OrderID NVARCHAR(50)
)
AS

SET NOCOUNT ON

DECLARE	@iOrderID INT

INSERT	tbl_TOrderInfo
	(
		AdressID,
		UserID,
		UserRealName,
		DateOrdered,
		Comment,
		OurReference,
		YourReference,
		DeliveryAdress,
		Buyer,
		DeliverBy,
		DestCountry,
		FreightMark,
		OrderID
	)
VALUES	(
		@AdressID,
		@UserID,
		@UserRealName,
		GETDATE(),
		@Comment,
		@OurReference,
		@YourReference,
		@DeliveryAdress,
		@Buyer,
		@DeliverBy,
		@DestCountry,
		@FreightMark,
		@OrderID
	)

SET	@iOrderID = SCOPE_IDENTITY()
SELECT	@iOrderID

INSERT	tbl_TOrders
	(
		TOrderID,
		ProductName,
		OwnBID
	)
SELECT	@iOrderID,
	Product,
	OwnBID
FROM	tbl_CTemp
WHERE	UID = @OrderID

Shouldn't that read

WHERE	OrderID = @OrderID

instead of

WHERE	UID = @OrderID

???
UID for me denotes UserID.

The insert will then only work when a user have same ID as an order.

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 06:35:12

But the UID field have the same nVarchar value as the @OrderID nVarchar value as being passed on into the SP, so that should work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 06:39:17

quote:
Originally posted by magmo

But the UID field have the same nVarchar value as the @OrderID nVarchar value as being passed on into the SP, so that should work

Why is UID having datatype of NVARCHAR? isnt it having ID i.e int values?

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 06:44:55

I agree that the UID is kinda confusing, but no. This is a nvarcharvalue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 06:55:25

So UID has the value for OrderID? Not UserID?

Perhaps you should write

WHERE	UID = @UserID

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 07:05:45

The OrderID value is a value like this... "123456-789" This value exsits in the tbl_CTemp from the start, I then pass "123456-789" into the SP (@OrderID NVARCHAR(50)). I then filter my insert statement based on this value. I cannot filter based on the UID = @UserID, beacuse the UID is the nVarcharValue, and the UserID is a integer value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 07:09:54

Are both tbl_CTemp.UID and @OrderID of same length?
What column datatype and length does tbl_CTemp.UID have?

Could you provide us with the table layout of tbl_ctemp?

E 12°55'05.25"
N 56°04'39.16"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-06-13 : 07:16:14

Yes both are nvarchar(50)

USE [MyTestDB]
GO
/****** Object: Table [dbo].[tbl_CTemp] Script Date: 06/13/2008 13:11:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_CTemp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UID] [nvarchar](50) NOT NULL,
[Product] [nvarchar](100) NULL,
[OwnBID] [int] NULL,
CONSTRAINT [PK_tbl_CTemp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources