Prev: Help with FOR XML
Next: SQL Question
From: Hattori on 12 Mar 2010 15:13
Hi, I wish some body could Help Me, I have being loocking for a solution for
2 days.

I need to make a simple Query to check if the parameter values are in a
Table. I want the Query to return "Founds" and "Not Founds".

For Example:

Table Definition: TName

CREATE TABLE [dbo].[TName]
(
[IdName] [bigint] IDENTITY (0, 1) NOT NULL ,
[Name] [nvarchar] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL
) ON [PRIMARY]

Values:
Insert Into TName (Name) Values ('Paul')
Insert Into TName (Name) Values ('Peter')
Insert Into TName (Name) Values ('Michael')
Insert Into TName (Name) Values ('Charls')
Insert Into TName (Name) Values ('Pamela')

My Query is:

Select Name From TName Where Name in
('Pamela','Charls','Peter','Jhon','Monica','Eduard','Nicole')

The result would be:

Name
_________
Pamela
Charls
Peter

This query will give me just the Founds, but I want to know wich records
where found and wich were not.

I want to know is there is a way to get a result like:

Select InQuery, Tname.Name as InDB from TName IN
('Pamela','Charls','Peter','Jhon','Monica','Eduard','Nicole') (Just
imagin...)

InQuery InDB
_______ _______
Pamela Pamela
Charls Charls
Peter Peter
Jhon
Monica
Eduard
Nicole

So what I need, is to get the In Parameters as a value for a Column in order
to know wich values exist and wich others dont.

Thanks in advances, You will really help with your support

Carlos Lee


From: Scott Morris on 12 Mar 2010 15:35

"Hattori" <concor(a)nad-int.com> wrote in message
news:OqGdDCiwKHA.3536(a)TK2MSFTNGP06.phx.gbl...
> Hi, I wish some body could Help Me, I have being loocking for a solution
> for 2 days.
>
> I need to make a simple Query to check if the parameter values are in a
> Table. I want the Query to return "Founds" and "Not Founds".

You can find many versions of a TVF that will take a CSV string and convert
it to a table. With that, you can then outer join the TVF to your table and
use a case expression to generate the values "Found" and "Not Found"

One example:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/2366112304848c27/40274338907f5c40?hl=en&q=csv+convert+group:microsoft.public.sqlserver.programming

For more information about the general technique and performance
considerations:
http://www.sommarskog.se/arrays-in-sql-2005.html


 | 
Pages: 1
Prev: Help with FOR XML
Next: SQL Question