3 table joins - 3rd table join main not exist (return null columns) - please help!

Hello SQL Guru's,

This has had me stumped for about 8 hours already and I think I've gotten to a point where I'm trying the same things over and over again and they are just not working. Any help would be greatly appreciated!

My Table Structure:

Table 1) 'Modules'

ModuleID | ModuleName | isVisible
--
1 Test 1 True
2 Test 2 True
3 Test 3 False
4 Test 4 True

Table 2) 'ModuleUserTypes'

ID | ModuleID | UserType

1 1 1
2 1 2
3 2 1
4 3 1
5 4 1
6 4 2

Table 3) 'ModuleUserSettings'

ID | ModuleID | UserID | CustomTitle | BGColor
--
1 2 1 New Title2 Black
2 2 2 New Title2 White
3 3 1 New Title3 Orange
4 4 1 NewTitle4 Yellow

My Goal:
To be able to join the 3 tables 'Modules', 'ModuleUserTypes', and 'ModuleUserSettings' together and return 'ModuleID, ModuleName, CustomTitle, BGColor' for ALL Modules with UserType = 1 along with associated ModuleUserSettings IF the UserSetting exists, otherwise NULL for the columns.

My desired result set:

UserID = 1
UserType = 1
isVisible = True

ModuleID | ModuleName | CustomTitle | BGColor
--
1 Test1 NULL NULL
2 Test2 New Title2 White
4 Test4 New Title4 Yellow

I'm sure this type of query will be easy for someone out there, but rather hard for me!

Thanks for your efforts!

Execute the following query, to get your results :

select m.ModuleID,m.ModuleName,CustomTitle,BGColor

from Modules m left join ModuleUserTypes mut on m.ModuleID = mut.ModuleID

left join ModuleUserSettings mus on m.ModuleID = mus.ModuleID

where (UserID is null or UserID = 1)

and UserType=1

and IsVisible = 1

Assumption - IsVisible column is bit data type

otherwise - use -

and IsVisible = 'True'

Thanks

Naras.

|||you need to include your User table or you table that define the usertype of a user and you need to use left join

hope this helps

SELECT *
INTO #Modules
FROM (
SELECT 1 AS ModuleID
,'Test 1' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 2 AS ModuleID
,'Test 2' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 3 AS ModuleID
,'Test 3' AS ModuleName
, 'False' AS isVisible
UNION ALL
SELECT 4 AS ModuleID
,'Test 4' AS ModuleName
, 'True' AS isVisible
) Modules

SELECT *
INTO #ModuleUserTypes
FROM ( SELECT 1 AS [ID]
, 1 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 2 AS [ID]
, 1 AS ModuleID
, 2 AS UserType
UNION ALL
SELECT 3 AS [ID]
, 2 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 4 AS [ID]
, 3 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 5 AS [ID]
, 4 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 6 AS [ID]
, 4 AS ModuleID
, 2 AS UserType

) ModuleUserTypes

SELECT *
INTO #ModuleUserSettings
FROM (

SELECT 1 AS [ID]
, 2 AS ModuleID
, 1 AS UserID
, 'New Title2' AS CustomTitle
, 'White' AS BGColor
UNION ALL
SELECT 2 AS [ID]
, 2 AS ModuleID
, 2 AS UserID
, 'New Title2' AS CustomTitle
, 'Black' AS BGColor
UNION ALL
SELECT 3 AS [ID]
, 3 AS ModuleID
, 1 AS UserID
, 'New Title3' AS CustomTitle
, 'Orange' AS BGColor
UNION ALL
SELECT 4 AS [ID]
, 4 AS ModuleID
, 1 AS UserID
, 'New Title4' AS CustomTitle
, 'Yellow' AS BGColor
) ModuleUserSettings

SELECT *
INTO #Users
FROM (
SELECT 1 AS UserID
, 1 AS UserType
UNION ALL
SELECT 2 AS UserID
, 2 AS UserType

) Users

DECLARE @.UserType int
DECLARE @.UserID int
DECLARE @.isVisible varchar(5)

SET @.UserType = 1
SET @.UserID = 1
SET @.isVisible = 'True'

SELECT DISTINCT
m.ModuleID
, m.ModuleName
, mus.CustomTitle
, mus.BGColor
FROM #Modules m LEFT OUTER JOIN
#ModuleUserTypes mut ON m.ModuleID = mut.ModuleID LEFT OUTER JOIN
#ModuleUserSettings mus ON m.ModuleID = mus.ModuleID
AND mut.ModuleID = mus.ModuleID LEFT OUTER JOIN
#Users ut ON mut.UserType = ut.UserType
AND mus.UserID = ut.UserID
WHERE ISNULL(mut.UserType,@.UserType) = @.UserType
AND ISNULL(mus.UserID,@.UserID) = @.UserID
AND ISNULL(m.isVisible,@.isVisible) = @.isVisible

DROP TABLE #Modules
DROP TABLE #ModuleUserTypes
DROP TABLE #ModuleUserSettings
DROP TABLE #Users|||

select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolour

from Modules m

join moduleusertypes mut

on m.moduleid = mut.moduleid

and mut.usertype = 1

left join ModuleUserSettings mus

on mus.moduleid = mut.moduleid

and mus.userid = mut.usertype

where IsVisible = 1

Assuming userid in 'ModuleUserSettings' is equal to UserType in 'ModuleUserTypes'

Regards,

kwareol

|||Thanks Nara's for your reply. I tried a similar statement but it was not filtering correctly. It would work until I added the UserType=1 and isVisible=1 to the where clause.
|||Kwareol,

Your statement took me in the right direction!

All I needed to add was the UserID filter.

This is the final statement that works exactly as I needed:

select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolor

from Modules m

join moduleusertypes mut

on m.moduleid = mut.moduleid

and mut.usertype = 1

left join ModuleUserSettings mus

on mus.moduleid = mut.moduleid

and (mus.userid = 1 or mus.userid is null)

where IsVisible = 1

Thank you and everybody so much for your time and efforts!! It's much much appreciated!

(I marked this post as the final answer. I'm not exactly sure how this forums works and if users get some sort of point ranking for posting correct answers. If so, I will change it to Kwareol for him leading me in the direction I needed to go)

3 small table database. Query: Return All Suppliers, Offering All Product, Excep

I have broken my question down into simpler terms using only 3 small tables, but the idea is the same. Well let me get to the problem (=.

Pretend we have just a small
database with 3 tables (Tb_Supplier, Tb_Product,
Tb_Offers)
Our problem is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.

Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.

CREATE TABLE Tb_Supplier (
Supp_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Product (
Prod_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Offers (
Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
Quantity [decimal](18, 0) NULL ,
Price [money] NULL
)

The query I'm trying to solve is to return ALL
supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.

Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.

SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

(also wrote it using the NOT IN statement)

SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

I don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)

The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).

Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively

And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)

Our Offers Table contains the following

Supp_ID Prod_ID
111 10
111 13

222 11
222 13
222 14

333 13
333 14
333 15

444 14
444 15

I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.

I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda

I hope I am explaining this well. Any reply is greatly appreciated. Thanks!

(Oh and yes this is just Microsoft SQL Syntax)Try the following:

First create a query of the products you want to show.

SELECT Prod_ID, [Name]
FROM Tb_Products
WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])

Once this query is created, create the new query using this query instead of Tb_Products|||this is a most interesting problem

let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)

in other words, a given supplier can offer a given product only once

(this is important because we'll be counting rows without using DISTINCT)

the number of products each supplier supplies is given by --
select Supp_ID, count(*)
from Tb_Offers
group by Supp_ID
the total number of products is --
select count(*) from Tb_Products
the suppliers which supply all products are --
select Supp_ID
from Tb_Offers
group by Supp_ID
having count(*) =
( select count(*) from Tb_Products )
now for the tricky part, excluding three certain products

first, let's figure out which product IDs they have --
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
now if a supplier supplies one of these three "excluded" products,
let's count a 1, and for any of the other products, let's count a 0 --
select Supp_ID
, sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
) then 1 else 0 end
) as excluded_product_count
from Tb_Offers
group by Supp_ID
notice how the subquery inside the CASE is not correlated,
which means that it will be extremely efficient

the suppliers we want are those with an "excluded product count" of 0

furthermore, the count of all the products they do supply has to be
3 less than the total number of products

so here is the final query --
select Supp_ID
from Tb_Offers
group by Supp_ID
having sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer' ,'car', 'tv')
) then 1 else 0 end
) = 0
and count(*) =
( select count(*) from Tb_Products ) - 3
i'm fairly confident in this, but have not tested it

please let me know how it works for you

rudy
http://r937.com/
http://rudy.ca/|||CreativeSoul,

What you are after is called relational division.

Celko has an article that you will find very helpful...

http://www.dbazine.com/celko1.html

Please read this first and then look at this query...There are several approaches to achieving this in SQL...but this is usually the easiest to understand....

SELECT S.Supp_ID
FROM Tb_Supplier S
INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
WHERE O.Prod_ID NOT IN(10,11,12)
GROUP BY S.Supp_ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))

3 columns as 1

I have three columns which I would like to have return as one column BUT have each column add its collection result to the end of the previous one.

Example

Col1 col2 col3

Happy Fun Land

Wally World Here

Result set would be as below

ColAlis

Happy

Wally

Fun

World

Land

Here

I can do this need at a code level on my application but i would rather the SQL statement do it.

Thanks

Here it is

Code Snippet

Create Table #data (

[Col1] Varchar(100) ,

[col2] Varchar(100) ,

[col3] Varchar(100)

);

Insert Into #data Values('Happy','Fun','Land');

Insert Into #data Values('Wally','World','Here');

For SQL Server 2000

Code Snippet

Select Col1 as [Values] From #Data

Union All

Select Col2 From #Data

Union All

Select Col3 From #Data

For SQL Server 2005

Code Snippet

Select [Values] from #Data

Unpivot

(

[Values] for Cols in (Col1,Col2,Col3)

) as Upvt Order By Cols

|||

Code Snippet

create table #t (Col1 varchar(10), col2 varchar(10), col3 varchar(10))

insert into #t

select 'Happy', 'Fun', 'Land'

union all select 'Wally', 'World', 'Here'

select Items

from

(select * from #t) p

unpivot

( items for data in (col1, col2, col3) ) as x

OR

Code Snippet

select col1 from #t

union all select col2 from #t

union all select col3 from #t

|||Try this:

SELECT col1+' '+col2+' '+col3 FROM yourtable

Have a nice day!

Edited:
I'm sorry, didn't read the desired result hehe.
This is not an option...|||

Try:

Code Snippet

declare @.t table (

col1 varchar(25),

col2 varchar(25),

col3 varchar(25)

)

insert into @.t values('Happy', 'Fun', 'Land')

insert into @.t values('Wally', 'World', 'Here')

select

t2.c1 as col,

case

when t2.c1 = 1 then col1

when t2.c1 = 2 then col2

when t2.c1 = 3 then col3

end as [value]

from

@.t as t1

cross join

(select 1 as c1 union all select 2 union all select 3) as t2

order by

col,

[value]

-- 2005

select

col,

[value]

from

(select col1 as [1], col2 as [2], col3 as [3] from @.t) as p

unpivot

([value] for col in ([1], [2], [3])) as unpvt

order by

col,

[value]

go

AMB

|||

Excellent suggestions Mani, Dale, Alejandro!

However, both solutions 'almost' produce the same resultset -not quite what the OP asked.

While two solutions (SQL 2000 / SQL 2005) was not specifically requested, it seems that if two solutions are provided, that they should both provide the same resultset to address the OP's problem. It's just a matter of quality control.

It seems that the 'closest' solution may perhaps be the simplest solution -adding an artificial grouping indicator. Since with the OP's data, there is no way to precisely determine the order of a column, either we have to conclude that we cannot derive an accurate solution for the problem, or we take corrective actions.

Here is a suggestion that 'should' alleviate the ordering problem:

Code Snippet

DECLARE @.Data table
( Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);

Insert Into @.Data Values ( 'Happy', 'Fun', 'Land' );
Insert Into @.Data Values ( 'Wally', 'World', 'Here' );

DECLARE @.Data2 table
( RowOrder int IDENTITY,
Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);

INSERT INTO @.Data2
SELECT *
FROM @.Data;

--Works in both SQL 2000/2005

SELECT
1 AS Collection,
RowOrder,
Col1 AS [Values]
FROM @.Data2

Union All

SELECT
2 AS Collection,
RowOrder,
Col2 AS [Values]
FROM @.Data2

Union All

SELECT
3 AS Collection,
RowOrder,
Col3 AS [Values]
FROM @.Data2

ORDER BY
Collection,
RowOrder

Collection RowOrder Values
-- -- --
1 1 Happy
1 2 Wally
2 1 Fun
2 2 World
3 1 Land
3 2 Here

Of course, there 'could' be a slight reorganization of the data order in the process of inserting from the original table into the 'identified' table (@.Data2) -but under most circumstances, that should be minimal.

(Dale, Alejandro -please send me an email -Arnie)

|||

Hi Arnie,

I am using "order by" clause to be consistent, but the result is lightly different from what the OP expected (values from col3 - 'Here', 'Land'). There is not an easy way to use a row number, to know from which row is every value coming from.

Thanks,

Alejandro Mesa

|||

You're absolutely correct Arnie.

Thanks!

I think I'm in need of a vacation

|||

Hi Arnie,

I agree with you, but I wonder how are you assuring that the data will be inserted into the second table in the same order you are inserting it into the first one.

> INSERT INTO @.Data2

> SELECT * FROM @.Data;

this statement does not guarantee that the row ( 'Happy', 'Fun', 'Land' ) will be the first one. Just add a clustered index (we need a table and not a table variable in order to do this) to table [Data], by [col1] DESC and that solution will fail. Check also the attached link.

Code Snippet

create table dbo.Data

( Col1 varchar(20) ,

Col2 varchar(20) ,

Col3 varchar(20)

);

Insert Into dbo.Data Values ( 'Happy', 'Fun', 'Land' );

Insert Into dbo.Data Values ( 'Wally', 'World', 'Here' );

go

create clustered index Data_col1_nu_c_ix

on dbo.Data(col1 DESC)

go

create table dbo.Data2

( RowOrder int IDENTITY,

Col1 varchar(20) ,

Col2 varchar(20) ,

Col3 varchar(20)

);

INSERT INTO dbo.Data2

SELECT *

FROM dbo.Data;

--Works in both SQL 2000/2005

SELECT

1 AS Collection,

RowOrder,

Col1 AS [Values]

FROM dbo.Data2

Union All

SELECT

2 AS Collection,

RowOrder,

Col2 AS [Values]

FROM dbo.Data2

Union All

SELECT

3 AS Collection,

RowOrder,

Col3 AS [Values]

FROM dbo.Data2

ORDER BY

Collection,

RowOrder

go

drop table dbo.Data, dbo.Data2

go

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

http://support.microsoft.com/kb/273586

AMB

|||

Alejandro,

I agree totally, since the OP didn't provide any indication of sequecing, positioning is totally happenstance.

Without a definitive sequencing indicator on the primary table, and without the option to alter the primary table to add such (PK, IDENTITY, Index, etc.), there is little that can be done to absolutely ensure sequence. The only option is to temporarily assert sequencing -as my example of moving into a temp table with IDENTITY. Whether or not it the sequence matches the primary table is totally by accident -BUT sequence can then be maintained for additional activities using the temp table. It just may be a different sequence the 'next time'.

|||

I haven’t dug deep into these solutions so I apologize in advance if I ask for clarification of what it does vs. what I need.

What I don’t want

Record 1 = 'HappyFunLand'

Record 2 = 'WallyWorldHere'

I want one column returned that says

Happy

Wally

Fun

World

Land

Here

The order I have listed above if look closely is actually contents of col1 then append contents of col2 then append contents of col3 all returned as one column.

So in the example below

insert into @.t values('Happy', 'Fun', 'Land')

insert into @.t values('Wally', 'World', 'Here')

I would be inserting only the first two rows of the original table from my understanding

Here is a more specific example of the real table

Column 1 = EnduserEmail

Column 2 = TechEmail

Column3 = managerEmail

I want to populate one dropdown with all emails from all columns without doing any coding which I could do but I would rather keep it on SQL if I can.

If your SQL statement or someone else’s does this I thank you all greatly, just point me to the correct post number.

Reason I am clarifying myself is only because I don’t want to learn the statement only to find it doesn’t do what I am looking for.

Thanks again!

|||

Sorry everyone I think I figured it out.

Example below

select Email1 from myTable

union all
Select Email2 from myTable

union all

Select Email3 from myTable

Orderby would simply be alpha numeric in my case which i assume is standard orderby.

Sorry I completely forgot about UNION statement

|||

Sean,

There is NO 'standard' sorting or ordering UNLESS you specifically use an ORDER BY.

Without the ORDER BY, it is just by chance.

|||

Right, what I ment to say was as long as a standard ORDER BY statement would work the same in the context of UNION statement as it would with a simple SELECT statement.

And it did perfectly.

Thanks again I have my sproc now.

2-table join where only need one row of second table.

For discussion sake, I've got two tables: customers and orders. What I want
is a query that will return the most recent order for each customer. Let's
say the tables are like:

Customer: customerId, customerName
Orders: orderId, customerId, orderDate

The simple joins I've done give me back all of the orders for all of the
customers.

Any advice?Try

select * from customer c, orders o
where c.customerId = o.customerId
and c.orderId =
(
select max (o2.orderDate)
from orders o2
where o2.customerId = c.customerId
)

--
Regards Bagieta
~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbDeveloper - Multiple databases editor
http://www.prominentus.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~

2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is the
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
@.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawrisch',null,n ull,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part of
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to get
this resolved for a migration
the parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:

> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is the
> same...a parametized function with a no join select on a table. The function
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is the
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
> @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawrisch',null,n ull,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are the
> same in either running the function or sql statement as above...I need to get
> this resolved for a migration
|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of any assistance.
|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan

2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is the
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState = @.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawrisch',null,null,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part of
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to get
this resolved for a migrationthe parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:
> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is the
> same...a parametized function with a no join select on a table. The function
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is the
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState => @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawrisch',null,null,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are the
> same in either running the function or sql statement as above...I need to get
> this resolved for a migration|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan|||nice catch...I thought it had to be something like that but I tried set
ansi_nulls off with an alter on the procedure...that did not work and that
is why I thought it was some other problem...didn't occur to me to drop and
recreate
also nice call with the @.birthCountry IS NULL was not looking at it from
that perspective...will be much cleaner...ticks me off because that was a dah!
Thnx again
"Razvan Socol" wrote:
> Here is a quote from Books Online:
> For stored procedures, SQL Server uses the SET ANSI_NULLS setting
> value from the initial creation time of the stored procedure.
> Whenever the stored procedure is subsequently executed,
> the setting of SET ANSI_NULLS is restored to its originally
> used value and takes effect. When invoked inside a stored
> procedure, the setting of SET ANSI_NULLS is not changed.
> In this aspect, views and functions are also treated like stored
> procedures. I guess you have created the function when SET ANSI_NULLS
> was ON, so whenever you execute the function, it considers this
> setting, regardless of the current state of SET ANSI_NULLS when the
> function is invoked.
> You can re-create the function with SET ANSI_NULLS OFF and it should
> behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
> would modify the function to use conditions like this:
> [...]
> AND (BirthCountry LIKE @.birthCountry
> OR BirthCountry = @.birthCountry
> OR @.birthCountry IS NULL)
> [...]
> Razvan
>|||Solved because of the ansi_nulls set to on upon creation of func...missed that
Thnx for help though
"Wei-Dong XU [MS]" wrote:
>
> Hi Blad,
> Welcome to use MSDN Managed Newsgroup!
> From your description, my understanding of this issue is: you use Inline
> table to query some data under SQL Server 2005, but it returns the
> different result with that the sql statement executed in Management Studio.
> And also the same SQL statement return the same results in SQL Server 2000.
> If I have misunderstood your concern, please feel free to point it out.
> Since you defined this function on a specific database and you use
> User-defined Data type in your table, I can not re-pro it on my own
> environment. So for narrowing down the question, would you like to give me
> some more information?
> 1. Which edition of SQL Server 2005 do you use?
> 2. What is the difference between the results of those 2 method? Does
> the Inline table return all the record with NULL value in those
> corresponding column?
> 3. Do you have any other function to query the table? If so, does this
> issue happen on those function?
> 4. If you create a simple function use Inline table ( with less
> criteria ) to query the table, does this issue happen?
> If there are more information on the issue, please feel free to let us
> know. Have a great day!
> Best Regards,
> Wei-Dong XU
> Microsoft Support
> ----
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ----
> It is my pleasure to be of any assistance.
>
>|||You are very welcome! Enjoy a nice weekend!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.

2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is th
e
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) O
R
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
@.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawris
ch',null,null,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part o
f
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to ge
t
this resolved for a migrationthe parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:

> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is
the
> same...a parametized function with a no join select on a table. The functi
on
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is t
he
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIK
E
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LI
KE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurit
y)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCoun
ty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry)
OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
> @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawris
ch',null,null,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part
of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = nul
l)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are th
e
> same in either running the function or sql statement as above...I need to
get
> this resolved for a migration|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan

2005 Express - SPs read-only

I am attempting to use a select SP with params to return data to MS Access. I am using SQL Server Management Express to manage SQL Server 2005. I can create tables, views and SPs just fine. Tables and views return data that is r/w but ALL SELECT SPs return data that is Read Only. Is this normal? I am under the impression that SPs can be used to return data to Access forms which is updateable, but I cannot even edit the data for an SP directly out in SMSE, never mind in Access.

TIA for any assistance on this,

John W. ColbyIf I understand your question properly, you are using a stored procedure to return a data set to a Microsoft Access form. If that is the case, then the result set is not updateable. In other words, you can't run a stored procedure, get some data, and then update that data on the screen and expect it to change in the database. You would need to code another stored procedure to take the updates and perform an UPDATE or INSERT statement back to the database from your Access form.

2005 Express - SPs read-only

I am attempting to use a select SP with params to return data to MS Access. I am using SQL Server Management Express to manage SQL Server 2005. I can create tables, views and SPs just fine. Tables and views return data that is r/w but ALL SELECT SPs return data that is Read Only. Is this normal? I am under the impression that SPs can be used to return data to Access forms which is updateable, but I cannot even edit the data for an SP directly out in SMSE, never mind in Access.

TIA for any assistance on this,

John W. ColbyIf I understand your question properly, you are using a stored procedure to return a data set to a Microsoft Access form. If that is the case, then the result set is not updateable. In other words, you can't run a stored procedure, get some data, and then update that data on the screen and expect it to change in the database. You would need to code another stored procedure to take the updates and perform an UPDATE or INSERT statement back to the database from your Access form.

2005 clr returning xmldocument

I would like to return an xmldocument from a 2005 vb clr stored procedure.

This is my definition for the stored procedure. passing in a string, return xmldoc.

Can I not return an xmldoc as output? The solution will build, but not run.

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Sub SP_Transform(ByVal cc As String, <Out()> ByVal RetValue As XmlDocument)

Error 1 Column, parameter, or variable #2: Cannot find data type XmlDocument. SqlServerProject1

Interesting question. I'll have to try it.

My gut, however, is that you should just return the XML data using the new Xml Data Type in SQL Server 2005 and just use a DataReader like dr.GetSqlXml(index) in ADO.NET to read the XML and load it into an XmlDocument.

I wrote an example of that here-

Reading XML Data Type into XmlDocument Using ADO.NET - SQL Server 2005 Tutorials

Regards,

Dave