I need help with Multiple Where and IF's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need help with Multiple Where and IF's

Author

Topic

Cwm
Starting Member

39 Posts

Posted - 2008-06-28 : 22:15:08

Hello Everyone,
I am having an issue with this stored procedure. In short what I am trying to do is pass about 27 comma delimited strings to about 27 parameters. the stored procedure runs fine but it does not insert anything into a table variable, I have tried different variances with the multiple Where clauses and If statements. What should be happening is that it should return all the delimited data when i select from the table variable, but return 0. Since it returns 0 i can't insert into a data table from the table variable and it just isn't working..Can someone see what I am doing wrong? thanks in advance

ALTER PROCEDURE Add$Data$From$Text$File
(
@gr1Arr varchar(5000),
@gr2Arr varchar(5000),
@gr3Arr varchar(5000),
@gr4Arr varchar(5000),
@gr5Arr varchar(5000),
@DateEnteredArr varchar(5000),
@DateToShipArr varchar(5000),
@DestinationNumberArr varchar(5000),
@CustomerNumberArr varchar(5000),
@OilTypeArr varchar(5000),
@gCommentArr varchar(5000),
@CommentLineArr varchar(5000),
@gPuLotArr varchar(5000),
@OrderNumberArr varchar(5000),
@LotnumberArr varchar(5000),
@gLotNumberArr varchar(5000),
@gOilInfoArr varchar(5000),
@CommodityNumberArr varchar(5000),
@OilNumberArr varchar(5000),
@OilDescriptionArr varchar(5000),
@gDestinationArr varchar(5000),
@gBolArr varchar(5000),
@BolNumberArr varchar(5000),
@gCarrierArr varchar(5000),
@gr13Arr varchar(5000),
@gr14Arr varchar(5000)
)
AS

declare @agr1 varchar(5000)
declare @agr1Len int
declare @agr1Temp varchar(5000)
declare @agr1Cnt int

declare @agr2 varchar(5000)
declare @agr2Len int
declare @agr2Cnt int
declare @agr2Temp varchar(5000)

declare @agr3 varchar(5000)
declare @agr3Len int
declare @agr3Cnt int
declare @agr3Temp varchar(5000)

declare @agr4 varchar(5000)
declare @agr4Len int
declare @agr4Cnt int
declare @agr4Temp varchar(5000)

declare @agr5 varchar(5000)
declare @agr5Len int
declare @agr5Cnt int
declare @agr5Temp varchar(5000)

declare @aDateEntered varchar(5000)
declare @aDateEnteredLen int
declare @aDateEnteredCnt int
declare @aDateEnteredTemp varchar(5000)

declare @aDateToShip varchar(5000)
declare @aDateToShipLen int
declare @aDateToShipCnt int
declare @aDateToShipTemp varchar(5000)

declare @aDestinationNumber varchar(5000)
declare @aDestinationNumberLen int
declare @aDestinationNumberCnt int
declare @aDestinationNumberTemp varchar(5000)

declare @aCustomerNumber varchar(5000)
declare @aCustomerNumberLen int
declare @aCustomerNumberCnt int
declare @aCustomerNumberTemp varchar(5000)

declare @aOilType varchar(5000)
declare @aOilTypeLen int
declare @aOilTypeCnt int
declare @aOilTypeTemp varchar(5000)

declare @agComment varchar(5000)
declare @agCommentLen int
declare @agCommentCnt int
declare @agCommentTemp varchar(5000)

declare @aCommentLine varchar(5000)
declare @aCommentLineLen int
declare @aCommentLineCnt int
declare @aCommentLineTemp varchar(5000)

declare @agPuLot varchar(5000)
declare @agPuLotLen int
declare @agPuLotCnt int
declare @agPuLotTemp varchar(5000)

declare @aOrderNumber varchar(5000)
declare @aOrderNumberLen int
declare @aOrderNumberCnt int
declare @aOrderNumberTemp varchar(5000)

declare @aLotNumber varchar(5000)
declare @aLotNumberLen int
declare @aLotNumberCnt int
declare @aLotNumberTemp varchar(5000)

declare @agLotNumber varchar(5000)
declare @agLotNumberLen int
declare @agLotNumberCnt int
declare @agLotNumberTemp varchar(5000)

declare @agOilInfo varchar(5000)
declare @agOilInfoLen int
declare @agOilInfoCnt int
declare @agOilInfoTemp varchar(5000)

declare @aCommodityNumber varchar(5000)
declare @aCommodityNumberLen int
declare @aCommodityNumberCnt int
declare @aCommodityNumberTemp varchar(5000)

declare @aOilNumber varchar(5000)
declare @aOilNumberLen int
declare @aOilNumberCnt int
declare @aOilNumberTemp varchar(5000)

declare @aOilDescription varchar(5000)
declare @aOilDescriptionLen int
declare @aOilDescriptionCnt int
declare @aOilDescriptionTemp varchar(5000)

declare @agDestination varchar(5000)
declare @agDestinationLen int
declare @agDestinationCnt int
declare @agDestinationTemp varchar(5000)

declare @agBol varchar(5000)
declare @agBolLen int
declare @agBolCnt int
declare @agBolTemp varchar(5000)

declare @aBolNumber varchar(5000)
declare @aBolNumberLen int
declare @aBolNumberCnt int
declare @aBolNumberTemp varchar(5000)

declare @agCarrier varchar(5000)
declare @agCarrierLen int
declare @agCarrierCnt int
declare @agCarrierTemp varchar(5000)

declare @agr13 varchar(5000)
declare @agr13Len int
declare @agr13Cnt int
declare @agr13Temp varchar(5000)

declare @agr14 varchar(5000)
declare @agr14Len int
declare @agr14Cnt int
declare @agr14Temp varchar(5000)

DECLARE @T TABLE
(
--Garbage VARCHAR(5000)
gr1 varchar(5000),
gr2 varchar (5000),
gr3 varchar(5000),
gr4 varchar(5000),
gr5 varchar(5000),
DateEntered varchar(5000),
DateToShip varchar(5000),
DestinationNumber varchar(5000),
CustomerNumber varchar(5000),
OilType varchar(5000),
gComment varchar(5000),
CommentLine varchar(5000),
gPuLot varchar(5000),
OrderNumber varchar(5000),
Lotnumber varchar(5000),
gLotNumber varchar(5000),
gOilInfo varchar(5000),
CommodityNumber varchar(5000),
OilNumber varchar(5000),
OilDescription varchar(5000),
gDestination varchar(5000),
gBol varchar(5000),
BolNumber varchar(5000),
gCarrier varchar(5000),
gr13 varchar(5000),
gr14 varchar(5000)
)

set @aDateEnteredLen = len(@DateEnteredArr)
set @aDateEnteredCnt = 1
set @aDateEntered = ''

set @aDateToShipLen = len(@DateToShipArr)
set @aDateToShipCnt = 1
set @aDateToShip = ''

set @aDestinationNumberLen = len(@DestinationNumberArr)
set @aDestinationNumberCnt = 1
set @aDestinationNumber = ''

set @aCustomerNumberLen = len(@CustomerNumberArr)
set @aCustomerNumberCnt = 1
set @aCustomerNumber = ''

set @aOilTypeLen = len(@OilTypeArr)
set @aOilTypeCnt = 1
set @aOilType = ''

set @agCommentLen = len(@gCommentArr)
set @agCommentCnt = 1
set @agComment = ''

set @aCommentLineLen = len(@CommentLineArr)
set @aCommentLineCnt = 1
set @aCommentLine = ''

set @agPuLotLen = len(@gPuLotArr)
set @agPuLotCnt = 1
set @agPuLot = ''

set @aOrderNumberLen = len(@OrderNumberArr)
set @aOrderNumberCnt = 1
set @aOrderNumber = ''

set @aLotNumberLen = len(@LotNumberArr)
set @aLotNumberCnt = 1
set @aLotNumber = ''

set @agLotNumberLen = len(@gLotNumberArr)
set @agLotNumberCnt = 1
set @agLotNumber = ''

set @agOilInfoLen = len(@gOilInfoArr)
set @agOilInfoCnt = 1
set @agOilInfo = ''

set @aCommodityNumberLen = len(@CommodityNumberArr)
set @aCommodityNumberCnt = 1
set @aCommodityNumber = ''

set @aOilNumberLen = len(@OilNumberArr)
set @aOilNumberCnt = 1
set @aOilNumber = ''

set @aOilDescriptionLen = len(@OilDescriptionArr)
set @aOilDescriptionCnt = 1
set @aOilDescription = ''

set @agDestinationLen = len(@gDestinationArr)
set @agDestinationCnt = 1
set @agDestination = ''

set @agBolLen = len(@gBolArr)
set @agBolCnt = 1
set @agBol = ''

set @aBolNumberLen = len(@BolNumberArr)
set @aBolNumberCnt = 1
set @aBolNumber = ''

set @agCarrierLen = len(@gCarrierArr)
set @agCarrierCnt = 1
set @agCarrier = ''

while(@agr1Cnt <= @agr1Len) and (@agr2Cnt <= @agr2Len) and (@agr3Cnt <= @agr3Len) and (@agr4Cnt <= @agr4Len) and
(@agr5Cnt <= @agr5Len) and (@aDateEnteredCnt <= @aDateEnteredLen) and (@aDateToShipCnt <= @aDateToShipLen) and
(@aDestinationNumberCnt <= @aDestinationNumberLen) and (@aCustomerNumberCnt <= @aCustomerNumberLen) and
(@aOilTypeCnt <= @aOilTypeLen) and (@agCommentCnt <= @agCommentLen) and (@aCommentLineCnt <= @aCommentLineLen) and
(@agPuLotCnt <= @agPuLotLen) and (@aOrderNumberCnt <= @aOrderNumberLen) and (@aLotNumberCnt <= @aLotNumberLen) and
(@agLotNumberCnt <= @agLotNumberLen) and (@agOilInfoCnt <= @agOilInfoLen) and (@aCommodityNumberCnt <= @aCommodityNumberLen) and
(@aOilNumberCnt <= @aOilNumberLen) and (@agDestinationCnt <= @agDestinationLen) and (@aOilDescriptionCnt <= @aOilDescriptionLen) and
(@agBolCnt <= @agBolLen) and (@aBolNumberCnt <= @aBolNumberLen) and (@agCarrierCnt <= @agCarrierLen) and
(@agr13Cnt <= @agr13Len) and (@agr14Cnt <= @agr14Len)

begin

set @agr1Temp = substring(@gr1Arr, @agr1Cnt, 1)
set @agr2Temp = substring(@gr2Arr, @agr2Cnt, 1)
set @agr3Temp = substring(@gr3Arr, @agr3Cnt, 1)
set @agr4Temp = substring(@gr4Arr, @agr4Cnt, 1)
set @agr5Temp = substring(@gr5Arr, @agr5Cnt, 1)
set @aDateEnteredTemp = substring(@DateEnteredArr, @aDateEnteredCnt, 1)
set @aDateToShipTemp = substring(@DateToShipArr, @aDateToShipCnt,1)
set @agDestinationTemp = substring(@DestinationNumberArr, @aDestinationNumberCnt, 1)
set @aCustomerNumberTemp = substring(@CustomerNumberArr, @aCustomerNumberCnt, 1)
set @aOilTypeTemp = substring(@OilTypeArr, @aOilTypeCnt, 1)
set @agCommentTemp = substring(@gCommentArr, @agCommentCnt, 1)
set @aCommentLineTemp = substring(@CommentLineArr, @aCommentLineCnt, 1)
set @agPuLotTemp = substring(@gPuLotArr, @agPuLotCnt, 1)
set @aOrderNumberTemp = substring(@OrderNumberArr, @aOrderNumberCnt, 1)
set @aLotNumberTemp = substring(@LotNumberArr, @aLotNumberCnt, 1)
set @agLotNumberTemp = substring(@gLotNumberArr, @agLotNumberCnt, 1)
set @agOilInfoTemp = substring(@gOilInfoArr, @agOilInfoCnt, 1)
set @aCommodityNumberTemp = substring(@CommodityNumberArr, @aCommodityNumberCnt, 1)
set @aOilNumberTemp = substring(@OilNumberArr, @aOilNumberCnt, 1)
set @agDestinationTemp = substring(@gDestinationArr, @agDestinationCnt, 1)
set @aOilDescriptionTemp = substring(@OilDescriptionArr, @aOilDescriptionCnt, 1)
set @agBolTemp = substring(@gBolArr, @agBolCnt, 1)
set @aBolNumberTemp = substring(@BolNumberArr, @aBolNumberCnt, 1)
set @agCarrierTemp = substring(@gCarrierArr, @agCarrierCnt, 1)
set @agr13Temp = substring(@gr13Arr, @agr13Cnt, 1)
set @agr14Temp = substring(@gr14Arr, @agr14Cnt, 1)

----------------
if(@agr1Temp = ',') and (@agr2Temp = ',') and
(@agr3Temp = ',') and (@agr4Temp = ',') and
(@agr5Temp = ',') and (@aDateEnteredTemp = ',') and
(@aDateToShipTemp = ',') and (@agDestinationTemp = ',') and
(@aCustomerNumberTemp = ',') and (@aOilTypeTemp = ',') and
(@agCommentTemp = ',') and (@aCommentLineTemp = ',') and
(@agPuLotTemp = ',') and (@aOrderNumberTemp = ',') and
(@aLotNumberTemp = ',') and (@agLotNumberTemp = ',') and
(@agOilInfoTemp = ',') and (@aCommodityNumberTemp = ',') and
(@aOilNumberTemp = ',') and (@agDestinationTemp = ',') and
(@aOilDescriptionTemp = ',') and (@agBolTemp = ',') and
(@aBolNumberTemp = ',') and (@agCarrierTemp = ',') and
(@agr13Temp = ',') and (@agr14Temp = ',')

begin
insert @T (gr1,
gr2,
gr3,
gr4,
gr5,
DateEntered,
DateToShip,
DestinationNumber,
CustomerNumber,
OilType,
gComment,
CommentLine,
gPuLot,
OrderNumber,
Lotnumber,
gLotNumber,
gOilInfo,
CommodityNumber,
OilNumber,
OilDescription,
gDestination,
gBol,
BolNumber,
gCarrier,
gr13,
gr14)
values (@agr1,
@agr2,
@agr3,
@agr4,
@agr5,
@aDateEntered,
@aDateToShip,
@aDestinationNumber,
@aCustomerNumber,
@aOilType,
@agComment,
@aCommentLine,
@agPuLot,
@aOrderNumber,
@aLotnumber,
@agLotNumber,
@agOilInfo,
@aCommodityNumber,
@aOilNumber,
@aOilDescription,
@agDestination,
@agBol,
@aBolNumber,
@agCarrier,
@agr13,
@agr14)

set @agr1 = ''
set @agr2 = ''
set @agr3 = ''
set @agr4 = ''
set @agr5 = ''
set @aDateEntered = ''
set @aDateToShip = ''
set @agDestination = ''
set @aCustomerNumber = ''
set @aOilType = ''
set @agComment = ''
set @aCommentLine = ''
set @agPuLot = ''
set @aOrderNumber = ''
set @aLotNumber = ''
set @agLotNumber = ''
set @agOilInfo = ''
set @aCommodityNumber = ''
set @aOilNumber = ''
set @agDestination = ''
set @aOilDescription = ''
set @agBol = ''
set @aBolNumber = ''
set @agCarrier = ''
set @agr13 = ''
set @agr14 = ''
end

if( @agr1Temp <> ',' and @agr2Temp <> ',' and
@agr3Temp <> ',' and @agr4Temp <> ',' and
@agr5Temp <> ',' and @aDateEnteredTemp <> ',' and
@aDateToShipTemp <> ',' and @agDestinationTemp <> ',' and
@aCustomerNumberTemp <> ',' and @aOilTypeTemp <> ',' and
@agCommentTemp <> ',' and @aCommentLineTemp <> ',' and
@agPuLotTemp <> ',' and @aOrderNumberTemp <> ',' and
@aLotNumberTemp <> ',' and @agLotNumberTemp <> ',' and
@agOilInfoTemp <> ',' and @aCommodityNumberTemp <> ',' and
@aOilNumberTemp <> ',' and @agDestinationTemp <> ',' and
@aOilDescriptionTemp <> ',' and @agBolTemp <> ',' and
@aBolNumberTemp <> ',' and @agCarrierTemp <> ',' and
@agr13Temp <> ',' and @agr14Temp <> ',')

begin
set @agr1 = @agr1 + @agr1Temp
set @agr2 = @agr2 + @agr2Temp
set @agr3 = @agr3 + @agr3Temp
set @agr4 = @agr4 + @agr4Temp
set @agr5 = @agr5 + @agr5Temp
set @aDateEntered = @aDateEntered + @aDateEnteredTemp
set @aDateToShip = @aDateToShip + @aDateToShipTemp
set @agDestination = @agDestination + @agDestinationTemp
set @aCustomerNumber = @aCustomerNumber + @aCustomerNumberTemp
set @aOilType = @aOilType + @aOilTypeTemp
set @agComment = @agComment + @agCommentTemp
set @aCommentLine = @aCommentLine + @aCommentLineTemp
set @agPuLot = @agPuLot + @agPuLotTemp
set @aOrderNumber = @aOrderNumber + @aOrderNumberTemp
set @aLotNumber = @aLotNumber + @aLotNumberTemp
set @agLotNumber = @agLotNumber + @agLotNumberTemp
set @agOilInfo = @agOilInfo + @agOilInfoTemp
set @aCommodityNumber = @aCommodityNumber + @aCommodityNumberTemp
set @aOilNumber = @aOilNumber + @aOilNumberTemp
set @agDestination = @agDestination + @agDestinationTemp
set @aOilDescription = @aOilDescription + @aOilDescriptionTemp
set @agBol = @agBol + @agBolTemp
set @aBolNumber = @aBolNumber + @aBolNumberTemp
set @agCarrier = @agCarrier + @agCarrierTemp
set @agr13 = @agr13 + @agr13Temp
set @agr14 = @agr14 + @agr14Temp

end
set @agr1Cnt = @agr1Cnt + 1
set @agr2Cnt = @agr2Cnt + 1
set @agr3Cnt = @agr3Cnt + 1
set @agr4Cnt = @agr4Cnt + 1
set @agr5Cnt = @agr5Cnt + 1
set @aDateEnteredCnt = @aDateEnteredCnt + 1
set @aDateToShipCnt = @aDateToShipCnt + 1
set @agDestinationCnt = @agDestinationCnt + 1
set @aCustomerNumberCnt = @aCustomerNumberCnt + 1
set @aOilTypeCnt = @aOilTypeCnt + 1
set @agCommentCnt = @agCommentCnt + 1
set @aCommentLineCnt = @aCommentLineCnt + 1
set @agPuLotCnt = @agPuLotCnt + 1
set @aOrderNumberCnt = @aOrderNumberCnt + 1
set @aLotNumberCnt = @aLotNumberCnt + 1
set @agLotNumberCnt = @agLotNumberCnt + 1
set @agOilInfoCnt = @agOilInfoCnt + 1
set @aCommodityNumberCnt = @aCommodityNumberCnt + 1
set @aOilNumberCnt = @aOilNumberCnt + 1
set @agDestinationCnt = @agDestinationCnt + 1
set @aOilDescriptionCnt = @aOilDescriptionCnt + 1
set @agBolCnt = @agBolCnt + 1
set @aBolNumberCnt = @aBolNumberCnt + 1
set @agCarrierCnt = @agCarrierCnt + 1
set @agr13Cnt = @agr13Cnt + 1
set @agr14Cnt = @agr14Cnt + 1

