Multi-Select Parameter issues - DataType Also? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Multi-Select Parameter issues - DataType Also?

Author

Topic

bdavis
Starting Member

12 Posts

Posted - 2007-01-09 : 17:21:09

I've tried several things to get my multi-select parameter to take more than one value but it seems to only take the 1st one I input. I've been working on this for days and been trying alternate methods but nothing seems to work. I think my first question is if I'm planning on using a parameter as a multi-select parameter what is the data type I should call that parameter. In my case here, the evt_key is unique and will always be 36 characters. I cant imagine in my lifetime that I'm going to have any more than 20 items in the multiselect. Am I looking over something or any other suggestions? I also attached my sp at the bottom.

Thanks

____________________________________________________________________

CREATE procedure dbo.rpt_client_ngcoa_event_registration_attendees_by_state
@evt_key varchar(4000)
as

set nocount on
set transaction isolation level read uncommitted

create table #tmp_dates
(
tmp_evt_key varchar(4000),
tmp_title nvarchar(160),
tmp_start_date datetime
)

create table #temp
(
tmp_key varchar(4000), -- event
evt_title nvarchar(160),
reg_key varchar(38),
reg_session_code nvarchar(400),
type nvarchar(20),
reg_name nvarchar(300),
reg_sort nvarchar(300),
reg_org nvarchar(300),
reg_date datetime,
amount_paid money,
adr_city_state_code NVARCHAR(110) NULL,
adr_state NVARCHAR(40) NULL,
adr_country NVARCHAR(60) NULL,
adr_city NVARCHAR(40) NULL,
adr_post_code NVARCHAR(20) NULL,
src_code NVARCHAR(50) NULL,
days_out int
)

/*
-- FOR MUTLI SELECT FUNCTIONALITY --
create table #tempet
(
tmpet_key varchar(4000)
)

if @evt_key = '' or @evt_key is null

begin
insert #tempet
select distinct evt_key
from ev_event
where evt_key in ('00394886-dfc7-4466-b674-1f2b3ede79ea', '10BE664D-DA4F-44F4-802C-ABD4FC015FB4','56504014-9787-4207-8FAD-EC6C6A384C1D')
and evt_delete_flag = 0
end

else
begin
insert #tempet
exec _selectstringfromstring @idstring = @evt_key,@intorstring='S'
end

if @evt_key = '' goto finalselect
set @evt_key = dbo.av_preprptguidparam(@evt_key,'returnnull')

*/

IF @evt_key='' SELECT @evt_key=NULL

insert into #tmp_dates
select distinct
reg_evt_key,
evt_title,
evt_start_date
from ev_registrant
join ev_event on reg_evt_key = evt_key
--join #tempet on evt_key=tmpet_key
where reg_delete_flag = 0
and evt_delete_flag = 0 --and reg_evt_key = '10BE664D-DA4F-44F4-802C-ABD4FC015FB4'
and (@evt_key is null or (@evt_key is not null and reg_evt_key = @evt_key))
---AND (@evt_key IS NULL OR (@evt_key IS NOT NULL AND reg_evt_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))

insert into #temp
select tmp_evt_key,
tmp_title,
reg_key,
-- net_prc_code,
case net_prc_code when null then tmp_title
when '' then tmp_title
when 'NULL' then tmp_title
when ' ' then tmp_title
else net_prc_code end,
'Reg Code',
cst_ind_full_name_dn,
cst_sort_name_dn,
cst_org_name_dn,
dbo.av_end_of_day(reg_add_date),
net_payamount,
adr_city_state_code,
adr_state,
adr_country,
adr_city,
adr_post_code,
src_code,
datediff(dd, reg_add_date, tmp_start_date)
from #tmp_dates
join ev_registrant on reg_evt_key = tmp_evt_key
join vw_ac_invoice_detail on reg_ivd_key = net_ivd_key
join co_customer on cst_key = reg_cst_key
LEFT JOIN co_customer_x_address x ON x.cxa_key = reg_cxa_key AND cxa_delete_flag = 0
LEFT JOIN co_address a ON a.adr_key = x.cxa_adr_key AND adr_delete_flag = 0
LEFT JOIN co_source_code o ON src_key = reg_src_key AND src_delete_flag = 0
--join #tempet on reg_evt_key=tmpet_key
where reg_delete_flag = 0
and reg_cancel_date is null
and cst_delete_flag = 0
AND (@evt_key IS NULL OR (@evt_key IS NOT NULL AND tmp_evt_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))

finalselect:
select *
from #temp
--where (@evt_key IS NULL OR (@evt_key IS NOT NULL AND tmp_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))
order by days_out desc, type, reg_name
GO

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-09 : 17:29:44

Your av_SelectStringFromString function and _SelectStringFromString procedure are doing the work of getting the individual parameters from the multi-select parameter, so you need to post their code.

bdavis
Starting Member

12 Posts

Posted - 2007-01-09 : 17:49:00

CREATE FUNCTION [dbo].[av_PrepRptGuidParam] (
@value varchar(38),
@option varchar(100)
)
RETURNS varchar(36) AS
BEGIN

declare @returnvalue varchar(36)

If isnull(@value,'') = '' Begin

select @ReturnValue =
case lower(ltrim(rtrim(@option)))
when 'returnall' then '%'
when 'returnnone' then '00000000-0000-0000-0000-000000000009'
when 'returnnull' then null
else '%'
end
End else begin

select @value=replace(@value,'{','')

select @value=replace(@value,'}','')

select @ReturnValue = @value

end

return @ReturnValue

END

____________________________

CREATE FUNCTION dbo.av_SelectStringFromString (

@sInputList varchar(8000) -- List of delimited items
, @Delimiter char(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (Item varchar(8000))
WITH SCHEMABINDING
/*
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The
* strings are trimmed before being returned. Null items are not
* returned so if there are multiple separators between items,
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
select * FROM dbo.av_SelectStringFromString('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count int, @Delim char(10), @Input varchar(128)
SELECT @Count = Count(*)
FROM dbo.av_SelectStringFromString('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
, @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*)
FROM dbo.av_SelectStringFromString(@Input, @Delim)
PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #29
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
*
***************************************************************/
AS BEGIN

DECLARE @Item Varchar(8000)
DECLARE @Pos int -- Current Starting Position
, @NextPos int -- position of next delimiter
, @LenInput int -- length of input
, @LenNext int -- length of next item
, @DelimLen int -- length of the delimiter

SELECT @Pos = 1
, @DelimLen = LEN(@Delimiter) -- usually 1
, @LenInput = LEN(@sInputList)
, @NextPos = CharIndex(@Delimiter, @sInputList, 1)

-- Doesn't work for space as a delimiter
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END

-- loop over the input, until the last delimiter.
While @Pos <= @LenInput and @NextPos > 0 BEGIN

IF @NextPos > @Pos BEGIN -- another delimiter found
SET @LenNext = @NextPos - @Pos
Set @Item = LTrim(RTrim(
substring(@sInputList
, @Pos
, @LenNext)
)
)
IF LEN(@Item) > 0
Insert Into @List Select @Item
-- ENDIF

END -- IF

-- Position over the next item
SELECT @Pos = @NextPos + @DelimLen
, @NextPos = CharIndex(@Delimiter
, @sInputList
, @Pos)
END

-- Now there might be one more item left
SET @Item = LTrim(RTrim(
SUBSTRING(@sInputList
, @Pos
, @LenInput-@Pos + 1)
)
)

IF Len(@Item) > 0 -- Put the last item in, if found
INSERT INTO @List SELECT @Item

RETURN
END

bdavis
Starting Member

12 Posts

Posted - 2007-01-09 : 17:49:01

CREATE FUNCTION [dbo].[av_PrepRptGuidParam] (
@value varchar(38),
@option varchar(100)
)
RETURNS varchar(36) AS
BEGIN

declare @returnvalue varchar(36)

If isnull(@value,'') = '' Begin

select @ReturnValue =
case lower(ltrim(rtrim(@option)))
when 'returnall' then '%'
when 'returnnone' then '00000000-0000-0000-0000-000000000009'
when 'returnnull' then null
else '%'
end
End else begin

select @value=replace(@value,'{','')

select @value=replace(@value,'}','')

select @ReturnValue = @value

end

return @ReturnValue

END

____________________________

CREATE FUNCTION dbo.av_SelectStringFromString (

@sInputList varchar(8000) -- List of delimited items
, @Delimiter char(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (Item varchar(8000))
WITH SCHEMABINDING
/*
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The
* strings are trimmed before being returned. Null items are not
* returned so if there are multiple separators between items,
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
select * FROM dbo.av_SelectStringFromString('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count int, @Delim char(10), @Input varchar(128)
SELECT @Count = Count(*)
FROM dbo.av_SelectStringFromString('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
, @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*)
FROM dbo.av_SelectStringFromString(@Input, @Delim)
PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #29
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
*
***************************************************************/
AS BEGIN

DECLARE @Item Varchar(8000)
DECLARE @Pos int -- Current Starting Position
, @NextPos int -- position of next delimiter
, @LenInput int -- length of input
, @LenNext int -- length of next item
, @DelimLen int -- length of the delimiter

SELECT @Pos = 1
, @DelimLen = LEN(@Delimiter) -- usually 1
, @LenInput = LEN(@sInputList)
, @NextPos = CharIndex(@Delimiter, @sInputList, 1)

-- Doesn't work for space as a delimiter
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END

-- loop over the input, until the last delimiter.
While @Pos <= @LenInput and @NextPos > 0 BEGIN

IF @NextPos > @Pos BEGIN -- another delimiter found
SET @LenNext = @NextPos - @Pos
Set @Item = LTrim(RTrim(
substring(@sInputList
, @Pos
, @LenNext)
)
)
IF LEN(@Item) > 0
Insert Into @List Select @Item
-- ENDIF

END -- IF

-- Position over the next item
SELECT @Pos = @NextPos + @DelimLen
, @NextPos = CharIndex(@Delimiter
, @sInputList
, @Pos)
END

-- Now there might be one more item left
SET @Item = LTrim(RTrim(
SUBSTRING(@sInputList
, @Pos
, @LenInput-@Pos + 1)
)
)

IF Len(@Item) > 0 -- Put the last item in, if found
INSERT INTO @List SELECT @Item

RETURN
END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-10 : 00:29:39

Are you passing the parameter list like this
'00394886-dfc7-4466-b674-1f2b3ede79ea,10BE664D-DA4F-44F4-802C-ABD4FC015FB4,56504014-9787-4207-8FAD-EC6C6A384C1D'

Try putting a print statement in your sp to print out the value of @evt_key and then post the result here

bdavis
Starting Member

12 Posts

Posted - 2007-01-10 : 09:21:25

Yes my execution statement looks like this

exec dbo.rpt_client_ngcoa_event_registration_attendees_by_state'00394886-dfc7-4466-b674-1f2b3ede79ea,10BE664D-DA4F-44F4-802C-ABD4FC015FB4,56504014-9787-4207-8FAD-EC6C6A384C1D'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources