Multi-Values Input Parameter

Question

Post reply

Multi-Values Input Parameter

Ghanta

Hall of Fame

Points: 3999
More actions
September 21, 2010 at 12:23 pm

#238572

Hi Guys,
I am working on SQL Proc which will have multiple input parameters. Some of the input parameters can have more than one values seprated by "|". I was looking at bitwise operation and thinking that should handle that multi-valued parameters... anyone who has tackled such issue of handling multi-values input parameters, please help... I have done it using parse, but was checking if there is a way to do it using bitwise operation as the parameter list is big.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ghanta Hall of Fame Points: 3999 More actions · Answer 1

Anyone here has experience with bitwise operation? Thanks for the time.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

Can you post that sample code so that it would be bit easy to get through

Thanks

Parthi

Thanks
Parthi

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

Thanks for the response... I dont have code, but this is what I have to do...

Say I am createing a Proc with 5 input parameters

TestProc (@VendorName, @LoadDateStart, @LoadDateEnd, @FileType, @Database)

@VendorName, @FileType, @Database can have multiple values sent (probably separated by | or , or whatever we come with)

Is there a way do this by using bitwise operation? Or I should do using parsing and temp tables?

Thanks!

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Declare @VendorName1 Nvarchar(max), @FileType1 Nvarchar(max) , @Database1 Nvarchar(max)

Declare @VendorName Nvarchar(max), @FileType Nvarchar(max) , @Database Nvarchar(max)

set @VendorName1 ='Test1|Test2|Test3|Test4|'

set @FileType1 ='File1|File2|File3|File4|'

set @Database1 ='Database1|Database2|Database3|'

Select CHARINDEX('|', @VendorName1), len(@VendorName1)

While len(@VendorName1)>0

begin

Select @VendorName = substring(@VendorName1, 0,CHARINDEX('|', @VendorName1))

Select @VendorName1=substring(@VendorName1, CHARINDEX('|', @VendorName1)+1,LEN(@VendorName1))

Select @FileType = substring(@FileType1, 0,CHARINDEX('|', @FileType1))

Select @FileType1=substring(@FileType1, CHARINDEX('|', @FileType1)+1,LEN(@FileType1))

Select @Database = substring(@Database1, 0,CHARINDEX('|', @Database1))

Select @Database1=substring(@Database1, CHARINDEX('|', @Database1)+1,LEN(@Database1))

Select @VendorName,@FileType ,@Database

--<Taking one by one from now here you have to process your things >,

--<>,

--<>

end

go

I have kept @VendorName1 ,@FileType1 ,@Database1 as input change to your code

Thanks

Parthi

Thanks
Parthi

Ghanta Hall of Fame Points: 3999 More actions · Answer 5

Thanks Parthi this is what I had planned to do... I convinced my lead that we do not have to use bitwise... I am on the track now. Thanks again.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

Ghanta (9/22/2010)
Thanks Parthi this is what I had planned to do... I convinced my lead that we do not have to use bitwise... I am on the track now. Thanks again.

It was great my query has helped you.

My Suggestion when ever you post the development query give with example so that the person who reads can get better understanding,so that it may save your time.

Thanks

Parthi

Thanks
Parthi

Ghanta Hall of Fame Points: 3999 More actions · Answer 7

Ghanta

Hall of Fame

Points: 3999

September 22, 2010 at 2:09 pm

#1225930

Sure Parthi. Thanks!