end

select * from @T

RETURN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 02:27:49

It would be better if you can explain what you're trying to achieve with some sample data and your reqd output. Its much difficult to read and understand from code what you're trying to do.

Cwm
Starting Member

39 Posts

Posted - 2008-06-29 : 12:52:01

Hi,
This is what i am trying to do...pass 27 comma delimitd strings to a stored procedure that has 27 parameters to accept each of the comma delimited strings. Each parameter will get this kind of data for test purposes... @g1Arr will be passed 1,2,3 as a string same as all the other parameters then what i am trying to do is pick off each character ( or data ) which is seperated by a comma. Then once that is achieved it gets inserted into a table variable. Then once all the parameters data has been passed into the table variables, it will be inserted into a normal database table with a subselect from the table variable. If i could figure out how to debug the stored procedure in sqlexpress i would but apparently you can't in sqlexpress.

Thanks
Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 13:21:42

quote:
Originally posted by Cwm

Hi,
This is what i am trying to do...pass 27 comma delimitd strings to a stored procedure that has 27 parameters to accept each of the comma delimited strings. Each parameter will get this kind of data for test purposes... @g1Arr will be passed 1,2,3 as a string same as all the other parameters then what i am trying to do is pick off each character ( or data ) which is seperated by a comma. Then once that is achieved it gets inserted into a table variable. Then once all the parameters data has been passed into the table variables, it will be inserted into a normal database table with a subselect from the table variable. If i could figure out how to debug the stored procedure in sqlexpress i would but apparently you can't in sqlexpress.

Thanks
Chris

so what you're basically trying is to filter some results from table(s) based on comma delimited value passed for 27 params and put it onto another table? also where's sample data from your table and output you require out of them as requested?

Cwm
Starting Member

39 Posts

Posted - 2008-06-29 : 17:11:10

Hi, what I am doing is...I got this flat file that I have delimited by comma. This is not a csv file. Sometimes there are multiple orders in this flat file. So what I do is read in the flatfile and use a bunch of substrings to extract the data basedon positions because each line can multiple data elements, so what i do is take those elements and put them into variables, then once that is done i open a connection and pass all my variables to the stored procedure. In brief the output should be..based on a test of passing 1,2,3
gr1 gr2 gr3 gr4 etc....
1 1 1 1
2 2 2 2
3 3 3 3

that is what should be sitting in the @T table. Then when i go to insert the data to my table the statement is

insert into [mytable]
(g1, g2, g3, g4)
select @gr1, @gr2, @gr3, @gr4 from @T

but as for testing purposes at the end I do
select * from @T

to see if there is data in the @T table. But there isn't, in the output i get this

gr1 gr2 gr3 gr4

no rows returned.

basically what really need to know is the parts where i have the WHERE clauses and the multiple IF statements if they are used correctly or how i am supposed to use multiple WHERE clauses and IF statements.
If you copy all the sp into sql server management studio and run it and pass this data to all of it (1,2,3) and you will see that it returns no rows I can also post the script for the table if need be.

Thanks

Chris

Cwm
Starting Member

39 Posts

Posted - 2008-06-29 : 17:39:08

Sorry I just looked at my sp and its not where clauses its while clauses and if statements

Cwm
Starting Member

39 Posts

Posted - 2008-06-29 : 18:56:34

I have it all figured out now. I rewrote the sp and kept adding variables to it and it keeps working.

Thanks for the replies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources