How to parse parameters

[mbusa]

I have a stored procedure which needs 2 parameters: Status and Substatus
The parameters are passed from a webpage where a user can choose from the available status/substatus
e.g
if a user chose status 10 substatus 20 , the string to be executed will be

execute rcp_FL_CRPReferenceActivityReport "10,20"

now if a user chooses two status and substatus , status 10 substatus 20 & status 11 substatus 21 then the string to be executed will be

execute rcp_FL_CRPReferenceActivityReport "10,20,11,21"

How do I set up the stored procedure to recognize the incoming string that the 1st value is a status and next is substatus.

in this above case i want the @Where in the stored procedure to be
(vFL_CRPCustomerReferenceActivity.iStatusId=10 and vFL_CRPCustomerReferenceActivity.iStatusSubTypeId=20) OR
(vFL_CRPCustomerReferenceActivity.iStatusId=11 and vFL_CRPCustomerReferenceActivity.iStatusSubTypeId=21)


below is the code for the stored procedure.
------------------------------------


CREATE PROCEDURE rcp_FL_CRPReferenceActivityReport
@status varchar(255),
@substatus varchar(255) = null
AS

BEGIN
SET NOCOUNT ON

DECLARE @WHERE VARCHAR(8000)
DECLARE @SQLSTMT VARCHAR(8000)

SET @SQLSTMT ='
Insert into FL_CRPReferenceActivityReport
(CompId,
CompName,
City,
State,
CompanyType,
CompanySubType,
SIC,
SalesChannel,
RefType,
Status,
StatusSubType,
InsertDate,
CloseDate
)
Select Distinct
vCompany.iCompanyId,
vCompany.vchCompanyName,
vCompany.vchCity,
(select vRegion.chRegionName
from vRegion
where vRegion.chregioncode = vCompany.chregioncode
and vRegion.chcountrycode = vCompany.chcountrycode),
(select vReferenceParameters.vchparameterdesc
from vReferenceParameters
where vCompany.iCompanyTypeCode = vReferenceParameters.iParameterId),
(select vReferenceParameters.vchparameterdesc
from vReferenceParameters
where vCompany.iCompanySubTypeCode = vReferenceParameters.iParameterId),
(select vReferenceParameters.vchparameterdesc
from vReferenceParameters
where vCompany.imarketsector = vReferenceParameters.iParameterId),
(select vReferenceParameters.vchparameterdesc
from vReferenceParameters
where vCompany.vchuser8 = vReferenceParameters.iParameterId),
(select vchparameterdesc
from vreferenceparameters
where vreferenceparameters.iparameterid = vFL_CRPCustomerReferenceActivity.iRefTypeId),
(select vchparameterdesc
from vreferenceparameters
where vreferenceparameters.iparameterid = vFL_CRPCustomerReferenceActivity.iStatusId),
(select vchparameterdesc
from vreferenceparameters
where vreferenceparameters.iparameterid = vFL_CRPCustomerReferenceActivity.iStatusSubTypeId),
vFL_CRPCustomerReferenceActivity.dtInsertDate,
vFL_CRPCustomerReferenceActivity.dtActivityCloseDate
From
vCompany
Inner Join vFL_CRPCustomerReference
on vCompany.iCompanyId = vFL_CRPCustomerReference.iOwnerId
Inner Join vFL_CRPCustomerReferenceActivity
on vFL_CRPCustomerReference.iCustRefId = vFL_CRPCustomerReferenceActivity.iCustRefId
where'
END



SET @WHERE = ' vFL_CRPCustomerReferenceActivity.iStatusId IN (' + @status + ') '


IF Len(@substatus) > 0
BEGIN
SET @WHERE = @WHERE + 'AND vFL_CRPCustomerReferenceActivity.iStatusSubTypeId IN (' + @substatus + ') '
END


SET @SQLSTMT = @SQLSTMT+@WHERE
DELETE FROM FL_CRPReferenceActivityReport

Exec(@SQLSTMT)



--END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[Catadmin]

If there are never going to be more than 2 sets of Status/Substatus, simply create the variables @Status1, @SubStatus1, @Status2 and @Substatus2. Put them as input accordingly on the Proc.

Then run your code a second time if @Status2 and @SubStatus2 > 0 and are Not NULL.

That would be the simplest solution to your problem.

The other solution I know of involves coding UDFs that would parse the incoming string as a psuedo-array and gets very messy.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mbusa]

thanx for the response
There are going to be more than 2 sets of status and substatus, and I cant change the way the parameters are passed so I have to find a way to change the stored procedure to accomodate this.

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[NoCoolHandle]

In the case of your execute statments

execute rcp_FL_CRPReferenceActivityReport "10,20,11,21"
and
execute rcp_FL_CRPReferenceActivityReport "10,20"

You are actually only sending SQL a single parameter string parameter..

Your proc is defined with 2 string parameters..
You need to call it correctly to get it to work or change your parameters..

Ie.
execute rcp_FL_CRPReferenceActivityReport "10","20"
and
execute rcp_FL_CRPReferenceActivityReport "10","20"
execute rcp_FL_CRPReferenceActivityReport "11","21"

Should work for your proc (more likley than not, but I can't really see any of your webpage code so don't want to say that with 100% guarantee).

Re sending sql a string array of parameters, check the faq's there are a couple of good ones there.

HTH


Rob

 

[mbusa]

hey Rob, Sorry i should have made it clear that the proc is set up for 2 paramaters right now but I need to change that . because I cant change the string that is passing the parameters.

The following the code I have for now which is working somewhat but needs to be fixed in some places.


---------------


declare @values varchar(255)
select @values = '328,null,329,106006,329,106007'
--select @values = '328,null,329,106006'
--select @values = 'null'



declare @vchSingleValue varchar(50)
declare @vchNewSingleValue varchar(50)
declare @vchParentValue varchar(50)
declare @vchChildValue varchar(50)
declare @vchNullValue varchar(10)
declare @iPosition int
declare @iLength int
declare @vchSQL varchar(500)

select @iLength = datalength(@values)
select @iPosition = 1
select @vchSQL = 'and ('
select @vchSingleValue = ''
select @vchNewSingleValue = ''
select @vchParentValue = ''
select @vchChildValue = ''

while @iLength > 0
and isnull(@iPosition,0) > 0
begin
-- get the value
select @iPosition = charindex(',',@values)
select @vchSingleValue = substring(@values,1,(@iPosition - 1))

select @vchNewSingleValue = case @vchSingleValue
when 'NULL' then NULL
when '' then NULL
else @vchSingleValue end

select @vchSingleValue = @vchNewSingleValue


-- is this a parent value
if convert(int,isnull(@vchSingleValue,0)) in (select rd.iParameterId from ReferenceFields rf
inner join ReferenceDefinition rd on rf.iReferenceId = rd.iReferenceId
where rf.chFieldName = 'customerReference.status' and rd.tiRecordStatus = 1)
begin
if isnull(@vchParentValue,'') <> ''
begin
-- build the sql before it's overwritten
if isnull(@vchNullValue ,'') <> ''
and isnull(@vchSingleValue,'') <> isnull(@vchParentValue,'')
begin
if isnull(@vchChildValue,0) <> 0
begin
select @vchSQL = @vchSQL + '(iStatusId = ' + @vchParentValue +
' and (iStatusSubType is null
or iStatusSubType in (' + ltrim(rtrim(substring(@vchChildValue,1,datalength(@vchChildValue)-1))) + ')))'
end
else
begin
select @vchSQL = @vchSQL + '(iStatusId = ' + @vchParentValue +
' and (iStatusSubType is null))'

end
end
else
begin
select @vchSQL = @vchSQL + '(iStatusId = ' + @vchParentValue +
' and iStatusSubType in (' + ltrim(rtrim(substring(@vchChildValue,1,datalength(@vchChildValue)-1))) + '))'
end

select @vchParentValue = ''
select @vchChildValue = ''
select @vchNullValue = ''
end
else
begin
select @vchParentValue = ltrim(rtrim(@vchSingleValue))
end
end
else
begin
if @vchSingleValue is null
begin
select @vchNullValue = 'NULL'
end
else
begin
select @vchChildValue = @vchChildValue + ltrim(rtrim(@vchSingleValue)) + ','
end
end

select @values = substring(@values,@iPosition + 1, @iLength - @iPosition)
select @iLength = datalength(@values)
select @iPosition = charindex(',',@values)
end

select @vchSingleValue = @values

select @vchNewSingleValue = case @vchSingleValue
when 'NULL' then NULL
when '' then NULL
else @vchSingleValue end

select @vchSingleValue = @vchNewSingleValue

if @vchSingleValue is null
begin
select @vchNullValue = 'NULL'
end
else
begin
select @vchChildValue = @vchChildValue + ltrim(rtrim(@vchSingleValue))
end

if isnull(@vchNullValue ,'') <> ''
and isnull(@vchSingleValue,'') <> isnull(@vchParentValue,'')
begin
if isnull(@vchChildValue,0) <> 0
begin
select @vchSQL = @vchSQL + '(iStatusId = ' + @vchParentValue +
' and (iStatusSubType is null
or iStatusSubType in (' + ltrim(rtrim(substring(@vchChildValue,1,datalength(@vchChildValue)-1))) + ')))'
end
else
begin
select @vchSQL = @vchSQL + '(iStatusId = ' + @vchParentValue +
' and (iStatusSubType is null))'

end
end
else
begin
select @vchSQL = @vchSQL + '(iStatusId = ' + @vchParentValue +
' and iStatusSubType in (' + ltrim(rtrim(@vchChildValue)) + '))'
end

print @vchSQL

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[gmmastros]

Here's a solution for you. It should work quite well.

Step 1, create a UDF to split strings. I did not create this function.

ALTER  FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint

IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
	WHILE @bcontinue = 1
		BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
			SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			
--Trim the element and its delimiter from the front of the string.
			--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
			SET @idx = @idx + 1
			SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
		
			END
		ELSE
			BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			--Exit the WHILE loop.
SET @bcontinue = 0
			END
		END
	END
ELSE
	BEGIN
	WHILE @bcontinue=1
		BEGIN
		--If the delimiter is an empty string, check for remaining text
		--instead of a delimiter. Insert the first character into the
		--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
		IF DATALENGTH(@sText)>1
			BEGIN
			SET @value = SUBSTRING(@sText,1,1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			SET @idx = @idx+1
			SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
			
			END
		ELSE
			BEGIN
			--One character remains.
			--Insert the character, and exit the WHILE loop.
			INSERT @retArray (idx, value)
			VALUES (@idx, @sText)
			SET @bcontinue = 0	
			END
	END

END

RETURN
END

Now, you can use this function to get the values you are looking for. Put them in a table variable (or modify to put in temp table). Then you can join on this table to get your final results.

Here's a temp procedure that I created.

Alter Procedure TestParameter
	@LongParameter VarChar(8000)
As
SET NOCOUNT ON
Declare	@Combined
Table	(ID smallint, Value VarChar(20))

Declare @FirstValue table (Id Integer, Value Integer)
Declare @SecondValue table (Id Integer, Value Integer)

Insert
Into	@combined(Id, Value)
Select	* From dbo.fn_Split(@LongParameter, ',')

Insert
Into	@FirstValue(Id, Value)
Select 	C.Id, C.Value
From	@Combined C
Where	Id % 2 = 0
Order By C.Id

Insert
Into	@SecondValue(Id, Value)
Select 	C.Id -1, C.Value
From	@Combined C
Where	Id % 2 = 1
Order By C.Id

Select	F.Value As Status,
		S.value As SubStatus
From	@FirstValue F
		Inner Join @SecondValue S On F.Id = S.Id
Order By F.Id

go

TestParameter '1,20,3,80,10,12'

You'll notice that the select statement at the end shows a table with your paired data in it. All you need to do is to incorporate this in to your SP.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Someone gave me a UDF that counts the number of delimeters and returns that so you can set variables in your code to whatever code is at X position in the string. Unfortunately, I don't have it with me at the moment and I can't do a search on it from the last time I posted it.

If I don't see a "BINGO" response from you this weekend, I'll go ahead and post it to see if it helps you out.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Jasen]

So, you're passing in a string that will always be comprised of "xx,xx", possibly repeated multiple times? I've parsed strings like that before. You could use something like this:

create procedure blah
        @string varchar(1000)

declare @curIndex as integer, 
	@status integer, 
	@substatus integer,
	@done as smallint


set @done = 0

while @done <> 1
  begin
	set @curIndex = charindex(',', @string, 1)
	set @status = substring(@string, 1, @curIndex - 1)
	set @string = substring(@string, @curIndex + 1, len(@string))
	set @curIndex = charindex(',', @string, 1)
	
	if @curIndex = 0
	  begin
		set @substatus = @string
		set @done = 1
	  end
	else
	  begin
		set @substatus = substring(@string, 1, @curIndex - 1)
		set @string = substring(@string, @curIndex + 1, len(@string))
	  end
        
       INSERT INTO MyTable (status, substatus) VALUES (@status, @substatus)
	  
  end

Obviously, where I did the INSERT statement, you stick in what you want to do with @status and @substatus. It basically breaks the string apart and cycles through however many repetitions of those two fields are present.

________
Remember, you're unique... just like everyone else.