SQL Server 2000 SP

7 Jul 2005 5:02 PM

Dib

Hi,

I have this SP, I need to check for customer info in 2 tables, if table 1
does not contain the info, look in table 2.

what I have so far works only if the customer info exits in 2 tables or the
1 st table "tblArShipto"

I am calling the SP through VB 6.

Cn.CursorLocation = adUseServer 'Must use Server side cursor.
        sParmNameCust = "@CustId"
        Set cmdCust = New ADODB.Command
        Set cmdCust.ActiveConnection = Cn
        cmdCust.CommandType = adCmdStoredProc
        cmdCust.CommandText = "dbo.CorpCust_SP"
        cmdCust.Parameters(sParmNameCust).Value = ICustID
        Set rsCust = cmdCust.Execute

Any advice?

Thanks
Dib

ALTER       PROCEDURE dbo.CorpCust_SP
@CustID nVarchar(10)

AS

SELECT Cust.CustId, Cust.ShiptoName AS [CustName], Cust.Addr1, Cust.Addr2,
Cust.Addr3, Cust.City,
Cust.Region, Cust.PostalCode, Cust.Phone,Cust.cNotes AS [Notes]

FROM    dbo.tblArShipTo Cust

Where (Cust.CustId = @CustId) AND (Cust.ShipToId = 'MAIL') Order BY
Cust.CustID

IF @@ROWCOUNT = 0

begin

SELECT Cust.CustId, Cust.CustName, Cust.Addr1, Cust.Addr2, Cust.Addr3,
Cust.City,
Cust.Region, Cust.PostalCode, Cust.Phone,Cust.Notes

FROM    dbo.tblArCust Cust

Where (Cust.CustId = @CustId) Order BY Cust.CustID

end

7 Jul 2005 5:46 PM

John Bell

Hi

Apart from not needing to order these are you are only selecting one custid
value, you probably have the fastest solution. If you want to try it in a
single statement then you could union both select statements and add a where
clause to the second that eliminates custids that exist in you first table.

John

Show quote

"Dib" wrote:

> Hi,
>
> I have this SP, I need to check for customer info in 2 tables, if table 1
> does not contain the info, look in table 2.
>
> what I have so far works only if the customer info exits in 2 tables or the
> 1 st table "tblArShipto"
>
> I am calling the SP through VB 6.
>
> Cn.CursorLocation = adUseServer 'Must use Server side cursor.
>         sParmNameCust = "@CustId"
>         Set cmdCust = New ADODB.Command
>         Set cmdCust.ActiveConnection = Cn
>         cmdCust.CommandType = adCmdStoredProc
>         cmdCust.CommandText = "dbo.CorpCust_SP"
>         cmdCust.Parameters(sParmNameCust).Value = ICustID
>         Set rsCust = cmdCust.Execute
>
> Any advice?
>
> Thanks
> Dib
>
>
> ALTER       PROCEDURE dbo.CorpCust_SP
> @CustID nVarchar(10)
>
> AS
>
>
>
> SELECT Cust.CustId, Cust.ShiptoName AS [CustName], Cust.Addr1, Cust.Addr2,
> Cust.Addr3, Cust.City,
> Cust.Region, Cust.PostalCode, Cust.Phone,Cust.cNotes AS [Notes]
>
> FROM    dbo.tblArShipTo Cust
>
> Where (Cust.CustId = @CustId) AND (Cust.ShipToId = 'MAIL') Order BY
> Cust.CustID
>
> IF @@ROWCOUNT = 0
>
> begin
>
> SELECT Cust.CustId, Cust.CustName, Cust.Addr1, Cust.Addr2, Cust.Addr3,
> Cust.City,
> Cust.Region, Cust.PostalCode, Cust.Phone,Cust.Notes
>
> FROM    dbo.tblArCust Cust
>
> Where (Cust.CustId = @CustId) Order BY Cust.CustID
>
> end
>
>
>

7 Jul 2005 6:10 PM

Dib

Thanks
Dib

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:8889054B-F3F7-49C9-B713-FA5990B064E2@microsoft.com...
> Hi
>
> Apart from not needing to order these are you are only selecting one
custid
> value, you probably have the fastest solution. If you want to try it in a
> single statement then you could union both select statements and add a
where
> clause to the second that eliminates custids that exist in you first
table.
>
> John
>
>
> "Dib" wrote:
>
> > Hi,
> >
> > I have this SP, I need to check for customer info in 2 tables, if table
1
> > does not contain the info, look in table 2.
> >
> > what I have so far works only if the customer info exits in 2 tables or
the
> > 1 st table "tblArShipto"
> >
> > I am calling the SP through VB 6.
> >
> > Cn.CursorLocation = adUseServer 'Must use Server side cursor.
> >         sParmNameCust = "@CustId"
> >         Set cmdCust = New ADODB.Command
> >         Set cmdCust.ActiveConnection = Cn
> >         cmdCust.CommandType = adCmdStoredProc
> >         cmdCust.CommandText = "dbo.CorpCust_SP"
> >         cmdCust.Parameters(sParmNameCust).Value = ICustID
> >         Set rsCust = cmdCust.Execute
> >
> > Any advice?
> >
> > Thanks
> > Dib
> >
> >
> > ALTER       PROCEDURE dbo.CorpCust_SP
> > @CustID nVarchar(10)
> >
> > AS
> >
> >
> >
> > SELECT Cust.CustId, Cust.ShiptoName AS [CustName], Cust.Addr1,
Cust.Addr2,
> > Cust.Addr3, Cust.City,
> > Cust.Region, Cust.PostalCode, Cust.Phone,Cust.cNotes AS [Notes]
> >
> > FROM    dbo.tblArShipTo Cust
> >
> > Where (Cust.CustId = @CustId) AND (Cust.ShipToId = 'MAIL') Order BY
> > Cust.CustID
> >
> > IF @@ROWCOUNT = 0
> >
> > begin
> >
> > SELECT Cust.CustId, Cust.CustName, Cust.Addr1, Cust.Addr2, Cust.Addr3,
> > Cust.City,
> > Cust.Region, Cust.PostalCode, Cust.Phone,Cust.Notes
> >
> > FROM    dbo.tblArCust Cust
> >
> > Where (Cust.CustId = @CustId) Order BY Cust.CustID
> >
> > end
> >
> >
> >

7 Jul 2005 5:51 PM

Alejandro Mesa

Try,

ALTER       PROCEDURE dbo.CorpCust_SP
@CustID nVarchar(10)

AS

set nocount on

if exists (SELECT * FROM dbo.tblArShipTo Where (CustId = @CustId) AND
(ShipToId = 'MAIL'))

    SELECT
        Cust.CustId,
        Cust.ShiptoName AS [CustName],
        Cust.Addr1,
        Cust.Addr2,
        Cust.Addr3,
        Cust.City,
        Cust.Region,
        Cust.PostalCode,
        Cust.Phone,
        Cust.cNotes AS [Notes]
    FROM
        dbo.tblArShipTo Cust

    Where
        (Cust.CustId = @CustId)
        AND (Cust.ShipToId = 'MAIL')
    Order BY
        Cust.CustID

else

    SELECT
        Cust.CustId,
        Cust.CustName,
        Cust.Addr1,
        Cust.Addr2,
        Cust.Addr3,
        Cust.City,
        Cust.Region,
        Cust.PostalCode,
        Cust.Phone,
        Cust.Notes
    FROM
        dbo.tblArCust Cust
    Where
        (Cust.CustId = @CustId)
    Order BY
        Cust.CustID

return @@error
go

AMB

Show quote

"Dib" wrote:

> Hi,
>
> I have this SP, I need to check for customer info in 2 tables, if table 1
> does not contain the info, look in table 2.
>
> what I have so far works only if the customer info exits in 2 tables or the
> 1 st table "tblArShipto"
>
> I am calling the SP through VB 6.
>
> Cn.CursorLocation = adUseServer 'Must use Server side cursor.
>         sParmNameCust = "@CustId"
>         Set cmdCust = New ADODB.Command
>         Set cmdCust.ActiveConnection = Cn
>         cmdCust.CommandType = adCmdStoredProc
>         cmdCust.CommandText = "dbo.CorpCust_SP"
>         cmdCust.Parameters(sParmNameCust).Value = ICustID
>         Set rsCust = cmdCust.Execute
>
> Any advice?
>
> Thanks
> Dib
>
>
> ALTER       PROCEDURE dbo.CorpCust_SP
> @CustID nVarchar(10)
>
> AS
>
>
>
> SELECT Cust.CustId, Cust.ShiptoName AS [CustName], Cust.Addr1, Cust.Addr2,
> Cust.Addr3, Cust.City,
> Cust.Region, Cust.PostalCode, Cust.Phone,Cust.cNotes AS [Notes]
>
> FROM    dbo.tblArShipTo Cust
>
> Where (Cust.CustId = @CustId) AND (Cust.ShipToId = 'MAIL') Order BY
> Cust.CustID
>
> IF @@ROWCOUNT = 0
>
> begin
>
> SELECT Cust.CustId, Cust.CustName, Cust.Addr1, Cust.Addr2, Cust.Addr3,
> Cust.City,
> Cust.Region, Cust.PostalCode, Cust.Phone,Cust.Notes
>
> FROM    dbo.tblArCust Cust
>
> Where (Cust.CustId = @CustId) Order BY Cust.CustID
>
> end
>
>
>

7 Jul 2005 6:10 PM

Dib

Thanks
It works fine.

Dib

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:34DA943E-9445-4BBE-910F-01C26D55AB31@microsoft.com...
> Try,
>
> ALTER       PROCEDURE dbo.CorpCust_SP
> @CustID nVarchar(10)
>
> AS
>
> set nocount on
>
> if exists (SELECT * FROM dbo.tblArShipTo Where (CustId = @CustId) AND
> (ShipToId = 'MAIL'))
>
> SELECT
> Cust.CustId,
> Cust.ShiptoName AS [CustName],
> Cust.Addr1,
> Cust.Addr2,
> Cust.Addr3,
> Cust.City,
> Cust.Region,
> Cust.PostalCode,
> Cust.Phone,
> Cust.cNotes AS [Notes]
> FROM
> dbo.tblArShipTo Cust
>
> Where
> (Cust.CustId = @CustId)
> AND (Cust.ShipToId = 'MAIL')
> Order BY
> Cust.CustID
>
> else
>
> SELECT
> Cust.CustId,
> Cust.CustName,
> Cust.Addr1,
> Cust.Addr2,
> Cust.Addr3,
> Cust.City,
> Cust.Region,
> Cust.PostalCode,
> Cust.Phone,
> Cust.Notes
> FROM
> dbo.tblArCust Cust
> Where
> (Cust.CustId = @CustId)
> Order BY
> Cust.CustID
>
> return @@error
> go
>
>
> AMB
>
>
> "Dib" wrote:
>
> > Hi,
> >
> > I have this SP, I need to check for customer info in 2 tables, if table
1
> > does not contain the info, look in table 2.
> >
> > what I have so far works only if the customer info exits in 2 tables or
the
> > 1 st table "tblArShipto"
> >
> > I am calling the SP through VB 6.
> >
> > Cn.CursorLocation = adUseServer 'Must use Server side cursor.
> >         sParmNameCust = "@CustId"
> >         Set cmdCust = New ADODB.Command
> >         Set cmdCust.ActiveConnection = Cn
> >         cmdCust.CommandType = adCmdStoredProc
> >         cmdCust.CommandText = "dbo.CorpCust_SP"
> >         cmdCust.Parameters(sParmNameCust).Value = ICustID
> >         Set rsCust = cmdCust.Execute
> >
> > Any advice?
> >
> > Thanks
> > Dib
> >
> >
> > ALTER       PROCEDURE dbo.CorpCust_SP
> > @CustID nVarchar(10)
> >
> > AS
> >
> >
> >
> > SELECT Cust.CustId, Cust.ShiptoName AS [CustName], Cust.Addr1,
Cust.Addr2,
> > Cust.Addr3, Cust.City,
> > Cust.Region, Cust.PostalCode, Cust.Phone,Cust.cNotes AS [Notes]
> >
> > FROM    dbo.tblArShipTo Cust
> >
> > Where (Cust.CustId = @CustId) AND (Cust.ShipToId = 'MAIL') Order BY
> > Cust.CustID
> >
> > IF @@ROWCOUNT = 0
> >
> > begin
> >
> > SELECT Cust.CustId, Cust.CustName, Cust.Addr1, Cust.Addr2, Cust.Addr3,
> > Cust.City,
> > Cust.Region, Cust.PostalCode, Cust.Phone,Cust.Notes
> >
> > FROM    dbo.tblArCust Cust
> >
> > Where (Cust.CustId = @CustId) Order BY Cust.CustID
> >
> > end
> >
> >
> >