Join sp between Primary and controls?

I have mastered creating simple Select procedure in sql server, but now need
to create a query that returns values for three parameters: u_id(UserID),
BrokerID, SourceID

If the parameters are null it should return all rows; if value present it
should return the target rows. This seems to indicate a Join Query with
parameters...could someone offer advice on how to achieve this?

Many thanks
Jason



Primary Table Get User
Create Procedure sp_GetUser
@u_id int
As
set nocount on
select * from t_user where u_id=@u_id
return

GO




Control Table 2: Get Source
CREATE Procedure spr_GetSource
@SourceID int
As
set nocount on
select * from Source where SourceID=@SourceID
return
GO



Control Table 2: Get Broker
CREATE Procedure spr_GetBroker
@Brokerid int
As
set nocount on
select * from Broker where BrokerID=@BrokerID
return

GO

[Aaron [SQL Server MVP]]

Can you show us the table structure, some sample data, and desired results
given different parameters?

[url]http://www.aspfaq.com/5006[/url]

I like solving database problems, not word problems. ;-)

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




<jason@catamaranco.com> wrote in message
news:#0#d3Q9fEHA.3632@TK2MSFTNGP11.phx.gbl...

>
> I have mastered creating simple Select procedure in sql server, but now

need

> to create a query that returns values for three parameters: u_id(UserID),
> BrokerID, SourceID
>
> If the parameters are null it should return all rows; if value present it
> should return the target rows. This seems to indicate a Join Query with
> parameters...could someone offer advice on how to achieve this?
>
> Many thanks
> Jason
>
>
>
> Primary Table Get User
> Create Procedure sp_GetUser
> @u_id int
> As
> set nocount on
> select * from t_user where u_id=@u_id
> return
>
> GO
>
>
>
>
> Control Table 2: Get Source
> CREATE Procedure spr_GetSource
> @SourceID int
> As
> set nocount on
> select * from Source where SourceID=@SourceID
> return
> GO
>
>
>
> Control Table 2: Get Broker
> CREATE Procedure spr_GetBroker
> @Brokerid int
> As
> set nocount on
> select * from Broker where BrokerID=@BrokerID
> return
>
> GO
>
>

Got it....read article, so here is:

Goal: My registration page presents a user form with some of the fields
being database looks (see further below). On post, I create a new user and
deposit the lookup values
into the main table t_user. However, upon doing this I wish to now retrieve
the 'varchar' values for the lookup tables based on the numeric IDs selected
by the user in the form.

See example:
[url]http://69.2.200.70/dev/1/join.asp?Cookiechk=Disabled&u_FromURL=http://69.2.200.70/dev/1/link.asp&u_CurrentURL=/dev/1/doc3.asp[/url]

I thought perhaps a composite query would help me effeciently streamline my
asp code and allow me to do this in on go. I also hope to reuse the same
query later in the application
thus it needs to deal with null values.

A weakness in the in my table relationships may relate to the fact that I
look up tables (Nation) and (State) perhaps I should not have enforced
referential integrity on these tables and simply hard-coded
them into the web page which would simply future queries. Was I clear
enough...let me know:

Primary table:
---------------

CREATE TABLE [t_user] (
[u_id] [int] IDENTITY (1, 1) NOT NULL ,
[u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT (1),
[u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
DEFAULT (getdate()),
[u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate] DEFAULT
(getdate()),
[u_SecurityLevel] [int] NULL ,
[u_SourceID] [int] NULL ,
[u_YachtPlacementID] [int] NULL ,
[u_NationID] [int] NULL ,
[u_StateID] [int] NULL ,
[u_BrokerID] [int] NULL ,
[u_ExpiryDate] [datetime] NULL ,
[u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_VisitorIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_AcceptCookies] [int] NULL ,
[u_AcceptEmail] [int] NULL ,
[u_EmailOK] [int] NULL ,
[u_Browser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_OS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_TimeFrame] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_IDStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
(
[u_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
(
[u_name]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
(
[u_BrokerID]
) REFERENCES [Broker] (
[BrokerID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
(
[u_NationID]
) REFERENCES [Nation] (
[NationID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_t_user_Source] FOREIGN KEY
(
[u_SourceID]
) REFERENCES [Source] (
[SourceID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_t_user_State] FOREIGN KEY
(
[u_StateID]
) REFERENCES [State] (
[StateID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
(
[u_YachtPlacementID]
) REFERENCES [YachtPlacement] (
[YppID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO

Lookup Table (1)
-----------------

CREATE TABLE [Source] (
[SourceID] [int] IDENTITY (1, 1) NOT NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
(
[SourceID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO


Lookup Table (2)
-----------------

CREATE TABLE [Broker] (
[BrokerID] [int] IDENTITY (1, 1) NOT NULL ,
[Broker] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BrokerURL] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
(
[BrokerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO


lookup Table (3)
CREATE TABLE [YachtPlacement] (
[YppID] [int] IDENTITY (1, 1) NOT NULL ,
[YachtPlacement] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_YachtPlacementProgram] PRIMARY KEY CLUSTERED
(
[YppID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Lookup Table (4)
------------------
CREATE TABLE [Nation] (
[NationID] [int] IDENTITY (1, 1) NOT NULL ,
[Nation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Nation] PRIMARY KEY CLUSTERED
(
[NationID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO


Lookup Table (5)
--------------------

CREATE TABLE [State] (
[StateID] [int] IDENTITY (1, 1) NOT NULL ,
[State] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
(
[StateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO







"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:u%23IIlT9fEHA.1188@TK2MSFTNGP11.phx.gbl...

> Can you show us the table structure, some sample data, and desired results
> given different parameters?
>
> [url]http://www.aspfaq.com/5006[/url]
>
> I like solving database problems, not word problems. ;-)
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
> <jason@catamaranco.com> wrote in message
> news:#0#d3Q9fEHA.3632@TK2MSFTNGP11.phx.gbl...

> >
> > I have mastered creating simple Select procedure in sql server, but now

> need

> > to create a query that returns values for three parameters:

u_id(UserID),

> > BrokerID, SourceID
> >
> > If the parameters are null it should return all rows; if value present

it

> > should return the target rows. This seems to indicate a Join Query

with

> > parameters...could someone offer advice on how to achieve this?
> >
> > Many thanks
> > Jason
> >
> >
> >
> > Primary Table Get User
> > Create Procedure sp_GetUser
> > @u_id int
> > As
> > set nocount on
> > select * from t_user where u_id=@u_id
> > return
> >
> > GO
> >
> >
> >
> >
> > Control Table 2: Get Source
> > CREATE Procedure spr_GetSource
> > @SourceID int
> > As
> > set nocount on
> > select * from Source where SourceID=@SourceID
> > return
> > GO
> >
> >
> >
> > Control Table 2: Get Broker
> > CREATE Procedure spr_GetBroker
> > @Brokerid int
> > As
> > set nocount on
> > select * from Broker where BrokerID=@BrokerID
> > return
> >
> > GO
> >
> >

>
>

Ok...experimented and came up with initial parametized Join:


CREATE Procedure spr_GetUserSelections
@u_ID int

As
set nocount on
Select t_user.u_name, Source.Source FROM
t_user INNER JOIN Source ON
t_user.u_SourceID = Source.SourceID
WHERE t_user.u_ID=@u_ID

return
GO

It tested successfully.

If I did this correctly it should:

1. Allow me to pass the User ID to table: t_user
2. Return Username 't_user' from table t_user and 'Source' from the lookup
table 'Source'.

Now
-----

I need to add in additional lookup tables to the query (see previous
thread).....thus, the join becomes more complex.

?
---

Is there a way for me to test returned rows by giving the SP a dummy
parameter in the anallyzer...this is probably a very stupid question
and I know when you tell me the answer I will kick myself?

Thanks
Jaso0n

<jason@catamaranco.com> wrote in message
news:u$%23bRs%23fEHA.3944@tk2msftngp13.phx.gbl...

> Got it....read article, so here is:
>
> Goal: My registration page presents a user form with some of the fields
> being database looks (see further below). On post, I create a new user and
> deposit the lookup values
> into the main table t_user. However, upon doing this I wish to now

retrieve

> the 'varchar' values for the lookup tables based on the numeric IDs

selected

> by the user in the form.
>
> See example:
>

[url]http://69.2.200.70/dev/1/join.asp?Cookiechk=Disabled&u_FromURL=http://69.2.200.70/dev/1/link.asp&u_CurrentURL=/dev/1/doc3.asp[/url]

>
> I thought perhaps a composite query would help me effeciently streamline

my

> asp code and allow me to do this in on go. I also hope to reuse the same
> query later in the application
> thus it needs to deal with null values.
>
> A weakness in the in my table relationships may relate to the fact that I
> look up tables (Nation) and (State) perhaps I should not have enforced
> referential integrity on these tables and simply hard-coded
> them into the web page which would simply future queries. Was I clear
> enough...let me know:
>
> Primary table:
> ---------------
>
> CREATE TABLE [t_user] (
> [u_id] [int] IDENTITY (1, 1) NOT NULL ,
> [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT

NULL

> ,
> [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,

> [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT

(1),

> [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
> DEFAULT (getdate()),
> [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate]

DEFAULT

> (getdate()),
> [u_SecurityLevel] [int] NULL ,
> [u_SourceID] [int] NULL ,
> [u_YachtPlacementID] [int] NULL ,
> [u_NationID] [int] NULL ,
> [u_StateID] [int] NULL ,
> [u_BrokerID] [int] NULL ,
> [u_ExpiryDate] [datetime] NULL ,
> [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_VisitorIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_AcceptCookies] [int] NULL ,
> [u_AcceptEmail] [int] NULL ,
> [u_EmailOK] [int] NULL ,
> [u_Browser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_OS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_TimeFrame] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [u_IDStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
> (
> [u_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
> (
> [u_name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
> (
> [u_BrokerID]
> ) REFERENCES [Broker] (
> [BrokerID]
> ) ON UPDATE CASCADE ,
> CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
> (
> [u_NationID]
> ) REFERENCES [Nation] (
> [NationID]
> ) ON UPDATE CASCADE ,
> CONSTRAINT [FK_t_user_Source] FOREIGN KEY
> (
> [u_SourceID]
> ) REFERENCES [Source] (
> [SourceID]
> ) ON UPDATE CASCADE ,
> CONSTRAINT [FK_t_user_State] FOREIGN KEY
> (
> [u_StateID]
> ) REFERENCES [State] (
> [StateID]
> ) ON UPDATE CASCADE ,
> CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
> (
> [u_YachtPlacementID]
> ) REFERENCES [YachtPlacement] (
> [YppID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
>
> Lookup Table (1)
> -----------------
>
> CREATE TABLE [Source] (
> [SourceID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
> (
> [SourceID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
> Lookup Table (2)
> -----------------
>
> CREATE TABLE [Broker] (
> [BrokerID] [int] IDENTITY (1, 1) NOT NULL ,
> [Broker] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BrokerURL] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
> (
> [BrokerID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
> lookup Table (3)
> CREATE TABLE [YachtPlacement] (
> [YppID] [int] IDENTITY (1, 1) NOT NULL ,
> [YachtPlacement] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> CONSTRAINT [PK_YachtPlacementProgram] PRIMARY KEY CLUSTERED
> (
> [YppID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> Lookup Table (4)
> ------------------
> CREATE TABLE [Nation] (
> [NationID] [int] IDENTITY (1, 1) NOT NULL ,
> [Nation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Nation] PRIMARY KEY CLUSTERED
> (
> [NationID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
> Lookup Table (5)
> --------------------
>
> CREATE TABLE [State] (
> [StateID] [int] IDENTITY (1, 1) NOT NULL ,
> [State] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
> (
> [StateID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>
>
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:u%23IIlT9fEHA.1188@TK2MSFTNGP11.phx.gbl...

> > Can you show us the table structure, some sample data, and desired

results

> > given different parameters?
> >
> > [url]http://www.aspfaq.com/5006[/url]
> >
> > I like solving database problems, not word problems. ;-)
> >
> > --
> > [url]http://www.aspfaq.com/[/url]
> > (Reverse address to reply.)
> >
> >
> >
> >
> > <jason@catamaranco.com> wrote in message
> > news:#0#d3Q9fEHA.3632@TK2MSFTNGP11.phx.gbl...

> > >
> > > I have mastered creating simple Select procedure in sql server, but

now

> > need

> > > to create a query that returns values for three parameters:

> u_id(UserID),

> > > BrokerID, SourceID
> > >
> > > If the parameters are null it should return all rows; if value present

> it

> > > should return the target rows. This seems to indicate a Join Query

> with

> > > parameters...could someone offer advice on how to achieve this?
> > >
> > > Many thanks
> > > Jason
> > >
> > >
> > >
> > > Primary Table Get User
> > > Create Procedure sp_GetUser
> > > @u_id int
> > > As
> > > set nocount on
> > > select * from t_user where u_id=@u_id
> > > return
> > >
> > > GO
> > >
> > >
> > >
> > >
> > > Control Table 2: Get Source
> > > CREATE Procedure spr_GetSource
> > > @SourceID int
> > > As
> > > set nocount on
> > > select * from Source where SourceID=@SourceID
> > > return
> > > GO
> > >
> > >
> > >
> > > Control Table 2: Get Broker
> > > CREATE Procedure spr_GetBroker
> > > @Brokerid int
> > > As
> > > set nocount on
> > > select * from Broker where BrokerID=@BrokerID
> > > return
> > >
> > > GO
> > >
> > >

> >
> >

>
>

I tried this:


CREATE Procedure spr_GetUserSelections
@u_ID int

As
set nocount on
Select t_user.u_name, Source.Source, YachtPlacement.YachtPlacement
FROM t_user
INNER JOIN Source ON
t_user.u_SourceID = Source.SourceID
INNER JOIN YachtPlacement ON
t_user.u_YachPlacementID = YachtPlacement.YppID
WHERE t_user.u_ID=@u_ID

return

GO

But I cannot figure out how to get that extra inner join in there....I am
trying to return values from the lookup tables: Source, YachtPlacement withn
t_user

- Jason
<jason@catamaranco.com> wrote in message
news:OorwxzHgEHA.1972@TK2MSFTNGP09.phx.gbl...

> Ok...experimented and came up with initial parametized Join:
>
>
> CREATE Procedure spr_GetUserSelections
> @u_ID int
>
> As
> set nocount on
> Select t_user.u_name, Source.Source FROM
> t_user INNER JOIN Source ON
> t_user.u_SourceID = Source.SourceID
> WHERE t_user.u_ID=@u_ID
>
> return
> GO
>
> It tested successfully.
>
> If I did this correctly it should:
>
> 1. Allow me to pass the User ID to table: t_user
> 2. Return Username 't_user' from table t_user and 'Source' from the

lookup

> table 'Source'.
>
> Now
> -----
>
> I need to add in additional lookup tables to the query (see previous
> thread).....thus, the join becomes more complex.
>
> ?
> ---
>
> Is there a way for me to test returned rows by giving the SP a dummy
> parameter in the anallyzer...this is probably a very stupid question
> and I know when you tell me the answer I will kick myself?
>
> Thanks
> Jaso0n
>
> <jason@catamaranco.com> wrote in message
> news:u$%23bRs%23fEHA.3944@tk2msftngp13.phx.gbl...

> > Got it....read article, so here is:
> >
> > Goal: My registration page presents a user form with some of the fields
> > being database looks (see further below). On post, I create a new user

and

> > deposit the lookup values
> > into the main table t_user. However, upon doing this I wish to now

> retrieve

> > the 'varchar' values for the lookup tables based on the numeric IDs

> selected

> > by the user in the form.
> >
> > See example:
> >

>

[url]http://69.2.200.70/dev/1/join.asp?Cookiechk=Disabled&u_FromURL=http://69.2.200.70/dev/1/link.asp&u_CurrentURL=/dev/1/doc3.asp[/url]

> >
> > I thought perhaps a composite query would help me effeciently streamline

> my

> > asp code and allow me to do this in on go. I also hope to reuse the same
> > query later in the application
> > thus it needs to deal with null values.
> >
> > A weakness in the in my table relationships may relate to the fact that

I

> > look up tables (Nation) and (State) perhaps I should not have enforced
> > referential integrity on these tables and simply hard-coded
> > them into the web page which would simply future queries. Was I clear
> > enough...let me know:
> >
> > Primary table:
> > ---------------
> >
> > CREATE TABLE [t_user] (
> > [u_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [u_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [u_password] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [u_firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT

> NULL

> > ,
> > [u_lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT

NULL

> ,

> > [u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,

> > [u_logcount] [int] NOT NULL CONSTRAINT [DF_t_user_u_logcount] DEFAULT

> (1),

> > [u_createDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_createDate]
> > DEFAULT (getdate()),
> > [u_lastDate] [datetime] NOT NULL CONSTRAINT [DF_t_user_u_lastDate]

> DEFAULT

> > (getdate()),
> > [u_SecurityLevel] [int] NULL ,
> > [u_SourceID] [int] NULL ,
> > [u_YachtPlacementID] [int] NULL ,
> > [u_NationID] [int] NULL ,
> > [u_StateID] [int] NULL ,
> > [u_BrokerID] [int] NULL ,
> > [u_ExpiryDate] [datetime] NULL ,
> > [u_Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [u_CurrentURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [u_FromURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [u_VisitorIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [u_AcceptCookies] [int] NULL ,
> > [u_AcceptEmail] [int] NULL ,
> > [u_EmailOK] [int] NULL ,
> > [u_Browser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [u_OS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [u_TimeFrame] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [u_IDStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_t_user_name] PRIMARY KEY NONCLUSTERED
> > (
> > [u_id]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> > CONSTRAINT [IX_t_user_name] UNIQUE NONCLUSTERED
> > (
> > [u_name]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> > CONSTRAINT [FK_t_user_Broker] FOREIGN KEY
> > (
> > [u_BrokerID]
> > ) REFERENCES [Broker] (
> > [BrokerID]
> > ) ON UPDATE CASCADE ,
> > CONSTRAINT [FK_t_user_Nation] FOREIGN KEY
> > (
> > [u_NationID]
> > ) REFERENCES [Nation] (
> > [NationID]
> > ) ON UPDATE CASCADE ,
> > CONSTRAINT [FK_t_user_Source] FOREIGN KEY
> > (
> > [u_SourceID]
> > ) REFERENCES [Source] (
> > [SourceID]
> > ) ON UPDATE CASCADE ,
> > CONSTRAINT [FK_t_user_State] FOREIGN KEY
> > (
> > [u_StateID]
> > ) REFERENCES [State] (
> > [StateID]
> > ) ON UPDATE CASCADE ,
> > CONSTRAINT [FK_t_user_YachtPlacement] FOREIGN KEY
> > (
> > [u_YachtPlacementID]
> > ) REFERENCES [YachtPlacement] (
> > [YppID]
> > ) ON UPDATE CASCADE
> > ) ON [PRIMARY]
> > GO
> >
> > Lookup Table (1)
> > -----------------
> >
> > CREATE TABLE [Source] (
> > [SourceID] [int] IDENTITY (1, 1) NOT NULL ,
> > [Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
> > (
> > [SourceID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Lookup Table (2)
> > -----------------
> >
> > CREATE TABLE [Broker] (
> > [BrokerID] [int] IDENTITY (1, 1) NOT NULL ,
> > [Broker] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BrokerURL] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
> > (
> > [BrokerID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> >
> > lookup Table (3)
> > CREATE TABLE [YachtPlacement] (
> > [YppID] [int] IDENTITY (1, 1) NOT NULL ,
> > [YachtPlacement] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > CONSTRAINT [PK_YachtPlacementProgram] PRIMARY KEY CLUSTERED
> > (
> > [YppID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> > Lookup Table (4)
> > ------------------
> > CREATE TABLE [Nation] (
> > [NationID] [int] IDENTITY (1, 1) NOT NULL ,
> > [Nation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_Nation] PRIMARY KEY CLUSTERED
> > (
> > [NationID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Lookup Table (5)
> > --------------------
> >
> > CREATE TABLE [State] (
> > [StateID] [int] IDENTITY (1, 1) NOT NULL ,
> > [State] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
> > (
> > [StateID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> >
> >
> >
> >
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> > news:u%23IIlT9fEHA.1188@TK2MSFTNGP11.phx.gbl...

> > > Can you show us the table structure, some sample data, and desired

> results

> > > given different parameters?
> > >
> > > [url]http://www.aspfaq.com/5006[/url]
> > >
> > > I like solving database problems, not word problems. ;-)
> > >
> > > --
> > > [url]http://www.aspfaq.com/[/url]
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > <jason@catamaranco.com> wrote in message
> > > news:#0#d3Q9fEHA.3632@TK2MSFTNGP11.phx.gbl...
> > > >
> > > > I have mastered creating simple Select procedure in sql server, but

> now

> > > need
> > > > to create a query that returns values for three parameters:

> > u_id(UserID),

> > > > BrokerID, SourceID
> > > >
> > > > If the parameters are null it should return all rows; if value

present

> > it

> > > > should return the target rows. This seems to indicate a Join Query

> > with

> > > > parameters...could someone offer advice on how to achieve this?
> > > >
> > > > Many thanks
> > > > Jason
> > > >
> > > >
> > > >
> > > > Primary Table Get User
> > > > Create Procedure sp_GetUser
> > > > @u_id int
> > > > As
> > > > set nocount on
> > > > select * from t_user where u_id=@u_id
> > > > return
> > > >
> > > > GO
> > > >
> > > >
> > > >
> > > >
> > > > Control Table 2: Get Source
> > > > CREATE Procedure spr_GetSource
> > > > @SourceID int
> > > > As
> > > > set nocount on
> > > > select * from Source where SourceID=@SourceID
> > > > return
> > > > GO
> > > >
> > > >
> > > >
> > > > Control Table 2: Get Broker
> > > > CREATE Procedure spr_GetBroker
> > > > @Brokerid int
> > > > As
> > > > set nocount on
> > > > select * from Broker where BrokerID=@BrokerID
> > > > return
> > > >
> > > > GO
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Aaron [SQL Server MVP]]

> But I cannot figure out how to get that extra inner join in there

What "extra inner join"? Your syntax looks correct, can you tell us the
actual problem? Are you getting an error message? If so, what is it? Are
you getting incorrect results? If so, what are they, and what do you think
they should be? It's great to see your stored procedure code over and over
again, but some other information might be useful also.

(In addition, could you trim your replies a bit?)

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

Sorry, I fought with it and realised a comma delimiter was the
problem....here is the full query which now works...I'll be more careful
about posting without more thorough testing:

CREATE Procedure spr_GetUserSelections
@u_ID int

As
set nocount on
Select *
FROM t_user
INNER JOIN Source ON
t_user.u_SourceID = Source.SourceID
INNER JOIN YachtPlacement ON
t_user.u_YachtPlacementID = YachtPlacement.YppID
INNER JOIN Broker ON
t_user.u_BrokerID = Broker.BrokerID
INNER JOIN State ON
t_user.u_StateID = State.StateID
INNER JOIN Nation ON
t_user.u_NationID = Nation.NationID
WHERE t_user.u_ID=@u