SQL problem ? - Database Discussion Boards

Michael Potter25-Mar-05 8:24

25-Mar-05 8:24

If you are looking for all IDs that have a record with a TypeID of i and j and k and at least one other TypeID then I think this would work:

SELECT
    DISTINCT a.ID
FROM
    T AS a
INNER JOIN 
    T AS b
    ON (a.ID = b.ID)
INNER JOIN
    T AS c
    ON (a.ID = c.ID)
INNER JOIN
    T AS d
    ON (a.ID = d.ID)
WHERE
    a.TypeID = i,
    b.TypeID = j,
    c.TypeID = k,
    d.TypeId NOT IN (i,j,k)

Re: Simplify a T-SQL

WDI25-Mar-05 9:10

WDI

25-Mar-05 9:10

There is an error in your statement.

Are u sure this statement has a better performance?

Any Idea?

Re: Simplify a T-SQL

Michael Potter25-Mar-05 11:34

Michael Potter

25-Mar-05 11:34

The error might be in the engine - I use MS SQL 2000 - or it may be that there is typo since I didn't create any test tables.

Only examining the execution plan between the two queries and testing will tell you which one is faster. Indexes also help.

My experience is that IN (or NOT IN) statments tend to generate more table scans whereas JOIN makes use of indexes better. Given your set of criteria, an index on [TypeID, ID] could generate a faster query.

Re: Simplify a T-SQL

Tony Lewis25-Mar-05 9:41

Tony Lewis

25-Mar-05 9:41

Try this:

select ID from
(Select ID, sum(case when TypeID in ('I', 'J', 'K', 'L', 'M') then 1 else 0 end) as InList,
sum(case when TypeID in ('I', 'J', 'K', 'L', 'M') then 0 else 1 end) as NotInList
from t
group by ID) as foo
where inlist > 0 and NotInList = 0

Tony Lewis
SQL "Guru"
XAKTsoft, Inc.

Re: Simplify a T-SQL

andyharman28-Mar-05 4:32

andyharman

28-Mar-05 4:32

I used the following method a few years back:

SELECT ID FROM T
WHERE TypeId IN (i, j, k)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3

Where "3" is the number of terms in the "IN" clause. To handle your "NOT IN" requirement, you could use the following:

SELECT ID FROM T
WHERE ID IN (SELECT ID FROM T
    WHERE TypeId IN (i, j, k)
    GROUP BY ID
    HAVING COUNT(DISTINCT TypeId) = 3)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3

I don't know how fast this will run against your chosen database engine. Under SQL-Server, I would normally expect the following indexes to help:

Clustered index on T (ID, TypeId)

And possibly:

Nonclustered index on T (TypeId)

Hope this helps.

Regards
Andy

DateTime

vuthaianh25-Mar-05 5:24

vuthaianh

25-Mar-05 5:24

i just want to save time in a field got type is datetime, but every time i insert time into the field, SQL SERVER automatically add day to that field.
Any way to solve this problem? Thanks in advance

Re: DateTime

Colin Angus Mackay25-Mar-05 6:29

Colin Angus Mackay

25-Mar-05 6:29

There is no specific time column type, only smalldatetime or datetime. Just ignore the date part.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

SQL problem ?

Guinness4Strength25-Mar-05 4:28

Guinness4Strength

25-Mar-05 4:28

I have a document storage system written in C# that has been running fine for over a year on a SQL 2000 database. I have a table that holds three BLOB field which contain files. The problem I'm having is, that this morning the files can no longer be retrieved from the table's BLOB field(s). When I query a record in the Query Analyzer the BLOB I'm attempting to get shows a value of 0x where the other field show values of 0x123423dj3ed24aq2 etc......anyone see this before ? Any ideas why SQL is not longer storing this BLOB field correctly ?

Multiple insert in single roundtrip with Sqlcommand, Possible???

abcxyz8225-Mar-05 4:11

abcxyz82

25-Mar-05 4:11

My application has one web form where admin can add as many users as desired and at the end when he press SaveChanges I want to add all of them to database, There should be some way to pass arrays to stored procedure..I am looking for it or something similar to this....

Any Ideas!!!

Thanks in advance...

MaulikCE

Re: Multiple insert in single roundtrip with Sqlcommand, Possible???

ToddHileHoffer25-Mar-05 4:35

ToddHileHoffer

25-Mar-05 4:35

No, you can't pass an array. What you should do is open your connection and loop through array of users. Set the new parameter value and execute. Why would you need to pass an array?

"People who never make mistakes, never do anything."

My blog
http://toddsnotsoamazinglife.blogspot.com/

Re: Multiple insert in single roundtrip with Sqlcommand, Possible???

Colin Angus Mackay25-Mar-05 6:32

Colin Angus Mackay

25-Mar-05 6:32

ToddHileHoffer wrote:
Why would you need to pass an array?

I've had many occasions where I want to pass an array into a stored procedure so that it can perform all the actions in one pass rather than take serveral calls to the server.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Multiple insert in single roundtrip with Sqlcommand, Possible???

_J_25-Mar-05 7:19

_J_

25-Mar-05 7:19

look here:

http://www.sommarskog.se/arrays-in-sql.html[^]

------------------------------------
To study, study and only to study

Re: Multiple insert in single roundtrip with Sqlcommand, Possible???

_J_25-Mar-05 11:19

_J_

25-Mar-05 11:19

Another idea is :

To call the Update method on a SQLDataAdapter, passiing it a modified dataset containing new, changed and or deleted rows.

------------------------------------
To study, study and only to study

How to ROLLBACK TRANSACTION on client level

_J_25-Mar-05 3:32

_J_

25-Mar-05 3:32

Suppose i connect to db (SQL Server) from the client (.NET) and call to some store procedure, in this sp i start transaction (BEGIN TRANSACTION) and before COMMIT or RALLBACK an error happen that imidietly stop the execution of stored procedure, In the client i cacth this error but what about an open transaction ???

HOW to rollback in the client???

------------------------------------
To study, study and only to study

Re: How to ROLLBACK TRANSACTION on client level

Luis Alonso Ramos25-Mar-05 12:21

Luis Alonso Ramos

25-Mar-05 12:21

I am not an expert in SQL by any means, and so I don't know if it is possible to do what you want. But if you could start you transaction on the client side, then you could undo everything. See SqlTransaction class.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: How to ROLLBACK TRANSACTION on client level

turbochimp25-Mar-05 18:02

turbochimp

25-Mar-05 18:02

Short answer: try SET XACT_ABORT ON...

"If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.<br />
<br />
It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs."

-- SQL Server Books Online

A better answer would be to include error handling. There's a very good article
here[^] that describes one strategy for getting more predictable results from stored procedures through error handling.

The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

Re: How to ROLLBACK TRANSACTION on client level

_J_25-Mar-05 23:56

_J_

25-Mar-05 23:56

Thank u in andvance.

------------------------------------
To study, study and only to study

returning table from a stored procedure

balkang25-Mar-05 2:52

balkang

25-Mar-05 2:52

Hello,
I want to get all of the rows of a table in my database which are suit some criteria in a stored procedure.

but ı dont know getting and returning table (multiple data) from a stored procedure.
How can ı success this..

Re: returning table from a stored procedure

_J_25-Mar-05 3:00

_J_

25-Mar-05 3:00

CREATE PROCEDURE procName

@criteria1 int,
@criteria2 char ,
........
......

SELECT bla, bla bla... FROM tableName
WHERE bla = @criteria1 And .....

or using og JOIN.

something like that.

is it help?

------------------------------------
To study, study and only to study

Re: returning table from a stored procedure

balkang25-Mar-05 23:25

balkang

25-Mar-05 23:25

thanks very much.

Is Insert, Delete, Update already have transaction

_J_25-Mar-05 1:59

_J_

25-Mar-05 1:59

Is Insert, Delete, Update already have transaction inside
if yes how it help us and for what it ??

To study, study and only to study

Re: Is Insert, Delete, Update already had transaction

Colin Angus Mackay25-Mar-05 2:07

Colin Angus Mackay

25-Mar-05 2:07

_J_ wrote:
Is Insert, Delete, Update already had transaction inside

I'm not entirely sure what you are asking, however I am guessing you are asking: Are INSERT, DELETE and UPDATE statements wrapped in a transaction?

Yes, and the SELECT statement too unless you wrap a sequence of these statements in a transaction. SQL Server will always wrap indiivdual statements in a transaction so that the statement can complete successfully and does not interfer with other queries or so other queries do not interfere with it.

Does this help?

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Is Insert, Delete, Update already had transaction

_J_25-Mar-05 2:12

_J_

25-Mar-05 2:12

OK, thanks. So if i do deletion and 100% shure that more than one row will deleted should use a transaction as i now understood No.

So i don't need to wrap it with my tranascation like :

Begin TRAN
DELETE .....
COMMIT

To study, study and only to study

Re: Is Insert, Delete, Update already had transaction

Colin Angus Mackay25-Mar-05 2:25

Colin Angus Mackay

25-Mar-05 2:25

I'm not sure that you understood me.

If you are doing one delete statement only then you don't need to explicitly wrap it in a transaction because SQL Server will do that for you. (NOTE: Other database products may vary)

So DELETE MyTable WHERE SomeColumn='SomeValue' does not have to be wrapped in a transaction. It can delete 0, 1 or many rows.

However, if you need to delete from, say, two separate tables at the same time you might want to use a transaction, so

BEGIN TRANSACTION
DELETE MyTable WHERE SomeColumn='SomeValue'
DELETE MyOtherTable WHERE SomeOtherColumn='SomeOtherValue'
COMMIT TRANSACTION

Obviously, after each delete you may want to check for errors so that you can roll back a transaction if it fails.

Does this help?

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Is Insert, Delete, Update already had transaction

_J_25-Mar-05 2:38

_J_

25-Mar-05 2:38

Thank u, u r the best. Now everything clear me, Thanks in advance

I have another question to about transaction.
Transacttion in stoe proc , in the begining i save @@TRANCOUNT to local variable (@tranCount) after this i start transaction (BEGIN TRANSACTION), i do my work ... .. maybe call to other store procs, .. .. and now before COMMIT or ROLLBACK i must to check my local variable (@tranCount) with @@TRANCOUNT ??

------------------------------------
To study, study and only to study

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.