Optimize SQL

Hello All!
Is there a way to write this SQL without a UNION, because where i have to
set one field to null to match the number of field of the second sql :(
SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
AgenciaID, 'Caixa' AS Tipo
FROM Caixa
UNION
SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
Agencia.ID AS AgenciaID, 'Banco' AS Tipo
FROM Agencia JOIN
BANCO ON Banco.ID = Agencia.ID
Thank you all!!!
Bruno NThere probably are other solutions but if you want the same columns in
there then you will presumably have to populate the AgenciaID column
with NULLs anyway. I'm not sure what it is you want to do differently.
You should consider using UNION ALL instead of UNION unless it's
required to eliminate duplicates. UNION ALL will typically perform
better.
If you need more help, please post some more info as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Nope, unless you want to return 2 separate result sets. Then you can do
without the Tipo column as well.
There's nothing wrong really with setting one column to NULL if it doesn't
exist in that part of the unioned set.
Jacco Schalkwijk
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>|||Thanks guys!!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>

operations through stored procedures

i write a following stored procedure

create proc prc_BUSINESS_MASTER
(
@.chByMode Char(1),
@.intLIST_ID int=null,
@.intLISTBUSINESSID int=null,
@.dtmDATE datetime=null,
@.dtmEXPDATE datetime=null,
@.vchSTATUS varchar(10)=null,
@.vchOFFICE varchar(100)=null,
@.numCITY numeric =null,
@.numCOUNTY numeric=null,
@.numSTATE numeric=null,
@.numCOUNTRY numeric=null,
@.vchSICCODE varchar(50)=null,
@.numASKINGPRICE numeric =null,
@.numDOWNPRICE numeric=null,
@.bitOWNERFINANCE bit=null,
@.numOWNERFINANCE numeric=null,
@.numADJ_NETAMOUNT numeric=null,
@.numSALESAMOUNT numeric=null,
@.dtmYOE datetime=null,
@.dtmYRSOWNED datetime=null,
@.bitBITISACTIVE bit=null,
@.vchCREATEDBY varchar(50)=null,
@.dtmCREATEDDATE datetime=null,
@.vchUPDATEDBY varchar(50)=null,
@.dtmUPDATEDDATE datetime=null

)

as

set nocount on
declare @.byIntErrDescOut int
declare @.interrno int
declare @.intdupchk int
begin

IF @.chByMode NOT IN ('I','U')
BEGIN
SET @.byIntErrDescOut = 1
Return -1
END
SET @.byIntErrDescOut = 0
/* *************** Insertion Area *************** */

IF @.chByMode = 'I'
BEGIN

IF @.vchSTATUS IS NULL OR @.vchSTATUS = ''
BEGIN
SET @.byIntErrDescOut = 1
Return -1
END

/***** Duplicate checking ****/

SELECT @.intDupChk = COUNT(1) FROM BUSINESSMASTER WHEREvchLIST_STATUS=@.vchSTATUS AndvchLIST_OFFICE=@.vchOFFICE
IF @.intDupChk <> 0
BEGIN
SET @.byintErrDescOut = 5
Return -1
END

insert into BUSINESSMASTER
(

intLIST_ID,
dtmLIST_DATE,
dtmLIST_EXPDATE,
vchLIST_STATUS,
vchLIST_OFFICE,
numLIST_CITY,
numLIST_COUNTY,
numLIST_STATE,
numLIST_COUNTRY,
vchLIST_SICCODE,
numLIST_ASKINGPRICE,
numLIST_DOWNPRICE,
bitLIST_OWNERFINANCE,
numLIST_OWNERFINACE,
numLIST_ADJ_NETAMOUNT,
numLIST_SALESAMOUNT,
dtmLIST_YOE,
dtmLIST_YRSOWNED,
bitBITISACTIVE,
vchCREATEDBY,
dtmCREATEDDATE,
vchUPDATEDBY,
dtmUPDATEDDATE

)
values
(
@.intLIST_ID,
@.dtmDATE,
@.dtmEXPDATE,
@.vchSTATUS,
@.vchOFFICE,
@.numCITY,
@.numCOUNTY,
@.numSTATE,
@.numCOUNTRY,
@.vchSICCODE,
@.numASKINGPRICE,
@.numDOWNPRICE,
@.numOWNERFINANCE,
@.bitOWNERFINANCE,
@.numADJ_NETAMOUNT,
@.numSALESAMOUNT,
@.dtmYOE,
@.dtmYRSOWNED,
@.bitBITISACTIVE,
@.vchCREATEDBY,
@.dtmCREATEDDATE,
@.vchUPDATEDBY,
@.dtmUPDATEDDATE

)

SET @.intErrNo = @.@.Error
IF (@.intErrNo <> 0)
BEGIN
SET @.byIntErrDescOut = 2
Return -1
END

/*Insertion Area End*/

/*---Updation----*/

/*
IF @.chbyMode = 'U'
BEGIN
--IF @.intBuyerId IS NULL
BEGIN
SET @.byIntErrDescOut = 1
Return -1
END

*/

IF (EXISTS (SELECT * FROM BUSINESSMASTER
WHERE intLISTBUSINESSID = @.intLISTBUSINESSID ))
BEGIN

/***** Duplicate checking ****/

SELECT @.intDupChk = COUNT(1) FROM BUSINESSMASTER
WHERE intLISTBUSINESSID = @.intLISTBUSINESSID andvchLIST_STATUS=@.vchSTATUS
IF @.intDupChk <> 0
BEGIN
SET @.byintErrDescOut = 5
Return -1
END

UPDATE BUSINESSMASTER
SET
intLIST_ID=@.intLIST_ID,
dtmLIST_DATE=@.dtmDATE,
dtmLIST_EXPDATE=@.dtmEXPDATE,
vchLIST_STATUS=@.vchSTATUS,
vchLIST_OFFICE=@.vchOFFICE,
numLIST_CITY=@.numCITY,
numLIST_COUNTY=@.numCOUNTY,
numLIST_STATE=@.numSTATE,
numLIST_COUNTRY=@.numCOUNTRY,
vchLIST_SICCODE=@.vchSICCODE,
numLIST_ASKINGPRICE=@.numASKINGPRICE,
numLIST_DOWNPRICE=@.numDOWNPRICE,
bitLIST_OWNERFINANCE=@.bitOWNERFINANCE,
numLIST_OWNERFINACE=@.numOWNERFINANCE,
numLIST_ADJ_NETAMOUNT=@.numADJ_NETAMOUNT,
numLIST_SALESAMOUNT=@.numSALESAMOUNT,
dtmLIST_YOE=@.dtmYOE,
dtmLIST_YRSOWNED=@.dtmYRSOWNED,
bitBITISACTIVE=@.bitBITISACTIVE,
vchCREATEDBY=@.vchCREATEDBY,
dtmCREATEDDATE=@.dtmCREATEDDATE,
vchUPDATEDBY=@.vchUPDATEDBY,
dtmUPDATEDDATE=@.dtmUPDATEDDATE
WHERE intLISTBUSINESSID = @.intLISTBUSINESSID

-- AND WhenUpdated = @.ptmsUpdated
SET @.intErrNo = @.@.Error

IF (@.intErrNo <> 0)
BEGIN
SET @.byIntErrDescOut = 4
Return -1
END

END
ELSE
BEGIN
SET @.byIntErrDescOut = 4
Return -1
END

END
End

How can i check this procedure by performing insert ,update operations?

ie., how can i passing and updating informations through stored procedures?

Explain me in detail...

If you want to test it from the sql server environment, highlight the procedure name in the sql server query studio/management studio and right click it.

The menu will give you an option to execute the stored procedure. Choose it and it will build a script to execute the procedure for you.

All you have to do is supply sample values.

|||

I glanced at your procedure and noticed a few things about it:

The visual indentation of the code is horrible. I hope the copy/paste process lost that, because the logic control flow of the program is much harder to follow than it would be with proper indentation.