Bipolar - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Bipolar

Author

Topic

DarkConsultant
Starting Member

17 Posts

Posted - 2008-04-30 : 11:43:45

Hi everyone,

Strange title but I have gone mad. I'll explain ...

TableName = Table
UIN = int
Agent = int
Client = varchar(50)
Result = varchar(10)


UIN         Agent          Client         Result
1           1              Fred Bloggs    Sold
2           4              Bill Bailey    Not Sold
3           1              Tom Jones      NULL
4           2              Ida Hoe        Sold

Query
SELECT UIN,Agent,Client,Result FROM Table WHERE Agent=1 AND Result='Sold'

Result
2 rows 1 and 3

Changed NULL on row 3 to NotSold (NULL means no result yet)

Result
2 rows 1 and 3

Can anyone please help me with this?

I am using SQL Server Express 2005 and vb.Net 2005 and have started talking to my cat.

Thanks in anticipation.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-30 : 11:52:06

Are you describing what you want or what you are seeing but don't want?

quote:
Query
SELECT UIN,Agent,Client,Result FROM Table WHERE Agent=1 AND Result='Sold'

This will only give 1 row - not 2?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

DarkConsultant
Starting Member

17 Posts

Posted - 2008-04-30 : 11:54:23

Hi,

I am showing what I get which is NOT what I want or expect.

And yes it does give 2 rows every single time.

Here kitty kitty ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 12:12:40

quote:
Originally posted by DarkConsultant

Hi,

I am showing what I get which is NOT what I want or expect.

And yes it does give 2 rows every single time.

Here kitty kitty ....

It will give only 1 row with ID 1. are you using this as a part of some other query?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-30 : 12:33:54

That will only yield one row, based upon the data you have given. So if you are recieving two rows then either a different query is being submitted to the database or your query is being run against a different copy of the database.
Are you running this in SQL Server, or is it dynamic SQL being passed through by you application?

e4 d5 xd5 Nf6

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-30 : 12:34:49

Any chance you are NOT running the FULL query as posed in the original question?

DarkConsultant
Starting Member

17 Posts

Posted - 2008-04-30 : 14:36:42

Hello All,

This is truly strange.

I am fairly new to SQL although I have been a programmer for 37 years I only started my first SQL app in January.
This is the start of my second app and I thought I had forgotten it all already .. all the info I have given is accurate. I have dropped the table and recreated it manually and I still get the same result. In the original table there were over a hundred fields so I reduced it to the four I gave. The problem was exactly the same with the full table as with the reduced table. The query I gave was exactly as shown and although everyone will say 'That will return only one row', it always returns two. I have deleted the 'extra' row and another 'extra' row gets returned. If I reduce the table down to three records I get one row returned but the moment I add another record back comes the problem of two rows being returned.

Blindman, the query is sent from my new vb.Net app usning the query I gave. I have around 30 other SQL statements used in my part-built app and these all work fine. If I query the table using "SELECT COUNT(Result) FROM Table WHERE Result='Sold'", I get 1 returned, if I use "SELECT COUNT(Agent) FROM Table WHERE Agent=1" I get 2, both Integers obviously.

I originally thought this was a NULL problem but I have proved that this is nothing to do with it. Has anyone seen this behaviour before?

TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 14:45:46

quote:
Originally posted by DarkConsultant

Hello All,

This is truly strange.

I am fairly new to SQL although I have been a programmer for 37 years I only started my first SQL app in January.
This is the start of my second app and I thought I had forgotten it all already .. all the info I have given is accurate. I have dropped the table and recreated it manually and I still get the same result. In the original table there were over a hundred fields so I reduced it to the four I gave. The problem was exactly the same with the full table as with the reduced table. The query I gave was exactly as shown and although everyone will say 'That will return only one row', it always returns two. I have deleted the 'extra' row and another 'extra' row gets returned. If I reduce the table down to three records I get one row returned but the moment I add another record back comes the problem of two rows being returned.

Blindman, the query is sent from my new vb.Net app usning the query I gave. I have around 30 other SQL statements used in my part-built app and these all work fine. If I query the table using "SELECT COUNT(Result) FROM Table WHERE Result='Sold'", I get 1 returned, if I use "SELECT COUNT(Agent) FROM Table WHERE Agent=1" I get 2, both Integers obviously.

I originally thought this was a NULL problem but I have proved that this is nothing to do with it. Has anyone seen this behaviour before?

TIA

From the sample data posted

SELECT COUNT(Result) FROM Table WHERE Result='Sold'
should get 2 (UIN=1 & 4)

SELECT COUNT(Agent) FROM Table WHERE Agent=1
should also get you 2 (UIN=1 & 3)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 15:11:05

It can depend on current collation settings.
Maybe true data for Result column is 'sold' and 'Sold'

With certain collations they are not interpreted the same.

E 12°55'05.25"
N 56°04'39.16"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-30 : 15:25:11

quote:
Originally posted by DarkConsultant

Blindman, the query is sent from my new vb.Net app

Then that is where the problem is occuring.
If you really want to eliminate this issue and, at the same time, develop your application correctly according to database best practices, write a stored procedure to return this data rather than passing through an SQL statement.

e4 d5 xd5 Nf6

DarkConsultant
Starting Member

17 Posts

Posted - 2008-04-30 : 20:03:39

Hi All,

I sorta fixed it without find out how or why. It seemed to object to my column names. I recreated the table using different names and the problem went away.

Now the strange part ...

Using the same script to recreate the 'problematic' table I cannot recreate the problem.

I promise I have changed nothing and I think I am SQLphobic.

Blindman, thanks for your input but stored procedures are a touchy subject for me. I have read several tutorials and articles regarding stored procedures and although I think I understand them I have never got one to work. An ignorant observation from one who really does not know, they seem very very long winded to perform relatively simple tasks. A thousand apologies to anyone who tries to take offence at anything I say.

I consider myself to be a better than average applications coder but SQL leaves me a bit cold as I have found it unpredictable on all levels.

When I first started to use SQL in February I asked for help with stored procedures and was told to 'go away and learn SQL' (NOT in this forum may I add, everyone has been wonderful .. so far!?)

Can anyone suggest a tutorial preferably aimed at a small precocious child or immature primate?

I dont know why I dislike SQL I used to code in COBOL and that was ****.

Thanks to everyone who contibuted in helping me with my problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-30 : 20:09:57

DarkConsultant,

I don't know what you mean by stored procedures being long winded, but let's look at a simple example. Let's say you want to run the following query:

SELECT Column1, Column2
FROM Table1
WHERE Column3 = 1

You can choose to embed the query in your application or put it into a stored procedure. Let's take a look at what the stored procedure would look like:

CREATE PROC usp_SomeProc
AS

SELECT Column1, Column2
FROM Table1
WHERE Column3 = 1
GO

As you can see, there is nothing long winded about it. Whatever you would have run in your application just gets copied into a stored procedure. So if you've seen any examples of stored procedures that had long winded code, it was because it needed to be long winded and would have been long winded if embedded into the application.

HTH,

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Jenda
Starting Member

29 Posts

Posted - 2008-04-30 : 20:16:05

Not really a help with stored procs, but ... varchar(10) for a column with only three different values? That's not the right design. While it looks nice and readable now, it's unnecessarily slow. It's much more expensive to store and compare a varchar(10) than a bit or tinyint (yes, in this case a bit is enough, 1=Sold, 0=Not Sold, Null stays).

Stored procedures as such are not complex, but I would not be surprised if the .Net acces to them was ... interesting. It's outside the topic of this forum, but anyway. I do believe that ADO.Net is a big step in the wrong direction.

Jenda

DarkConsultant
Starting Member

17 Posts

Posted - 2008-04-30 : 20:41:04

Hi tkizer,

Thanks for the reply. That did seem very easy. I tried a dozen examples found in various places and none even looked like the code you profer.

Mate I hope you are OK to get into this with me, if you find yourself getting mad just use the special code words 'talk to the cat' and I will stop and go.

Of the examples I found this was the simplest and the one I had the most hope for -


USE [HD]
GO
/****** Object:  StoredProcedure [dbo].[LowestUIN]    Script Date: 05/01/2008 01:20:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LowestUIN] 
(
@IntNum int OUTPUT
) 
AS
SELECT @IntNum = MIN(MainUIN) FROM Main

All of the fields mentioned in the sp exist and a guy (sorry but I do not remember who) said it was OK but it never worked.

Now I am not being argumentative (you give all that up at 50 believe me) but a scalar query finds the answer very easily and with a tiny query (and the sp seems to be written in Polish). I guessed, rightly or wrongly, that before sp's I needed to learn SQL and so I have concentrated on that but maybe now is right for me to learn sp's.

You have no idea as to how grateful I am for your help. I run a small consultancy company and have already written and sold my first SQL app but I have two more waiting so if sp's are going to help me develop these then I need to get up to speed.

Wanna fly over and coach me for a couple of days, I live within spitting distance of Luton airport. Now I wake up and ask humbly, can you point me toward a good tut for sp's? I mean one in English and not Romanian of course.

One last question and I will go and play with sp's again ... what is the best way to call this sp from vb.net (your sp not mine!!)? Yes I do think I know how to call it but I have never gotten them to return anything and so I will check everything if thats OK.

Thanks Tara (is it OK to call you Tara?)

DarkConsultant
Starting Member

17 Posts

Posted - 2008-04-30 : 20:44:28

Jenda,

Thanks for the reply but it was never my intent to use varchar. I only stayed with it due to the odd problem.

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-01 : 09:51:05

DarkConsultant, you were given a lousy example for a stored procedure. That is a special type of procedure called an Output procedure, which not only has to be called from the application using a special syntax, but which also is seldom used these days because the User Defined Functions are better suited to the purpose.

Tara's example is much more straight forward. To turn a select statement into a sproc, just throw a Create Procedure statement in front of it (for SQL Server, at least. Oracle is much more complex).

Here is how you would extend it to use a parameter:


CREATE PROC usp_SomeProc (@ColumnValue int)
AS

SELECT	Column1,
	Column2
FROM	Table1
WHERE	Column3 = @ColumnValue
GO

Here is how you would call it:


exec usp_SomeProc 1

Not so difficult.

e4 d5 xd5 Nf6

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 12:40:12

DarkConsultant,

Yes you can call me Tara.

You didn't mention what didn't work in the stored procedure that you tried. What you've posted would work fine. Perhaps you had an issue with how you called it in your application.

Two of the most important reasons to use stored procedures are performance and security. The performance topic can get a little debated but the security option shouldn't. With stored procedures, you don't have to grant table access to the user(s). Instead you simply grant EXEC on each of the stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-01 : 14:56:29

quote:
Originally posted by Jenda

It's much more expensive to store and compare a varchar(10) than a bit or tinyint (yes, in this case a bit is enough, 1=Sold, 0=Not Sold, Null stays).

By the way, Jenda, I must strongly disagree with you on this. I don't have any problem using surrogate keys, but NULL should not be used to represent a value. It is, literally, "unknown", and in your example you are using it to represent a known value "stays". Leaving no way to differentiate between missing data and one of the valid data states.
So use a smallint.

e4 d5 xd5 Nf6

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-01 : 15:14:04

Or use a tinyint, which is going to use the same amount of storage as a bit anyway.

An infinite universe is the ultimate cartesian product.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-02 : 04:43:45

quote:
Originally posted by blindman

quote:
Originally posted by Jenda

It's much more expensive to store and compare a varchar(10) than a bit or tinyint (yes, in this case a bit is enough, 1=Sold, 0=Not Sold, Null stays).

By the way, Jenda, I must strongly disagree with you on this. I don't have any problem using surrogate keys, but NULL should not be used to represent a value. It is, literally, "unknown", and in your example you are using it to represent a known value "stays". Leaving no way to differentiate between missing data and one of the valid data states.
So use a smallint.

e4 d5 xd5 Nf6

Stays wasn't a value mentioned by the OP, so I'm guessing Jenda just meant that null can stay as it is - as null. So I'm not sure there's any disagreement to be had...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources