Return Duplicate Rows

I want to select all my columns that have the same number twice....but i dont know how....I tried doit it this way

Select Distinct sti.parent_topic_id, st.description_short, sti.sort_order, count(st.description_short)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, st.description_short, sti.sort_order

--Having count(sti.sort_order) > 1

Order By parent_topic_id

I even tried sti.sort_order = sti.sort_order and that does not work eithier

But it returns everything...when i uncomment the having clause it does not return anything....I want my end result to be

2 TheName 10 1

2 SomthingElse 10 1

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

select *
from sam_topic_items sti

inner join sam_topic st

on sti.topic_id = st.id
inner join
(
select sti.topic_id
from sam_topic_items sti
inner join sam_topic st
on sti.topic_id = st.id
where st.type = 'Topic'
group by sti.topic_id
having count(*) > 1
) d
on st.topic_id = d.topic_id|||

Thanks 4 the response but this returns all my rows with the sam ID's instead of the same sort order...I have a table like this

id sort_order

1 2

1 2

1 1

1 3

I only want to return.......

id sort_order

1 2

1 2

I thought if i did it like you had it but tweak it a little it would work....but it still returns all the rows

select *

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

inner join

(

select sti.parent_topic_id, sti.sort_order

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

where st.type = 'Topic'

group by sti.parent_topic_id, sti.sort_order

having count(*) > 1

) d

on sti.sort_order = d.sort_order

|||

I think you need to join by both sort_order and topic_id.

A more basic question is if you know where the duplication is coming from.

Are there multiple records in the topic table (of type 'topic') which have the same ID?|||There are multiple items in the topic_item tables that have the same number in thier sort order...and I only want to select those id's that have the same number in there sort_order....my topic table has a PK of id and my topic_items table has a PK of topic ID and a PK of parent_topic_id.....|||

When I say primary key I mean a column (or columns) which uniquely identify a single record in the table. That's means there cannot be duplicates in the primary key. I am interested in topic_id vs. parent_topic_id are these the same field or related fields. They look like foreign keys (fields which link to a primary key on another table).

The main issue in your query seems to be multiple records in the topic_items table with the same topic_ID (or parent_topic_ID I'm not sure) and the same sort order.

If this is the case then:

SELECT topic_ID, sort_order
FROM topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1

should give a list of these records (but only 1 instance of each combination). Then joining it back to the other tables should give the record list you want.

SELECT ST.ID, STI.description_short, STI.sort_order
FROM (
SELECT topic_ID, sort_order
FROM sam_topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1
) TS
INNER JOIN sam_topic_items STI
ON (TS.topic_ID = STI.topic_ID) AND
(TS.sort_order = STI.sort_order)
INNER JOIN sam_topic ST
ON (STI.topic_id = ST.id)
WHERE (ST.type = 'Topic')
ORDER BY ST.ID

This is all testing by topic_id. If it should be parent_topic_id then change it to this throughout the query.

There is also still the question of what the type field does in selecting records (as I say do you have records other than type='Topic' in the sam_topic table?). Depending upon exactly what this does it may cause problems with the SQL to pick up the duplicates.

|||

Yes there is other records other than "Topic" there is also "ROOT" but I am just worrying about "Topic" and nothing else....This is how I had to change the query it works now partially

SELECT STI.parent_topic_id, ST.description_short, STI.sort_order

FROM (

SELECT parent_topic_id, sort_order

FROM sam_topic_items

GROUP BY parent_topic_id, sort_order

HAVING count(sort_order) > 1

) TS

INNER JOIN sam_topic_items STI

ON (TS.sort_order = STI.sort_order)

INNER JOIN sam_topic ST

ON (STI.topic_id = ST.id)

WHERE (ST.type = 'Topic')

ORDER BY STI.parent_topic_id

but it still does not return only the rows with duplicate sort order for a particular parent_topic_id this query returns

id description_short sort_order

2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1.....thanks 4 the help though!!!

See how it returns all these results for the same thing?...in my table there is only one "<1mm" for the parent_topic_id of 2131 but this returns like 20 of them...I dont know how it did that?

|||

Change your having clause to:

HAVING count(*) >1

|||Yea i did that I still get the same result though?|||

Yeah I get it. I didn't see the whole thing.

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

You need to take the description name out of the group by, otherwise you will count the values with the same id but different descriptions as different rows. To count the number of occurences of a different description for the same id you need to take the description out of your group.

|||Ok here is a better question this query

Select Distinct sti.parent_topic_id, st.Type, sti.sort_order, count(sti.sort_order)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, sti.sort_order, st.Type

Having count(sti.sort_order) > 1

Order By parent_topic_id

Returns this data exactly

parent_topic_id type sort_order the last column is the number of occurences that the sort_order has.....like if you look at the first row of data.....there are 3 occurences of 0 for parent_topic_id 2334...

2334 TOPIC 0 3
2398 TOPIC 32 2
2399 TOPIC 16 2
2430 TOPIC 22 2
2447 TOPIC 26 2
2447 TOPIC 12 2
2447 TOPIC 15 2
2452 TOPIC 1 3
2505 TOPIC 37 2
2505 TOPIC 52 2
2505 TOPIC 3 2
2507 TOPIC 6 2
2526 TOPIC 32 2
2549 TOPIC 54 2
2549 TOPIC 52 2
2562 TOPIC 0 2
2722 TOPIC 2 2
2880 TOPIC 0 2
2995 TOPIC 0 2
2997 TOPIC 0 2
3000 TOPIC 0 2
3001 TOPIC 0 2
3040 TOPIC 6 2
3152 TOPIC 26 2
3244 TOPIC 0 3
3524 TOPIC 0 2
3605 TOPIC 0 2
3638 TOPIC 0 2
3721 TOPIC 2 2
3723 TOPIC 1 2
4082 TOPIC 4 2
4100 TOPIC 1 3
4181 TOPIC 11 2
4220 TOPIC 0 2
4261 TOPIC 2 2
4262 TOPIC 1 2
4302 TOPIC 3 2
4302 TOPIC 1 2
4302 TOPIC 2 2
4439 TOPIC 0 2
5029 TOPIC 2 3
5032 TOPIC 2 4
5042 TOPIC 2 6
5042 TOPIC 11 2
5071 TOPIC 2 3
5297 TOPIC 2 2
5297 TOPIC 3 2
5297 TOPIC 1 2
5368 TOPIC 52 2
5368 TOPIC 3 2
5368 TOPIC 36 2
5368 TOPIC 48 2
5368 TOPIC 37 2
5368 TOPIC 54 2
5849 TOPIC 45 2
5882 TOPIC 16 2
5882 TOPIC 42 2
5882 TOPIC 54 2
5882 TOPIC 11 2
5882 TOPIC 55 3
5882 TOPIC 60 2
5882 TOPIC 40 2
6032 TOPIC 0 3
6137 TOPIC 0 2
8897 TOPIC 3 2
9461 TOPIC 2 2
9461 TOPIC 1 2
9598 TOPIC 11 2
9608 TOPIC 54 2
10345 TOPIC 0 2
10366 TOPIC 2 2
10627 TOPIC 17 2
11992 TOPIC 42 2
12081 TOPIC 4 2
12083 TOPIC 3 3
12298 TOPIC 20 2
12701 TOPIC 0 2
12716 TOPIC 9 4
12717 TOPIC 4 2
12750 TOPIC 6 2
12755 TOPIC 3 2
12760 TOPIC 1 2
12760 TOPIC 3 2
12760 TOPIC 4 2
12760 TOPIC 2 2
12926 TOPIC 5 3
12990 TOPIC 8 6
12998 TOPIC 9 4
12998 TOPIC 6 2
13001 TOPIC 2 2
13069 TOPIC 5 2
13069 TOPIC 1 2
13132 TOPIC 8 2
13134 TOPIC 4 2
13349 TOPIC 5 3
13429 TOPIC 4 2
13429 TOPIC 19 3
13429 TOPIC 1 2
13429 TOPIC 3 2
13429 TOPIC 6 2

Now using the above query how would i write a sub query to use the data return in this result to show some other data in the same result set?

|||

Like what? I thought thats what you wanted to show?

Your result set is going to be grouped.

It will only have a single row for each group.

You can do simple tricks like putting an aggregate function like MIN function to show things like the description but you will only show a single description for the grouped row.

What is it exactly that you are trying to show?

Have you considered an sp rather than a single query?

ALSO, please not that you DO NOT need st.Type in the grouping or the field list since your where criteria clearly states that it has to be equal to 'TOPIC'.

|||

thanks liz.....

If you look at the results for the query....and look at the id of 2334 it has 3 instances of 0 in that id....I want to be able to show

2334 TheNAme 0

2334 The OtherName 0

2334 The OtherName 0

I want to show the id the description and the sort order.....Only for those duplicate sort orders and thats it.....I could write a vb or C# app to do it....but i need to learn sql, haha as you can see i dont know it well!!! thanks!!

|||

It is real ugly but, if you are using 2 values you need to convert them to a char string and use them in the where clause together in the outer query...

Select sti.parent_topic_id, st.description_short, sti.sort_order
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order) IN
(
Select convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order)
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where st.type = 'Topic'
Group By sti.parent_topic_id, sti.sort_order, st.Type
Having count(sti.sort_order) > 1
Order By parent_topic_id
)

I don't know if this will execute properly because I don't have the right tables to test it but it is the general idea...

|||

First off please explain to me what the convert method did? I need to know what that did to the query to make it act like so....

BUT you are a WIZ!!!!!!!!! unless I am just that mentally slow THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!......

return duplicate records

Hello experts,

I'm trying the run the following query with specific intentions.

I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.

I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?

Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);

alexAlex,

There are a few solutions. Two are (might have typos, but you should be
able to get the idea):

select Employee_last_name, Employee_first_name
from tbl_employee
join (
select '009' as id union all
select '008' as id union all
select '007' as id union all
select '007' as id union all
select '006' as id
) as IDs
on IDs.id = tbl_employee.employee_id

or to make the specification of ids simpler:

declare @.ids varchar(1000)
set @.ids = '009008007007006'
declare @.idlength int
set @.idlength = 3

select Employee_last_name, Employee_first_name
from tbl_employee
join a_permanent_table_of_integers_from_0_to_whatever as Nums
on employee_id = substring(@.ids,@.idlength*n+1,@.idlength)
and n < len(@.ids)/@.idlength
-- [n] is the column name for the permanent table and should
-- be that tables primary key

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
alex wrote:

Quote:

Originally Posted by

Hello experts,
>
I'm trying the run the following query with specific intentions.
>
I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.
>
I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?
>
Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
>
alex
>

|||>I would like the query to return 5 results; i.e., 4 distinct and one duplicate. <<

The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:

SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>I would like the query to return 5 results; i.e., 4 distinct and one

duplicate. <<

Quote:

Originally Posted by

>
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
>
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'

Joe, I thought you knew SQL? This query will not return the results
that Alex was asking for.

Why is left as an exercise to the reader.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Return Distinct Values from Stored Procedures

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

Is OrderNum contained in the result set returned by the sp rptOpenOrderLines? If yes, you may need something likeDataView.RowFilter:

http://msdn2.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx

If no, there seems to be no way to do this without checking back in database.

|||Great! Thanks lori_Jay! Although I don't yet have the exact solution, this looks like a good lead toward the final product. That article is pretty useful and I appreciate the help. I've spent days on developing these datalist/gridview controls and its about time that it should be done. I'll post back with the solution when I'm complete with this part.|||

Please See:

http://forums.asp.net/thread/1371660.aspx

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>
You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>
|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,[vbcol=seagreen]
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client[vbcol=seagreen]
it
>
>
|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>
|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with[vbcol=seagreen]
mode.[vbcol=seagreen]
works[vbcol=seagreen]
If
>
sql

Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client
it
>
>|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with
mode.
works
If
>

Return Different Fields Based On Expression

Hello Out There,
I have a dataset set up to return a set of billing information. I have this
information displayed in a matrix. However I have requirement where;
a) If the account type = ODC then return the vendor_name field
b) If the account type = ODC and venor_name field is null then return the
employee_name field
c) In any other case return the employee_name field.
Is there a way to set up an expression in a text box on the detail line of a
matrix to reflect the info needed?
There's a little more to be desired with my expression syntax skills so any
help would be greatly appreciated.
ThanksYou should do this in the SQL and return it in a single column.
In SQL Server,
NameField = CASE
WHEN accountType = ODC
CASE
WHEN vendor_name is null then employee_name
ELSE vendor_name
END
else
EMPLOYEE_NAME
END
-tIM
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:2ADF13EE-A421-4F4A-8E5E-A47C1D55D89B@.microsoft.com...
> Hello Out There,
> I have a dataset set up to return a set of billing information. I have
> this
> information displayed in a matrix. However I have requirement where;
> a) If the account type = ODC then return the vendor_name field
> b) If the account type = ODC and venor_name field is null then return the
> employee_name field
> c) In any other case return the employee_name field.
> Is there a way to set up an expression in a text box on the detail line of
> a
> matrix to reflect the info needed?
> There's a little more to be desired with my expression syntax skills so
> any
> help would be greatly appreciated.
> Thanks|||I often don't see the forest through the trees. Thanks for the quick help.
JD
"Tim Dot NoSpam" wrote:
> You should do this in the SQL and return it in a single column.
> In SQL Server,
> NameField = CASE
> WHEN accountType = ODC
> CASE
> WHEN vendor_name is null then employee_name
> ELSE vendor_name
> END
> else
> EMPLOYEE_NAME
> END
> -tIM
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:2ADF13EE-A421-4F4A-8E5E-A47C1D55D89B@.microsoft.com...
> > Hello Out There,
> >
> > I have a dataset set up to return a set of billing information. I have
> > this
> > information displayed in a matrix. However I have requirement where;
> >
> > a) If the account type = ODC then return the vendor_name field
> > b) If the account type = ODC and venor_name field is null then return the
> > employee_name field
> > c) In any other case return the employee_name field.
> >
> > Is there a way to set up an expression in a text box on the detail line of
> > a
> > matrix to reflect the info needed?
> >
> > There's a little more to be desired with my expression syntax skills so
> > any
> > help would be greatly appreciated.
> >
> > Thanks
>
>

return db names on server

Hello,

Does anyone know how to return the name of all the databases except the system databases on a server for SQL Server 2005?

Thank you

select * from sys.databases where database_id not in (1, 2, 3, 4)

|||Thank you!

Return datetime type variable from SP

How can I return a datetime type variable from a stored procedure in SQL Server to C# code?

Hello,

I don't know if I got it right, but to return anything from a Stored procedure just make something like:

Select client_datecreated from clients

if you need a date put it as a field then in C# execute the command and use the sqldatareader class:

check msdnhere

The command can be a Stored as well as a query.

|||Do i need to set an output parameter in the SP? The syntax in the SP is confusing me.|||

select convert(char(10),fieldname in table,101) as test_Date from tablename

the 101 is a code which gives the date in mm/dd/yyyy format.

You should check with 'books online" in your SQL server help section.that gives you a list of different format you may want your date to be in.

convert basicly is truncating your date to have 10 characters otherwise you will have the hours:minute:seconds too in your result.

|||

If you would like to get data as return parameter (not return Value which is always int) you have to define it as OUTPUT in stored procedure definition, and also you have to setup this parameter as output in your SQLcommand object parameters definition.

If you would like to return it as cell in result table you do not have to define parameter and you can just do select yourdatafied from yourtable at the end of your stored procedure.

But SQL Command with output parameter is more elegant solution and will work a little faster ( .net do not have to create table structure for returned data)
and you can use executeNonQuery instead of execute scalar or execute reader.

See VB or C# help for syntax how to do this if you will have problems post again, but help is very good in VS so you should be good.

Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.
Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:

> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.
sql

Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October
.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
[vbcol=seagreen]
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like t
o
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders wit
h
> an OrderDate recorded on the 5 October then the query would return 5 October.[/vbc
ol]

Return Dates Not in Table

Just wondering if anyone could help with returning dates not in a table eg
Orders table has a number of Orders with OrderDate stored. I would like to
write a query that displays all dates between say 01 October 2005 and 30
October 2005 that did not have a OrderDate, ie if there were no Orders with
an OrderDate recorded on the 5 October then the query would return 5 October.Tina
Below is one way you could achieve this using a Calender Reference Table:
CREATE TABLE orders
(
OrderID INT,
OrderDate SMALLDATETIME
)
INSERT orders SELECT 1, '1 Oct 2005'
INSERT orders SELECT 2, '2 Oct 2005'
INSERT orders SELECT 3, '3 Oct 2005'
INSERT orders SELECT 4, '4 Oct 2005'
-- Create Calender Reference Table
CREATE TABLE calender
(
CalenderDate SMALLDATETIME
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '1 Jan 2005'
WHILE @.dt < DATEADD(YEAR, 1, '1 Jan 2005')
BEGIN
INSERT calender SELECT @.dt
SET @.dt = DATEADD(DAY, 1, @.dt)
END
SELECT calenderdate
FROM Calender
WHERE calenderdate BETWEEN '1 Oct 2005' AND '31 Oct 2005'
AND NOT EXISTS (SELECT 1 FROM orders WHERE OrderDate = calenderdate)
- Peter Ward
WARDY IT Solutions
"Tina" wrote:
> Just wondering if anyone could help with returning dates not in a table eg
> Orders table has a number of Orders with OrderDate stored. I would like to
> write a query that displays all dates between say 01 October 2005 and 30
> October 2005 that did not have a OrderDate, ie if there were no Orders with
> an OrderDate recorded on the 5 October then the query would return 5 October.

Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

Return dataset in one column

Hi there

I have the following two tables

mainprofile (profile varchar(20), description)
accprofile (profile varchar(20), acct_type int)

Sample data could be

mainprofile
------
prof1 | profile one
prof2 | profile two
prof3 | profile three

accprofile
-----

prof1 | 0
prof1 | 1
prof1 | 2
prof2 | 0

Now doing a join between these two tables would return multiple rows,
but I would like to know whether it would be possible to return
acct_type horizontally in a column of the result set, e.g.

prof1 | profile one | [0,1,2]
prof2 | profile two | [0]

I could probably manage this with cursors, but it would be very
resource intensive. Is there a better way?

Regards,
LouisFor a one time data display or if this is used by a single application or a
report, you should consider retrieving the resultset to the client side,
leverage the display/presentation language's string manipulative features
and appropriately format the data there.

If this is more of a general requirement and used by several applications,
in certain cases it may make some sense to do it at the server using t-SQL.
For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx
--
Anith|||

Quote:

Originally Posted by

For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx

Thanks. In the end I decided to stick with using a CURSOR

Regards,
Louis

return data from two tables

Hi,
hope im posting in right group...
Im trying to return a value from 1 of two tables depending on the value in
one table.
I.e. TableA has an action_id and action_type fields. Depending on if
action_type = S, return the Desc field from TableB, where action_id =
Desc_ID. if action_type = L return the Desc field from TableC, where
action_id = Desc_ID
i.e.
select a.Type_id, (either b.Desc or c.Desc)
from TableA a, TableB b, TableC c
where 1=1
if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
i just dont know how to get this into an sql query. i can do it VBA, but
that isn't an option.
Any help or direction would be greatly appreciated.
Thanks
Tony Z.select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc
end
from TableA a, TableB b, TableC
where 1=1
"Tony Zappal" wrote:

> Hi,
> hope im posting in right group...
> Im trying to return a value from 1 of two tables depending on the value in
> one table.
> I.e. TableA has an action_id and action_type fields. Depending on if
> action_type = S, return the Desc field from TableB, where action_id =
> Desc_ID. if action_type = L return the Desc field from TableC, where
> action_id = Desc_ID
> i.e.
> select a.Type_id, (either b.Desc or c.Desc)
> from TableA a, TableB b, TableC c
> where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_i
d
> i just dont know how to get this into an sql query. i can do it VBA, but
> that isn't an option.
> Any help or direction would be greatly appreciated.
> Thanks
> Tony Z.|||>select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc endred">
> from TableA a, TableB b, TableC
> where 1=1
Those three tables, listed without any joining, will result in a cross
product result set. If there were 100 rows each, the result set would
have 100*100*100 = 1000000 rows.
Roy|||There are a few ways this could be written. Here is one.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S'
THEN (select B.desc from TableB as B
where A.something = B.something)
WHEN A.action_type = 'L'
THEN (select C.desc from TableC as C
where A.something = C.something)
FROM TableA as A
And another.
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
WHERE A.action_type = 'S'
UNION ALL
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableC as C
ON A.something = C.something
WHERE A.action_type = 'L'
And a third.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S' THEN B.desc
WHEN A.action_type = 'L' THEN C.desc
END
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
JOIN TableC as C
ON A.something = C.something
Roy
On Tue, 21 Feb 2006 18:16:27 -0800, "Tony Zappal"
<TonyZappal@.discussions.microsoft.com> wrote:

>Hi,
>hope im posting in right group...
>Im trying to return a value from 1 of two tables depending on the value in
>one table.
>I.e. TableA has an action_id and action_type fields. Depending on if
>action_type = S, return the Desc field from TableB, where action_id =
>Desc_ID. if action_type = L return the Desc field from TableC, where
>action_id = Desc_ID
>i.e.
>select a.Type_id, (either b.Desc or c.Desc)
>from TableA a, TableB b, TableC c
>where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
>i just dont know how to get this into an sql query. i can do it VBA, but
>that isn't an option.
>Any help or direction would be greatly appreciated.
>Thanks
>Tony Z.|||Thank you John and Roy.
I've got my query returning exactly what i need.
Big thanks.
Tony.sql

Return data from multiple tables

Hi there,

I have tables with such structure

transaction_YYMM
(idx,date,company_id,value)

where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

best regards
RafalI want to define query (maybe view, procedure):

Quote:

Originally Posted by

select * from [?] where date>='2007-01-01' and date<='2007-04-30'

A UNION ALL query will combine multiple result sets:

SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704

You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating a
partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rafa Bielecki" <rafal@.bielecki.infowrote in message
news:f2ubuk$i1o$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

Hi there,
>
I have tables with such structure
>
transaction_YYMM
(idx,date,company_id,value)
>
where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one
>
best regards
Rafal
>

|||Uzytkownik "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netnapisal w
wiadomosci news:JOz4i.22889$JZ3.9830@.newssvr13.news.prodigy.n et...

Quote:

Originally Posted by

A UNION ALL query will combine multiple result sets:
>
SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704
>
You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating
a partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.

thank you Dan, your help is very important to me
Rafal|||Rafa Bielecki (rafal@.bielecki.info) writes:

Quote:

Originally Posted by

I have tables with such structure
>
transaction_YYMM
(idx,date,company_id,value)
>
where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

In additions to Dan's response, I like to point out that from a logical
point of view, the above design is flawd. It's a lot easier to deal with a
single table. If there are enourmous volumes, it can still be motivated
with partitioning, but then we are talking enourmous values like tens
of millions of rows per month.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Return data from a temp table

I have the following stored proc
CREATE PROCEDURE [dbo].[GetPartHistory]
@.PartID int
AS
DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
Step_Description varchar(200), Station_Step_Description varchar(200), Result
varchar(100), Operator_Name varchar(50), Result_Date datetime)
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Step_Result.Result,
Operator.Operator_Name,
Step_Result.Result_Date
FROM
Step_Result
INNER JOIN
Operator ON Step_Result.Operator_# = Operator.Operator_#
INNER JOIN
Station_Step_Data ON Step_Result.Station_Step_Data_# =
Station_Step_Data.Station_Step_Data_#
INNER JOIN
Traveler_Step ON Station_Step_Data.Traveler_Step_# =
Traveler_Step.Traveler_Step_#
INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
WHERE
Step_Result.Product_# = @.PartID
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Weight.Weight,
Operator.Operator_Name,
Weight.Weight_Date
FROM Traveler_Step INNER JOIN
Station_Step_Data ON Traveler_Step.Traveler_Step_# =
Station_Step_Data.Traveler_Step_# INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
INNER JOIN
Weight ON Station_Step_Data.Station_Step_Data_# =
Weight.Station_Step_Data_# INNER JOIN
Operator ON Weight.Operator_# = Operator.Operator_#
WHERE
Weight.Product_# = @.PartID
SELECT Station_Name, Step, Step_Description, Station_Step_Description,
Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
GO
when executed in query analyser it returns the data I am after, but when I
try to access it via ADO I get nothing. What should I do to return data
from the temp table.
John WrightTry adding SET NOCOUNT ON in the beginning of the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int, Step_Descri
ption varchar(200),
> Station_Step_Description varchar(200), Result varchar(100), Operator_Name
varchar(50), Result_Date
> datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# = Traveler_
Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result
, Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_
# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# = Wei
ght.Station_Step_Data_#
> INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description, Res
ult, Operator_Name,
> Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
try to access it via
> ADO I get nothing. What should I do to return data from the temp table.
> John Wright
>|||Yea that did it. I remembered just after I posted the message.
John
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
> Step_Description varchar(200), Station_Step_Description varchar(200),
> Result varchar(100), Operator_Name varchar(50), Result_Date datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
> Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# =
> Traveler_Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# =
> Station.Station_# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# =
> Weight.Station_Step_Data_# INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
> try to access it via ADO I get nothing. What should I do to return data
> from the temp table.
> John Wright
>

return current date

Hi,

Can I write a code in SQL that return the current date? If so, how?

Thanks!

WillYou can use the GETDATE() function:

SELECT GETDATE() AS CurrentDateTime

And you can use the CONVERT function to format that datetime value.

Terri|||Convert(varchar(10),GetDate(),101)

returns the date as:

01/01/2004

Return COMPUTE from stored proc

I'm trying to figure out how to get my stored proc below to just return the result for COMPUTE only:
ALTER PROCEDURE [dbo].[procname]
AS
BEGIN
Select (cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR WHERE e='06'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100)
END
When my stored proc is run, it should only return one value, the result of COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100). Right now however, it returns only the list from the select (below) which I don't want, I just want the COMPUTE value returned which is one value (the sum of the items below):
27256.000000
14218.000000
0.000000
14930.000000
54824.000000
148616.666667
73320.000000
85956.000000
105507.500000
67911.904762
55276.190476
14467.500000
5985.000000
20910.000000
118784.000000
5340.000000
5295.000000
567.500000

It is not possible to modify behavior of COMPUTE. It is a non-standard / proprietary extension so you should avoid using it. Instead write your own query using say ROLLUP/CUBE operator to get the sum at the desired levels. This will allow you to control the output rows by filtering on GROUPING function return values. See BOL for examples.|||

Thanks figured that after spending half my day trying. I found a way to sum what I needed and return one GT without compute:

Select Total = SUM(gt)

FROM

(Select DistinctCustomerName,

CustomerNumber,

FeeGoal_AZ AS FG,

FeeSchedule,

(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

from DCR WHERE branch='00002'

group by CustomerName,

CustomerNumber,

FeeGoal_AZ,

FeeSchedule

) as dTable

return columns for different sql bases

I want in one query return columns from tables where exist in different sql bases.

In MS Access can link tables from many defferents sql bases or other MSAccess bases and finally can make a simple query where return columns from tables where exist in different bases.

How can i have the same functionality in Visual Basic .Net 2005 (or Visual Basic 6.0)

If you are using SQL Server, you can use Linked servers (see http://msdn2.microsoft.com/en-us/library/ms188279.aspx) or for a one time query to another database, you can use the OPENROWSET T-SQL directly in your SQL query (see http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

Regards, Uwa.

sql

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html#dynamic-sql
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:
> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to chec
k
for the success of the alter database stmt. @.@.error didn't trip to <> 0 an
d
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-hand...tml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to ch
eck
> for the success of the alter database stmt. @.@.error didn't trip to <> 0
and
> then tried placing a return status code variable after the execute stmt an
d
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.com
Try using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handl...ml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com
sql

Return Code from Stored Proc

I am doing an insert with a stored proc using the ExecuteNonQuery in the
DataAccess Block from Microsoft. My parameters are inserted correctly into
the database but my return code is always a -1 instead of 0. Please review
this code and tell me if you see something I am doing wrong> Thanks in
Advance!
CREATE PROCEDURE insertTrans_sp
(@.batch_id numeric,
@.cpi numeric,
@.visit numeric,
@.qty numeric,
@.gl_proc varchar(8),
@.gl_desc varchar(50),
@.charge float,
@.processed_date datetime,
@.processed_by varchar(50))
AS
SET NOCOUNT ON
DECLARE
@.err int,
@.err_desc varchar(50)
Begin Transaction
INSERT INTO Trans
(batch_id,
cpi,
visit,
qty,
gl_proc,
gl_desc,
charge,
processed_date,
processed_by)
VALUES
(@.batch_id,
@.cpi,
@.visit,
@.qty,
@.gl_proc,
@.gl_desc,
@.charge,
@.processed_date,
@.processed_by)
SET @.err = @.@.error
if @.err <> 0 GOTO ErrorHandler
COMMIT Transaction
return 0
ErrorHandler:
SET @.err_desc = 'Error occurred in insertTrans_sp'
EXEC insertErrLog_sp @.err, @.err_desc
return -100
GO
Robert HillRobert,
Exactly how are you executing this? Is this ADO, ADO.net etc? A couple of
comments here. One is that it is not necessary to wrap the Insert in an
explicit transaction. Any single sql statement is ATOMIC by itself. The
insert will either succeed or it won't. By wrapping it in a tran you now
have to commit or roll it back yourself. In this case you don't even have
any code to issue a rollback. You should always test the trancount level
before issuing a commit or rollback.
IF @.@.TRANCOUNT > 0
COMMIT TRAN
You declare a series of variables as Numeric but do not specify their
precision or scale. Always specify the size or scale of all data types.
These all look like Integers anyway. If that is the case it is more
efficient to declare them as integers than numeric.
Andrew J. Kelly SQL MVP
"Robert" <rhill938@.hotmail.com> wrote in message
news:0B27280E-35B3-4548-B928-34A0E227453F@.microsoft.com...
>I am doing an insert with a stored proc using the ExecuteNonQuery in the
> DataAccess Block from Microsoft. My parameters are inserted correctly
> into
> the database but my return code is always a -1 instead of 0. Please
> review
> this code and tell me if you see something I am doing wrong> Thanks in
> Advance!
> CREATE PROCEDURE insertTrans_sp
> (@.batch_id numeric,
> @.cpi numeric,
> @.visit numeric,
> @.qty numeric,
> @.gl_proc varchar(8),
> @.gl_desc varchar(50),
> @.charge float,
> @.processed_date datetime,
> @.processed_by varchar(50))
> AS
> SET NOCOUNT ON
> DECLARE
> @.err int,
> @.err_desc varchar(50)
> Begin Transaction
> INSERT INTO Trans
> (batch_id,
> cpi,
> visit,
> qty,
> gl_proc,
> gl_desc,
> charge,
> processed_date,
> processed_by)
> VALUES
> (@.batch_id,
> @.cpi,
> @.visit,
> @.qty,
> @.gl_proc,
> @.gl_desc,
> @.charge,
> @.processed_date,
> @.processed_by)
> SET @.err = @.@.error
> if @.err <> 0 GOTO ErrorHandler
> COMMIT Transaction
> return 0
> ErrorHandler:
> SET @.err_desc = 'Error occurred in insertTrans_sp'
> EXEC insertErrLog_sp @.err, @.err_desc
> return -100
> GO
> --
> Robert Hill
>|||in order to get a return code, I believe you need to use execute Scalar
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> in order to get a return code, I believe you need to use execute Scalar
>
Yes, the return from ExecuteNonQuery is NOT the stored procedure return
code.
No ExecuteScalar won't help. To get the return code you will need to use
CommandType.Text and write a batch like:
exec @.rc=MyProc(@.p1,@.p2,@.p3)
then bind an output parameter to @.rc.
In your case it's not necessary to test the return code. From client code
if something goes wrong you will get a SqlException. A stored procedure
return code is really just for other stored procedures. When one procedure
calls another procedure the calling procedure cannot intercept the error
messages generated by the called procedure, so it must use the return code
to determine if something went wrong. From SqlClient the error message will
appear as a SqlException and you can examine it in your catch block.
David|||Thanks.
"David Browne" wrote:

> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Yes, the return from ExecuteNonQuery is NOT the stored procedure return
> code.
> No ExecuteScalar won't help. To get the return code you will need to use
> CommandType.Text and write a batch like:
> exec @.rc=MyProc(@.p1,@.p2,@.p3)
> then bind an output parameter to @.rc.
> In your case it's not necessary to test the return code. From client code
> if something goes wrong you will get a SqlException. A stored procedure
> return code is really just for other stored procedures. When one procedur
e
> calls another procedure the calling procedure cannot intercept the error
> messages generated by the called procedure, so it must use the return code
> to determine if something went wrong. From SqlClient the error message wi
ll
> appear as a SqlException and you can examine it in your catch block.
> David
>
>|||Robert,
you dont have to set the command type to text.
you can (And in my opinion, should) leave the command type to "stored
procedure"
you can create a parameter object with direction of "Output" and get the
return value.
GAJ|||Thanks.
My solution was to use ExecuteScalar in the Application Block with the
command type "stored procedure" with an output parameter.
Robert
"pdxJaxon" wrote:

> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get the
> return value.
> GAJ
>
>|||pdxJaxon wrote:
> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get
> the return value.
>
You can also use a parameter with direction of "ReturnValue" to get the
return value ...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks!
I now need to return 2 values from a table. I tested my stored proc in
query analyzer and it seemed to work fine, returning the values I need. The
foloowing is the code I use in my app to call the stored proc using a
SqlParameter array. All I get back is a -1. What am I doing wrong?
SqlParameter[] oParms = new SqlParameter[3];
try
{
oParms[0] = new SqlParameter("@.cpi", sCPI);
oParms[1] = new SqlParameter("@.visit", ParameterDirection.Output);
oParms[2] = new SqlParameter("@.batch_id", ParameterDirection.Output);
ConnectString oCn = new ConnectString();
cn = oCn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteNonQuery(cn,
CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);
Robert
"Bob Barrows [MVP]" wrote:

> pdxJaxon wrote:
> You can also use a parameter with direction of "ReturnValue" to get the
> return value ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||close the connection before attempting to read the output parms
GAJ