• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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

0
exceter
Asked:
exceter
  • 14
  • 11
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
replace:
      IF @code=(SELECT code FROM prize_codes WHERE code=@code)
by:

      IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
0
 
exceterAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
exceterAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok:
@code char(6),

what is the data type in the table? is it also char(6)? or varchar(...) ?
0
 
exceterAuthor Commented:
hmm, it is NCHAR(10)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that is the problem, then.
please use NVARCHAR / VARCHAR instead of NCHAR/CHAR, as CHAR data types do right-padding with spaces.
0
 
exceterAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you add the SET NOCOUNT ON as the first line of the procedure?
maybe that's the problem of "sometimes" not getting the results?
0
 
exceterAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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-')
0
 
exceterAuthor Commented:
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  
...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>look at above lines, it cant be NULL:
i have seen those lines, and it still could be NULL if the id=@random does not return a line, for example ...

as I don't know the context, I can't be sure that the value generated into @random is indeed found in that table...
0
 
exceterAuthor Commented:
yep, great help man.
unfortunately I found there some ID's which return NULL values.

THANK YOU.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help +++
maybe we reopen the question to accept the "right" answer?
angel eyes
0
 
exceterAuthor Commented:
if that is possible, I would reaccept the "right" answer with a great pleasure.
How to do that? I could not find..
0
 
exceterAuthor Commented:
Excellent help +++
0
 
exceterAuthor Commented:
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 :(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
 
exceterAuthor Commented:
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

0
 
exceterAuthor Commented:
insert statements are in procedure
0
 
exceterAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
but there must be some starter data in the tables, no?
what parameter values will I have to test with?
0
 
exceterAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok!
wish you a nice future!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now