Iterate through list of parameters

Hello,
I want to concatenate a string like
=Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
+ Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
+ Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
+ ...
But this should be universal for all reports and universal for
single/multivalue parameters (where JOINs would be necessary).
So my question:
Is it possible to iterate through the list of parameters in custom code or
in an assembly to return such a string in a single, report-independant way?
Kind regards,
R."Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>
sure you can ... here is a function I used in a previous report :
function createparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function
enjoy
Peter|||Ralph,
I have used the Join function to concatenate the selected values of my
multivalue parameters like this:
=Join(Parameters!Para1.Label + ": " + Parameters!Para1.Value, ", ")
if you need to wrap anything in quotes you will need to modify, say add
quotes around the whole thing:
=Join("'" + Parameters!Para1.Label + ": " + Parameters!Para1.Value, "', '")
Hope this helps
Reeves
"Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>|||Hello Peter,
thank you very much for your help. Maybe I do not understand your code, but
I can't see the solution to my problem.
So I try to explain in more detail:
I wan't to print out the filters a user selected for displaying a report,
containing all filter descriptions and values. And so I think I need to
write a function like
function concatenateparameter(ByVal param as object) as string
but I do not know how to specify the object "param" which has to consist auf
all available parameters and their descriptions.
Kind regards,
Ralph
"Peter De Rop" <PeterDeRop@.discussions.microsoft.com> schrieb im Newsbeitrag
news:507E6A77-B3E6-4B26-8171-D5CB69764E68@.microsoft.com...
>
> "Ralph Watermann" wrote:
>> Hello,
>> I want to concatenate a string like
>> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
>> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
>> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
>> + ...
>> But this should be universal for all reports and universal for
>> single/multivalue parameters (where JOINs would be necessary).
>> So my question:
>> Is it possible to iterate through the list of parameters in custom code
>> or
>> in an assembly to return such a string in a single, report-independant
>> way?
>> Kind regards,
>> R.
>>
> sure you can ... here is a function I used in a previous report :
> function createparameter(ByVal param as object) as object
> dim strNewParam() as string
> dim countery as int32
> dim x as object
> for each x in param
> countery = countery + 1
> next
> redim strNewparam(countery + 1)
> dim counterx as int32
> counterx = 0
> for each x in param
> strNewParam(counterx) = x
> counterx = counterx + 1
> next
> strNewParam(counterx) = ""
> return strNewParam
> end function
> enjoy
> Peter

Iterate result set inside stored procedure

Hello,

I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).

How can I process result rows inside a stored procedure?

This is what I have so far:

CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@.serviceDetailIDint,
@.commentvarchar(500),
@.commentDateDateTime,
@.commentAuthorvarchar(100)
AS
BEGIN
DECLARE @.serviceIDint
DECLARE @.p_recipientsvarchar(8000)
DECLARE @.p_messagevarchar(8000)
DECLARE @.p_subjectvarchar(100)
/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @.serviceID = Service_idFROM lm_Service_DetailWHERE Service_Detail_id = @.serviceDetailID
/* Get email addresses of Service Responsible Parties */SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_DetailINNERJOIN dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
/* Build message */SET @.p_subject = "KLM - Service ID: " +CAST(@.serviceIDAS varchar(4))
SET @.p_recipients = ""/*need string of addresses*/SET @.p_message = @.p_message + "Service Detail ID: " +CAST(@.serviceDetailIDAS varchar(4)) +char(13)
SET @.p_message = @.p_message + "Comment Date: " +CAST(@.commentDateAs varchar(25)) +char(13)
SET @.p_message = @.p_message + "Comment Author: " + @.commentAuthor +char(13)
SET @.p_message = @.p_message + "Comment: " + @.comment +char(13)
PRINT "subject: " + @.p_subject +char(13)
PRINT "recip: " + @.p_recipients +char(13)
PRINT "msg: " + @.p_message +char(13)
/*Send the email*/Execute master..xp_sendmail @.recipients = @.p_recipients, @.message = @.p_message, @.subject = @.p_subject
ENDGO

Hi,
You can declare a variable and append the results onto it for each row with:

DECLARE @.emails varchar(1000)

SELECT @.emails = isnull( @.emails,'' ) + ', ' + dbo.lm_Responsible_Party.Email
FROM xxxxxx

This gives you the email addresses in comma delimted form:
"me@.me.com, you@.you.com, www.this.com"

The only caveat is that I don't believe you can use the DISTINCT with this. So you would need to amend your SELECT so that it does not use this.
You could do this with:

SELECT @.emails = isnull(@.emails,'') + ', ' + Email
FROM
( SELECT DISTINCT dbo.lm_Responsible_Party.Email AS Email
FROM dbo.lm_Service_DetailINNERJOIN
xxxx
) subquery

|||

Awesome, thanks! I got the concatenation working; and you were right about the DISTINCT command. So, when I tried the way you suggested (putting DISTINCT in select below), I kept getting errors. It didn't like the statement. So, any other ideas on how to retrieve only unique values?

Also, I get a leading comma - how do I avoid that on the first entry?

Ex: "recip: , xxx@.xxx.com, yyy@.yyy.com"

|||

I got it! Needed to assign a derived table.

Still have the comma issue, though.

/* Get email addresses of Service Responsible Parties */
SELECT @.emails =isnull(@.emails,'') +', ' + tmpEmail
FROM (
SELECT DISTINCT dbo.lm_Responsible_Party.EmailAS tmpEmail
FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID))AS derivedtbl_1

|||

The leading comma is probably because you may have assigned a value to the variable first?
like:
DECLARE @.emails varchar(1000)
SET @.emails=''

The SQL I gave you accounts for it being null the first time round and puts an empty string instead:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + Email

if you can't get rid of it, then use the SUBSTRING function to get rid of the first character.

As for error on the DISTINCT i suggested, it should work, I wrote some sample stuff at my end.
I'll try my explanation in full.. try copying this: (note that you have to name the subquery and you have to alias the Email)

DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + PartyEmail
FROM
(SELECT DISTINCT dbo.lm_Responsible_Party.Email AS PartyEmail

FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
 ) subquery

|||

Just seen your post after I did one!
Excellent news that you got it working.

The comma again is coming from the fact that you have probably assigned "recip:" before you do the select.
Add it on afterwards with

SET @.emails = 'recip: ' + @.emails

|||

Instead of

SELECT @.email=ISNULL(@.email,'') + ',' + {your field}

use

SELECT @.email=CASE WHEN @.email IS NULL THEN '' ELSE @.email+',' END + {your field}

|||

I figured it out, the comma had to be inside the ISNULL command (otherwise it was displaying it regardless of the value of @.emails).

Like this: SELECT @.emails = ISNULL(@.emails + ', ' ,'') + tmpEmail

And, yeah - you were right about the distinct. I didn't realize "subquery" was part of the actual query. I thought you were just referencing the remaining queries.

Either way, thanks a lot for the help - it's good to go now!