Records not updating - 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

General SQL Server Forums

New to SQL Server Programming

Records not updating

Author

Topic

Stumbler
Starting Member

23 Posts

Posted - 2009-03-15 : 06:02:58

Can somebody please tell me why this stored procedure is not updating the record like I expect. If execute the last 3 lines of code as a query, it works fine. When I put it in a stored procedure the records are not updated.

Thanks for your advise.


USE [touchdb]
GO
/****** Object:  StoredProcedure [dbo].[AppendTicket]    Script Date: 03/15/2009 09:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Hans Wittoeck
-- Create date: 12/03/2009
-- Description:	voegt blanko ticket toe
-- =============================================
ALTER PROCEDURE [dbo].[AppendTicket]

@posname varchar(20) = 'kassa1',
@sales_person varchar(8)= 'CARLA',
@sales_location varchar(10) = 'HIER',
@ticket float

as
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

	select @ticket = isnull(ticket + 1,ticket_offset+1) from ticket_number where pos_name = @posname;
	update ticket_number
	set ticket=@ticket where pos_name = @posname;
END

Hans, Belgium

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 06:06:34

We'll need same sample data before making any assumptions.

E 12°55'05.63"
N 56°04'39.26"

Stumbler
Starting Member

23 Posts

Posted - 2009-03-15 : 06:14:03

This is my table


USE [touchdb]
GO
/****** Object:  Table [dbo].[ticket_number]    Script Date: 03/15/2009 11:09:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ticket_number](
	[pos_name] [nvarchar](20) NULL,
	[ticket_offset] [float] NULL DEFAULT ((0)),
	[ticket] [float] NULL
) ON [PRIMARY]

Some data




     	20000	20001	
kassa1	5000	NULL	
kassa2	10000	NULL	
kassa3	15000	NULL

Hans, Belgium

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 10:44:56

Is [20000] a column name?

E 12°55'05.63"
N 56°04'39.26"

Stumbler
Starting Member

23 Posts

Posted - 2009-03-15 : 13:16:18

quote:

Originally posted by Stumbler

This is my table


USE [touchdb]
GO
/****** Object:  Table [dbo].[ticket_number]    Script Date: 03/15/2009 11:09:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ticket_number](
	[pos_name] [nvarchar](20) NULL,
	[ticket_offset] [float] NULL DEFAULT ((0)),
	[ticket] [float] NULL
) ON [PRIMARY]

Some data



pos_name  ticket_offset ticket
     	20000	20001	
kassa1	5000	NULL	
kassa2	10000	NULL	
kassa3	15000	NULL

Hans, Belgium

The first record does not contain a pos_name (pos_name is blank)
sorry for being incomplete.

Hans, Belgium

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-15 : 14:12:55

quote:

Originally posted by Stumbler

Can somebody please tell me why this stored procedure is not updating the record like I expect. If execute the last 3 lines of code as a query, it works fine. When I put it in a stored procedure the records are not updated.

Thanks for your advise.


USE [touchdb]
GO
/****** Object:  StoredProcedure [dbo].[AppendTicket]    Script Date: 03/15/2009 09:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Hans Wittoeck
-- Create date: 12/03/2009
-- Description:	voegt blanko ticket toe
-- =============================================
ALTER PROCEDURE [dbo].[AppendTicket]

@posname varchar(20) = 'kassa1',
@sales_person varchar(8)= 'CARLA',
@sales_location varchar(10) = 'HIER',
@ticket float

as
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

	select @ticket = Coalesce(ticket + 1,ticket_offset+1,0)+1 from ticket_number where pos_name = @posname;
	update ticket_number
	set ticket=@ticket where pos_name = @posname;
END

Hans, Belgium

Stumbler
Starting Member

23 Posts

Posted - 2009-03-15 : 15:06:24

Thanks for your answer.
But I am afraid that the problem has to with my variable declaration.
Your suggestion works in a query (just as mine), but when I incorporate it in my stored procedure and run it, nothing gets updated.

Hans, Belgium

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 16:14:29

Are you 100% sure your @posname variable value really exists in tickrt_number table?
Have you checked collation, case sensitivity etc?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-15 : 16:17:45

[code]ALTER PROCEDURE dbo.AppendTicket
(
@posname varchar(20) = 'kassa1',
@sales_person varchar(8)= 'CARLA',
@sales_location varchar(10) = 'HIER',
@ticket float
)
AS

SET NOCOUNT ON

select @ticket = COALESCE(ticket + 1, ticket_offset + 1, 1)
from ticket_number
where pos_name = @posname

update ticket_number
set ticket = @ticket
where pos_name = @posname

IF @@ROWCOUNT = 0
INSERT ticket_number
(
pos_name,
ticket_offset,
ticket
)
VALUES (
@posname,
@ticket,
NULL
)[/code]

E 12°55'05.63"
N 56°04'39.26"

Stumbler
Starting Member

23 Posts

Posted - 2009-03-16 : 15:49:26

Peso,

If I try your code, nothing happens.
No row is added!

I don't understand

Hans, Belgium

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 16:36:38

How are you calling the AppendTicket stored procedure?

E 12°55'05.63"
N 56°04'39.26"

Stumbler
Starting Member

23 Posts