Link to home
Start Free TrialLog in
Avatar of exceter
exceterFlag for Kyrgyzstan

asked on

The query sometimes works correctly< sometimes not

Hi,
the following code does not work correctly, that is, sometimes works as needed(correctly), sometimes gives incorrect query results.
The Query  [ IF @code=(SELECT code FROM prize_codes WHERE code=@code) ] may say that there is a code in table,  or not, even if there exists.
What is wrong? something with DB or the OS? or my query?
USE [textsms]
GO
/****** Object:  StoredProcedure [dbo].[sp_getSMvalue]    Script Date: 10/08/2008 10:52:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_getSMvalue]
@sender char(12),
@code char(6),
@RetText nchar(158) OUTPUT
AS
	DECLARE 
		@maxVal int,		@cVal int,			@codeID int,		@tmp int,		@winner bit,
		@Upper int,			@Random int,		@repeatingCode bit,	@repCnt int,	@str1 nchar(150),
		@str2 nchar(150),	@strPrize nchar(20),@length int		
 
SET @Upper = (SELECT MAX(id) FROM Anekdots)
--SET @Random = ROUND(((@Upper -1) * RAND() + 1), 0)
SET @Random = 1 + CONVERT(INT, (@Upper)*RAND())
SET @repCnt = (SELECT COUNT(code) FROM prize_sender WHERE code=(SELECT code FROM prize_codes WHERE code=@code ))
 
SET @tmp = (SELECT val FROM sm_value WHERE id=1)
IF (@tmp < (SELECT max_val FROM sm_value WHERE id=1))
SET @winner = 0
ELSE SET @winner = 1
 
IF @winner = 0 -- A;8 =5 ?>1548B5;L
BEGIN
	 IF @code=(SELECT code FROM prize_codes WHERE code=@code) 
	-- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
	BEGIN
		SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
		INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
		-- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
		IF (@repCnt > 0)
		BEGIN
			SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)			
			SET  @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
			SET @RetText =  'Kod otpravljat ne bolee 1 raz.' +CHAR(13)+ @str2 
		END
		ELSE BEGIN
			SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
			SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
			SET @RetText = 'Spasibo za SMS. '+@str2 
			UPDATE sm_value
			SET val = val + 1,
			total_sms = total_sms + 1		
		END
	END
	ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
	BEGIN
		INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)		
-- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
		SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
		SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
		SET @RetText = 'Kod nepravilni. ' +@str2 
	END
END
ELSE -- A;8 ?>1548B5;L
BEGIN
	IF @code=(SELECT code FROM prize_codes WHERE code=@code) 
	-- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
	BEGIN
		SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
		INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
		IF (@repCnt > 0)
		BEGIN
			SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
			SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
			SET @RetText =  'Kod otpravljat ne bolee 1 raz. ' + @str2
		END
		ELSE BEGIN
			SET @Upper = (SELECT MAX(id) FROM Prize WHERE Total>0)
			SET @Random = ROUND(((@Upper -1) * RAND() + 1), 0)
			SET @RetText = 'Vy viigrali priz-'+ ' ' +(SELECT name FROM prize WHERE id=@Random)-- +'. Prosba szjazatsja po nomeru '
			UPDATE prize
				SET total = total - 1 WHERE id=@Random	
			
			UPDATE sm_value
				SET val = 0,
				cnt_prize = cnt_prize + 1, 
				total_sms = total_sms + 1
				--WHERE id = 1;
		END
	END
	ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
	BEGIN
		INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)
		SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
		SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)		
		SET @RetText =  'Kod nepravilni. '+@str2 		
	END
END

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

replace:
      IF @code=(SELECT code FROM prize_codes WHERE code=@code)
by:

      IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
Avatar of exceter

ASKER

angelIII

Why  
  IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
is better than
  IF @code=(SELECT code FROM prize_codes WHERE code=@code)
???

what is the difference? I made those changes,  testing now
actually, the question is: can @code by NULL or not?
and in case @code is null is there a row in prize_codes with NULL also?
Avatar of exceter

ASKER

No, @code cant be NULL. it must exist.

Look here:
IF @winner = 0 -- A;8 =5 ?>1548B5;L
BEGIN
      IF @code=(SELECT code FROM prize_codes WHERE code=@code)
sometimes returns that the @code does not exist, and does following:
ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
      BEGIN
            INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)

and if query the same @code value, it does following:
IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
       --IF @code=(SELECT code FROM prize_codes WHERE code=@code)
      -- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
      BEGIN
            SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
            INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
            -- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
            IF (@repCnt > 0)
            BEGIN
                  SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)                  
                  SET  @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
                  SET @RetText =  'Kod otpravljat ne bolee 1 raz.' +CHAR(13)+ @str2

ok:
@code char(6),

what is the data type in the table? is it also char(6)? or varchar(...) ?
Avatar of exceter

ASKER

hmm, it is NCHAR(10)
that is the problem, then.
please use NVARCHAR / VARCHAR instead of NCHAR/CHAR, as CHAR data types do right-padding with spaces.
Avatar of exceter

ASKER

I think that the problem is in my PERL.
I call this procedure there, and sometimes it returns empty value. But the procedure itself should return always non-empty value.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exceter

ASKER

What is the help of that?

I am now sure that the problem is in my PERL code. It time by time looses connection to DB.
sorry, you are right, as you work with OUTPUT parameter...

question:
SET @RetText =  'Kod nepravilni. '+@str2          

is @str2 non-null in all the cases?
you might try to ensure it is not:
SET @RetText =  'Kod nepravilni. '+ ISNULL(@str2  , '-n/a-')
Avatar of exceter

ASKER

look at above lines, it cant be NULL:
...
            SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
            SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)      
SET @RetText =  'Kod nepravilni. '+@str2  
...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exceter

ASKER

yep, great help man.
unfortunately I found there some ID's which return NULL values.

THANK YOU.
glad I could help +++
maybe we reopen the question to accept the "right" answer?
angel eyes
Avatar of exceter

ASKER

if that is possible, I would reaccept the "right" answer with a great pleasure.
How to do that? I could not find..
Avatar of exceter

ASKER

Excellent help +++
Avatar of exceter

ASKER

I am sad.
I continue getting wrong query results. that the code does not exist.
I did changes that you suggested, but the problem still there :(
can you post the CREATE TABLE and the INSERT statements for the sample data, so I could try to reproduce the problem on my side?
Avatar of exceter

ASKER

my tables are:
CREATE TABLE [dbo].[prize_codes](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[code] [nvarchar](6) NULL,
	[name] [nvarchar](50) NULL,
	[type] [nvarchar](50) NULL,
	[datetime] [datetime] NULL CONSTRAINT [DF_prize_codes_datetime]  DEFAULT (getdate()),
 CONSTRAINT [PK_prize_codes] 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]
 
 
 
CREATE TABLE [dbo].[prize_sender](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sender] [nvarchar](50) NULL,
	[code] [nvarchar](50) NULL,
	[prizeCode_id] [int] NULL,
	[winner] [bit] NULL CONSTRAINT [DF_prize_sender_winner]  DEFAULT ((0)),
	[prize_id] [int] NULL,
	[datetime] [datetime] NULL CONSTRAINT [DF_prize_sender_datetime]  DEFAULT (getdate()),
 CONSTRAINT [PK_prize_sender] 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]
 
 
CREATE TABLE [dbo].[prize](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](20) NULL,
	[Total] [int] NULL,
	[datetime] [datetime] NULL CONSTRAINT [DF_prize_datetime]  DEFAULT (getdate()),
 CONSTRAINT [PK_prize] 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]

Open in new window

Avatar of exceter

ASKER

insert statements are in procedure
Avatar of exceter

ASKER

one more table:
CREATE TABLE [dbo].[Anekdots](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Anekdot] [nvarchar](130) NOT NULL
) ON [PRIMARY]

Open in new window

but there must be some starter data in the tables, no?
what parameter values will I have to test with?
Avatar of exceter

ASKER

sorry, at the moment I have no access to DB.
:( and will be in 10 days.
I am going to marry in a few days.
ok!
wish you a nice future!