Solved

The query sometimes works correctly< sometimes not

Posted on 2008-10-07
26
192 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
  • 14
  • 11
26 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

Author Comment

by:exceter
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
hmm, it is NCHAR(10)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
>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
Comment Utility
yep, great help man.
unfortunately I found there some ID's which return NULL values.

THANK YOU.
0
 
LVL 142

Expert Comment

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

Author Comment

by:exceter
Comment Utility
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
Comment Utility
Excellent help +++
0
 
LVL 3

Author Comment

by:exceter
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
insert statements are in procedure
0
 
LVL 3

Author Comment

by:exceter
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now