Database

returning table from a stored procedure

25-Mar-05 23:56

Thank u in andvance.

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

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

Re: returning table from a stored procedure

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

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

Re: Is Insert, Delete, Update already had transaction

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

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

Re: Is Insert, Delete, Update already had transaction

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

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

Re: Is Insert, Delete, Update already had transaction

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

turbochimp25-Mar-05 20:30

turbochimp

25-Mar-05 20:30

There are other potential transaction configurations, depending upon the database platform and the method of access.

SQL Server runs, by default, in AutoCommit mode although it can be disabled, requiring explicit COMMIT or ROLLBACK calls.

Oracle does not run in AutoCommit mode by default (in fact I have never determined if it can; I doubt it).

Of course, neither of the above comments really matter if the developer is using a managed data provider, all of which (along with the underlying ODBC and OleDB layers) implement some form of AutoCommit heuristics as far as I know, forcing the behavior to act as you describe.

I'm mainly making the distinction between exec'ing SQL at the command line versus through a managed provider, on different DB servers.

Have a good weekend.

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...’

T-SQL Question

Mickey_WWW24-Mar-05 19:09

Mickey_WWW

24-Mar-05 19:09

Question :

i have this table :

T

F X
_________________

F1 X1
F1 X2
F2 X6
F3 X3
. .
. .
. .
. .
Fn Xm

when i want Fs that their X is 'Xi OR Xj OR Xk' i will write

SELECT F FROM TABLE WHERE X IN( Xi , Xj , Xk )

when i want Fs that their X is 'Xi AND Xj AND Xk ' what should i write ?

_J_25-Mar-05 1:56

25-Mar-05 1:56

Where X = Xi AND
X = Xj AND
X = Xk

To study, study and only to study

Tony Lewis25-Mar-05 10:00

Tony Lewis

25-Mar-05 10:00

This will actually return nothing, since a single column cannot simultaneously be equal to three different values.

Try this:

Select F, count(distinct x)
from T
where X in (Xi, Xj, Xk)
group by F
having count(distinct x) = 3

Since we are only counting distinct values, and there are three distinct possiblities, a count of 3 means that all are present.

Tony Lewis
XAKTsoft, Inc.

WDI26-Mar-05 0:05

WDI

26-Mar-05 0:05

Thanks
i have use your statement in my article but i got an error with below statement:

Select distinct ID,Name from [View]
where ID not in ( select ID from [View] where TypeID not in(1,3) )
group by ID having count(distinct typeID) = 2

ADO Error :Column [View].Name is invalid in the select list because it is not contained

in either an aggregate function or GROUP BY clause.

Tony Lewis26-Mar-05 2:05

Tony Lewis

26-Mar-05 2:05

It means just what it says. Any column in the summary query (with a group by clause and aggregate columns like sum, count, etc.) that isn't summarized needs to be included in the group by clause. You need to add [Name] to your group by. I always put the aggregate columns at the end, copy the rest of the selected fields and paste them to the group by. Remember that the group by can't contain field aliases.

Tony Lewis
XAKTsoft, Inc.

turbochimp25-Mar-05 18:12

turbochimp

25-Mar-05 18:12

This should work, although I don't know if it's the best way to do it:

SELECT <br />
    F<br />
FROM<br />
    T T0<br />
WHERE<br />
    EXISTS (SELECT 1 FROM T T1 WHERE T1.F = T0.F AND X = Xi)<br />
    AND<br />
    EXISTS (SELECT 1 FROM T T2 WHERE T2.F = T0.F AND X = Xj)<br />
    AND<br />
    EXISTS (SELECT 1 FROM T T3 WHERE T3.F = T0.F AND X = Xk)

Of course, all the aliases are arbitrary, just make it easier for me to read.

Hope this helps.

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...’

Handle SQL errors with severity 10

Suman Singh29-Mar-05 11:38

Suman Singh

29-Mar-05 11:38

Plesae try

SELECT F FROM TABLE WHERE X all( Xi , Xj , Xk )

You can read about all, in and any

_J_23-Mar-05 9:05

23-Mar-05 9:05

how to handle sql errors with severity less then 10 in C#

.... From russia with love ....

Chris Meech23-Mar-05 9:14

Chris Meech

23-Mar-05 9:14

Here's a link[^] that might help.

Chris Meech

I am Canadian. [heard in a local bar]

Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]

Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]

_J_23-Mar-05 9:29

23-Mar-05 9:29

thanks my canadian bra, i will follow the link

.... From russia with love ....

Chris Meech23-Mar-05 9:54

Chris Meech

23-Mar-05 9:54

frmRussia wrote:
thanks my canadian bra,

I'll assume that should be 'bro' and say 'Glad I could help'. Smile | :)

Chris Meech

I am Canadian. [heard in a local bar]

Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]

Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]

_J_23-Mar-05 10:07

23-Mar-05 10:07

ok bro. my sql server knowledge is like c# knowdlege --> like english , understand Laugh | :laugh:

.

I wrote the link u advise me, but don't really know how to use it my app, if i write in the catch block:
catch(System.Data.SqlClient.SqlError err)
{....}
i got compilation error, "The type caught or thrown must be derived from System.Exception"

.... From russia with love ....

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

Colin Angus Mackay23-Mar-05 13:03

Colin Angus Mackay

23-Mar-05 13:03

I think that would be

catch(SqlException se)
{
}

If there is only one error then the exception object will contain the details. If there is more than one error the exception object will contatin the details of only the first error. You then have to look in the errors collection for the other errors.

catch(SqlException se)
{
    foreach(SqlError error in se.Errors)
    {
        // Do something with each error
    }
}

Does this help?

_J_23-Mar-05 21:31