Solved

The query sometimes works correctly< sometimes not

Posted on 2008-10-07
26
195 Views
Last Modified: 2013-11-05
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
Comment
Question by:exceter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
26 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666525
replace:
      IF @code=(SELECT code FROM prize_codes WHERE code=@code)
by:

      IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
0
 
LVL 3

Author Comment

by:exceter
ID: 22666618
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666630
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!

 
LVL 3

Author Comment

by:exceter
ID: 22666674
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666752
ok:
@code char(6),

what is the data type in the table? is it also char(6)? or varchar(...) ?
0
 
LVL 3

Author Comment

by:exceter
ID: 22666793
hmm, it is NCHAR(10)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666827
that is the problem, then.
please use NVARCHAR / VARCHAR instead of NCHAR/CHAR, as CHAR data types do right-padding with spaces.
0
 
LVL 3

Author Comment

by:exceter
ID: 22667042
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22667065
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
 
LVL 3

Author Comment

by:exceter
ID: 22667096
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22667116
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
 
LVL 3

Author Comment

by:exceter
ID: 22667153
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22667165
>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
 
LVL 3

Author Comment

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

THANK YOU.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22667238
glad I could help +++
maybe we reopen the question to accept the "right" answer?
angel eyes
0
 
LVL 3

Author Comment

by:exceter
ID: 22667294
if that is possible, I would reaccept the "right" answer with a great pleasure.
How to do that? I could not find..
0
 
LVL 3

Author Closing Comment

by:exceter
ID: 31504117
Excellent help +++
0
 
LVL 3

Author Comment

by:exceter
ID: 22675978
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22676150
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
 
LVL 3

Author Comment

by:exceter
ID: 22676174
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
 
LVL 3

Author Comment

by:exceter
ID: 22676183
insert statements are in procedure
0
 
LVL 3

Author Comment

by:exceter
ID: 22676205
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22677158
but there must be some starter data in the tables, no?
what parameter values will I have to test with?
0
 
LVL 3

Author Comment

by:exceter
ID: 22677418
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22677458
ok!
wish you a nice future!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question