Query works - sproc fails

I have a query that works fine but fails as a sproc.

QUERY:

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = 'Brown'

SPROC:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GetProfileId]

@.UserName nvarchar

AS

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = @.UserName

The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0

Set a size to the parameter. That might help.

|||

kerchunk!

That did it. Thanks!

Query Woes!

Query Help - Please
I have a table:
CREATE TABLE [dbo].[tblStudentOffers] (
[stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[start_study_date] [datetime] NULL ,
[comp_study_date] [datetime] NULL
) ON [PRIMARY]
GO
With Data:
stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crse
_code,subj_code,start_study_date,comp_st
udy_date
1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
I need to provide a list of new course enrolments for the month of March? If
providing new student date range of 2005-03-01 to 2005-03-31 the query would
need to return the following:
stud_no,crse_offer_code,crse_code,start_
study_date
1,30,COURSE3,2005-03-01
2,40,COURSE3,2005-03-01
Have the following query which eliminates student 1 and only return student
2. Am now really stumped how to achieve this. HEEEEEEELPP
select distinct stud_no,crse_offer_code,crse_code,start_
study_date
from tblStudentOffers
where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
CONVERT(DATETIME, '2005-03-31', 102)
AND stud_no not in
(SELECT stud_no FROM tblStudentOffers
WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
ORDER BY crse_code, stud_family_name
Thanks
RosscoMy best guess is this:
select stud_no, crse_offer_code, crse_code, start_study_date
from tblStudentOffers as SO1
where not exists (
select * from tblStudentOffers as SO2
where SO2.stud_no = SO1.stud_no
and SO2.start_study_date <= SO1.start_study_date
and SO2.crse_offer_code = SO1.crse_offer_code
and SO2.adms_elmnt_code < SO1.adms_elmnt_code
)
where start_study_date >= '20050301'
and start_study_date <= '20050401'
The parts of the criteria that get the specific single row you want
for a particular student probably have to be adjusted.
Steve Kass
Drew University
Rossco wrote:

>Query Help - Please
>I have a table:
>CREATE TABLE [dbo].[tblStudentOffers] (
> [stud_no] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [crse_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [subj_offer_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [adms_offer_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [adms_elmnt_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [crse_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subj_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [start_study_date] [datetime] NULL ,
> [comp_study_date] [datetime] NULL
> ) ON [PRIMARY]
>GO
>
>With Data:
> stud_no,crse_offer_code,subj_offer_code,
adms_offer_code,adms_elmnt_code,crs
e_code,subj_code,start_study_date,comp_s
tudy_date
> 1,10,40,40,ELEMENT1,COURSE1,ELEMENT1,200
4-10-01,2004-12-31
> 1,10,41,41,ELEMENT2,COURSE1,ELEMENT2,200
4-10-01,2004-12-31
> 1,10,42,42,ELEMENT3,COURSE1,ELEMENT3,200
5-02-01,2005-03-31
> 1,30,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 1,30,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 1,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 2,40,60,60,ELEMENT10,COURSE3,ELEMENT10,2
005-03-01,2005-05-25
> 2,40,61,61,ELEMENT11,COURSE3,ELEMENT11,2
005-03-01,2005-05-25
> 2,40,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-03-01,2005-05-25
> 3,35,62,62,ELEMENT12,COURSE3,ELEMENT12,2
005-02-01,2005-03-31
>I need to provide a list of new course enrolments for the month of March? I
f
>providing new student date range of 2005-03-01 to 2005-03-31 the query woul
d
>need to return the following:
> stud_no,crse_offer_code,crse_code,start_
study_date
>1,30,COURSE3,2005-03-01
>2,40,COURSE3,2005-03-01
>Have the following query which eliminates student 1 and only return student
>2. Am now really stumped how to achieve this. HEEEEEEELPP
>select distinct stud_no,crse_offer_code,crse_code,start_
study_date
>from tblStudentOffers
>where start_study_date BETWEEN CONVERT(DATETIME, '2005-03-01', 102) AND
>CONVERT(DATETIME, '2005-03-31', 102)
>AND stud_no not in
>(SELECT stud_no FROM tblStudentOffers
>WHERE start_study_date < CONVERT(DATETIME, '2005-03-01', 102))
>ORDER BY crse_code, stud_family_name
>Thanks
>Rossco
>|||Your blood is worth bottling! Thank you it works perfectly!
Cheers
"Steve Kass" wrote:

> My best guess is this:
> select stud_no, crse_offer_code, crse_code, start_study_date
> from tblStudentOffers as SO1
> where not exists (
> select * from tblStudentOffers as SO2
> where SO2.stud_no = SO1.stud_no
> and SO2.start_study_date <= SO1.start_study_date
> and SO2.crse_offer_code = SO1.crse_offer_code
> and SO2.adms_elmnt_code < SO1.adms_elmnt_code
> )
> where start_study_date >= '20050301'
> and start_study_date <= '20050401'
> The parts of the criteria that get the specific single row you want
> for a particular student probably have to be adjusted.
> Steve Kass
> Drew University
> Rossco wrote:
>
>|||Rossco wrote:
> Your blood is worth bottling! Thank you it works perfectly!
>
I think Steve is realy smart. Maybe even groovy. But I don't want his
blood. Angelina and Billy Bob ruined the whole bottled blood thing for
me.
David Gugick
Imceda Software
www.imceda.com|||Black pudding is quite nice.
http://www.g4cio.demon.co.uk/bpudding/pudding.htm
"David Gugick" wrote:

> Rossco wrote:
> I think Steve is realy smart. Maybe even groovy. But I don't want his
> blood. Angelina and Billy Bob ruined the whole bottled blood thing for
> me.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||In my neck of the woods it's called "boudin noir".
Although I find this sounds better than "blood pouding",
I still wouldn't touch it with... well, with anything.
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:2CC7355D-0E84-46A5-854E-6A5D34499C9B@.microsoft.com...
> Black pudding is quite nice.
> http://www.g4cio.demon.co.uk/bpudding/pudding.htm
>
> "David Gugick" wrote:
>sql

Query using two column names in a table (to find rows near each other)

I have a table called Seats in my database...
CREATE TABLE [dbo].[Seats] (
[SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[VehicleSerialNo] [int] NOT NULL ,
[RowNo] [smallint] NOT NULL ,
[ColumnNo] [smallint] NOT NULL ,
[SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This table defines the seats that are available for a given VehicleSerialNo
(the typical scenario would be a motor coach or possibly an aircraft). A
seat has a RowNo (how far down the vehicle it is), a ColumnNo (it's position
left to right on the vehicle) and a SeatNo (the actual seat number the
customer is given - e.g. please sit in seat number 40 - these can be numeric
or like aircraft seats numbers 23D etc).
I have another table called Passengers which stores the VehicleSerialNo (the
actual motor coach) and the SeatSerialNo (the seat they are sitting in on
that motor coach). Let's now assume this table contains lots of entries
already specifying where the existing passengers will be sitting.
Example: I now want to add 6 people on this vehicle and automatically
allocate each person a seat. I am trying to figure out whether this
automatic seat selection can be achieved in SQL Server or whether this
should be done on the client side using VB. Ideally you would always like
to make sure all 6 people are sitting on the same part of the motor coach
(unless it is getting full). Would it be possible to construct a query that
would select seats that are near each other based on RowNo and ColumnNo
(excluding SeatSerialNo's that exists in the Passengers table - e.g. no
double booking of a seat)? So I want to return a query that returns the 6
seats the system thinks are best. Can such a query be performed comparing
these column names (RowNo and ColumnNo) finding seats that are near to each
other.
Many thanks,
ChrisC-W
Its hard to suggest without seeing sample data+ relationship+ expected
result.
Why you don't have a primary on the table?
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:O0cKXJanFHA.3312@.tk2msftngp13.phx.gbl...
>I have a table called Seats in my database...
>
> CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> This table defines the seats that are available for a given
> VehicleSerialNo (the typical scenario would be a motor coach or possibly
> an aircraft). A seat has a RowNo (how far down the vehicle it is), a
> ColumnNo (it's position left to right on the vehicle) and a SeatNo (the
> actual seat number the customer is given - e.g. please sit in seat number
> 40 - these can be numeric or like aircraft seats numbers 23D etc).
>
> I have another table called Passengers which stores the VehicleSerialNo
> (the actual motor coach) and the SeatSerialNo (the seat they are sitting
> in on that motor coach). Let's now assume this table contains lots of
> entries already specifying where the existing passengers will be sitting.
>
> Example: I now want to add 6 people on this vehicle and automatically
> allocate each person a seat. I am trying to figure out whether this
> automatic seat selection can be achieved in SQL Server or whether this
> should be done on the client side using VB. Ideally you would always like
> to make sure all 6 people are sitting on the same part of the motor coach
> (unless it is getting full). Would it be possible to construct a query
> that would select seats that are near each other based on RowNo and
> ColumnNo (excluding SeatSerialNo's that exists in the Passengers table -
> e.g. no double booking of a seat)? So I want to return a query that
> returns the 6 seats the system thinks are best. Can such a query be
> performed comparing these column names (RowNo and ColumnNo) finding seats
> that are near to each other.
>
> Many thanks,
> Chris
>
>|||Sorry, that's just the way I scripted the table. SeatSerialNo is the
primary key.
I will try and work on some sample data.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uwOTGPanFHA.320@.TK2MSFTNGP09.phx.gbl...
> C-W
> Its hard to suggest without seeing sample data+ relationship+ expected
> result.
> Why you don't have a primary on the table?
>|||On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:

>I have a table called Seats in my database...
>
>CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
Hi Chris,
You have two good candidate keys in the table without the extra identity
column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
ColumnNo). My advice would be to drop the SeatSerialNo column, declare
one of the composite candidate keys to be the PRIMARY KEY (probably the
one with SeatNo, but it depends on a lot of factors I don't know) and
define a UNIQUE constraint for the other one.

>I have another table called Passengers which stores the VehicleSerialNo (th
e
>actual motor coach) and the SeatSerialNo (the seat they are sitting in on
>that motor coach).
That's redundant. What if a passenger has VehicleSerialNo 1 and
SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
VehicleSerialNo 2?
If you keep SeatSerialNo in Seats and use it to refer to a seat in the
Passengers table, then remove VehicleSerialNo from the Passengers table
(the seat will always be in the same vehicle, regardless of who is
sitting on it). Or, if you drop SeatSerialNo from Seats, store the
combination of VehicleSerialNo and SeatNo in the Passengers table.
(snip)
>So I want to return a query that returns the 6
>seats the system thinks are best. Can such a query be performed comparing
>these column names (RowNo and ColumnNo) finding seats that are near to each
>other.
As Uri said. A repro script that others can run to recreate your test
data and the expected output would make it much easier to help you.
See www.aspfaq.com/5006 for more details and hints.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Once i've generate the script to reproduce this I will explain the table
structure further (and hopefully will all make sense). I tried to reproduce
a simple example before but probably caused more confusion. The Seats table
does not actually contain the VehicleSerialNo. Hopefully all will make
sense when I post my script.
Thanks,
Chris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg3kf15cvqe3r4k8hfo1jnr7uhpf52o3el@.
4ax.com...
> On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:
>
> Hi Chris,
> You have two good candidate keys in the table without the extra identity
> column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
> ColumnNo). My advice would be to drop the SeatSerialNo column, declare
> one of the composite candidate keys to be the PRIMARY KEY (probably the
> one with SeatNo, but it depends on a lot of factors I don't know) and
> define a UNIQUE constraint for the other one.
>
> That's redundant. What if a passenger has VehicleSerialNo 1 and
> SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
> VehicleSerialNo 2?
> If you keep SeatSerialNo in Seats and use it to refer to a seat in the
> Passengers table, then remove VehicleSerialNo from the Passengers table
> (the seat will always be in the same vehicle, regardless of who is
> sitting on it). Or, if you drop SeatSerialNo from Seats, store the
> combination of VehicleSerialNo and SeatNo in the Passengers table.
>
> (snip)
> As Uri said. A repro script that others can run to recreate your test
> data and the expected output would make it much easier to help you.
> See www.aspfaq.com/5006 for more details and hints.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Query to know if a field exits in a table

In MsSQLServer what is the query to kown if a field exists in a table
if exist (select * from dbo.sysobjects where id =............
??SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 'MyTable' = TABLE_NAME-PatP|||select 1
from syscolumns
where ID = Object_ID ( '<table_name>' ) and
Name = '<field_name'

bEH

Query to get column name with specific value

Here is tested schema

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into testtable values ('123','pass',null,'fail','skip')
insert into testtable values ('456','fail',null,'pass','skip')
insert into testtable values ('789',null,'fail','skip','pass')
insert into testtable values ('345','pass','pass','pass','fail')

I would like to fetch the COLUMNNAME where the value is fail.
Basically I need to know which test failed, test1, test2, test3 or
test4?

Is this possible?One method:

SELECT CASE
WHEN test1 = 'fail' THEN 'test1'
WHEN test2 = 'fail' THEN 'test2'
WHEN test3 = 'fail' THEN 'test3'
WHEN test4 = 'fail' THEN 'test4'
END AS Test
FROM testtable

This could also be accomplished with a rather ugly dynamic SQL script but
I'd rather not go there. You might consider revising your schema to
eliminate the repeating data. It's a lot easier to query data when your
data is in 1NF. Suggested alternative:

CREATE TABLE TestTable
(
SerialNumber char (12) NOT NULL,
TestNumber int NOT NULL,
TestResult varchar(10),
CONSTRAINT PK_TestTable PRIMARY KEY(SerialNumber, TestNumber)
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kj" <kjaggi@.hotmail.com> wrote in message
news:665416be.0409071642.3710dac0@.posting.google.c om...
> Here is tested schema
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[TestTable]
> GO
> CREATE TABLE [dbo].[TestTable] (
> [SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testtable values ('123','pass',null,'fail','skip')
> insert into testtable values ('456','fail',null,'pass','skip')
> insert into testtable values ('789',null,'fail','skip','pass')
> insert into testtable values ('345','pass','pass','pass','fail')
>
> I would like to fetch the COLUMNNAME where the value is fail.
> Basically I need to know which test failed, test1, test2, test3 or
> test4?
> Is this possible?|||Thanks. I have already changed the schema. This is legacy stuff I am
trying to clean up but I needed to migrate the data to the new schema.

Query Syntax help

Hello All,

I have the following table:

CREATE TABLE [dbo].[TBL_NAME] (
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

With values:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')

Question is:

I need want to construct a query which returns all names for a standard
name plus the standard name itself.
e.g.
if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'

i have the following sql:

declare @.name varchar(50)
select @.name = 'DANIEL'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

declare @.name varchar(50)
select @.name = 'DANNY'
select standard_name from tbl_name where name = @.name
union
select name from tbl_name where standard_name = (select standard_name
from tbl_name where name = @.name)
union
select name from tbl_name where standard_name = @.name
union
select standard_name from tbl_name where standard_name = @.name

--

Both appear to work fine..can anyone see a fault or suggest a cleaner
way to achieve the above ?

Suggestions/pointers appreciated
Thanks in advance1) How many people do you know or have ever heard of that have a name
that need to have CHAR(50)? The USPS allows CHAR(35)

2) Why did you violate common sense and ISO-11179 Standards with the
"tbI-" prefix?

3) Why don't you have a key? Why did you prevent having a key with
NULL_able? Why are you smarter than Dr. Codd?

4) If you knew SQL would this look like this:

CREATE TABLE FirstNames
(first_name VARCHAR (35) NOT NULL
CHECK (first_name = RTRIM(LTRIM(first_name))),
alternate_first_name VARCHAR (35) NOT NULL
CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
PRIMARY KEY (first_name, alternate_first_name)
);

>> I need want to construct a query which returns all names for a standard name plus the standard name itself. <<

SELECT first_name, alternate_first_name
FROM FirstNames
WHERE first_name = @.my_guy;|||hharry (paulquigley@.nyc.com) writes:
> CREATE TABLE [dbo].[TBL_NAME] (
> [NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> With values:
> insert into tbl_name
> values('DAN', 'DANIEL')
> insert into tbl_name
> values('DANNY', 'DANIEL')
> insert into tbl_name
> values('DANYY', 'DANIEL')
> Question is:
> I need want to construct a query which returns all names for a standard
> name plus the standard name itself.
> e.g.
> if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
> ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'
>...

If you add a row with (DANIEL, DANIEL), you can write a much simpler query:

insert into tbl_name
values('DAN', 'DANIEL')
insert into tbl_name
values('DANNY', 'DANIEL')
insert into tbl_name
values('DANYY', 'DANIEL')
insert into tbl_name
values('DANIEL', 'DANIEL')
go
DECLARE @.name varchar(50)
SELECT @.name = 'DANIEL'
SELECT name
FROM tbl_name t1
WHERE EXISTS (SELECT name, standard_name
FROM tbl_name t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)
go

If this change is not feasible or possible, you could write:

SELECT t1.name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t1
WHERE EXISTS (SELECT name, standard_name
FROM (SELECT name, standard_name
FROM tbl_name
UNION
SELECT standard_name, standard_name
FROM tbl_name) t2
WHERE t2.standard_name = t1.standard_name
AND t2.name = @.name)

But that's certainly a little more complex, and whether it's cleaner
your current query is a matter of taste.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) How many people do you know or have ever heard of that have a name
> that need to have CHAR(50)? The USPS allows CHAR(35)
> 2) Why did you violate common sense and ISO-11179 Standards with the
> "tbI-" prefix?
> 3) Why don't you have a key? Why did you prevent having a key with
> NULL_able? Why are you smarter than Dr. Codd?
> 4) If you knew SQL would this look like this:
> CREATE TABLE FirstNames
> (first_name VARCHAR (35) NOT NULL
> CHECK (first_name = RTRIM(LTRIM(first_name))),
> alternate_first_name VARCHAR (35) NOT NULL
> CHECK (alternate_first_name = RTRIM(LTRIM(alternate_first_name))),
> PRIMARY KEY (first_name, alternate_first_name)
> );
>>> I need want to construct a query which returns all names for a standard
name plus the standard name itself. <<
> SELECT first_name, alternate_first_name
> FROM FirstNames
> WHERE first_name = @.my_guy;

I don't know don't if "hharry" is smarter than Codd, but he is
obviously smarter than you. After all, he was able to write a query
that solved his problem - you weren't. (Since hharry supplied tables
and insert statements, you could have tested.)

As for your points 1-3, they are completely irrelevant and not the least
helpful. Just impolite and unfriendly. My guess is that hharry's real
business problem is different, and the table he posted he just a
throwaway table to demonstrate the SQL problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp