Thursday, July 23, 2009

SSRS Multi-Select Parameters for Stored Procedures in SQL 2005

[Intro]

Using SSRS multi-value parameters with stored procedures can be a bit of a pain. Multi-Valued parameters are sent to Stored Procedures as a comma-delimited string. What follows is the method I use to write and debug stored procedures for SSRS reports.
The web is full of tutorials on handling multi-valued parameters in stored procedures. What I’m hoping to show is a method to set them up so that you can easily debug the stored procedure later on down the road.

[Background]

I found some code on SQLServerCentral.com that does a good job of splitting a comma-delimited string into a table of individual values. I wish I could credit the person who originally posted this idea, but there are lots of variations of this code floating around, and I modified the version I originally found, so please accept my apologies if this looks familiar.
This function will break down a comma-delimited string into a table of individual values. The one I’m posting here returns a table of ints, but I have others that return a table of other values (i.e. strings):

----------------------------------------------------
-- [dbo].[fnc_split_int]
-- Parse a comma delimited string and insert the values into a table variable.
-- Useful for sending a comma-delimited string parameter to a stored proc that will
-- use it in a where IN clause.
--
-- Ex:
-- select * from customers
-- will not work: Where customer_id in (@parameter)
-- will work : Where customer_id in (select item from fnc_split_string(@parameter,','))
---------------------------------------------------------
ALTER FUNCTION [dbo].[fnc_split_int](
@list varchar(8000),
@Delimiter VARCHAR(10) = ','
)
RETURNS @tablevalues TABLE (
item int
)
AS
BEGIN

DECLARE @P_item varchar(255)

WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END

INSERT INTO @tablevalues
SELECT Item = convert(int, LTRIM(RTRIM(@p_Item)))
END

RETURN

END

Here’s a simple example of a stored procedure using this function:

create proc dbo.salesinfo (
@customers varchar(max)
)
as

Select * from dbo.sales
where customer_id in ( select item from fnc_split_int(@customers, ',') )

[Using Table Variables]


The method above works well, but can get kind of annoying if you ever have to debug the stored procedure. The stored procedure could be huge, and you might not care too much about which customers are displayed. However it now requires a comma-delimited string of customers anytime you want to run some tests.
To keep me from losing my mind, I add a few table variables in my stored procedure that are designed to handle the multi-value SSRS parameters.


This is probably best explained in an example:

create proc rpt.salesinfo (
@customers varchar(max) = null --set to null by default
)
as

--Build local customer table
declare @customerlist table (
customer_id int
)

if @customers is null
begin
--Load all customers (null)
insert into @customerlist
select customer_id from customers
end
else
begin
--Load only those customers chosen in @customers
insert into @customerlist
select item from fnc_split_string(@customers, ',')
end


--report query (using @customerlist)
select * from sales
where customer_id in (select customer_id from @customerlist)


Notes:
  • It might seem a little excessive to build a local table of values when you already have those values in comma-delimited form. But using this method has saved me a lot of headaches when I’m trying to debug something in the stored procedure.

  • I could use a join instead of putting the @customerlist table in the where string. However, I’ve found that performance hasn’t really been an issue for our setup. I also think it’s a little easier to read – a new developer doesn’t have to worry about finding a join that is only used to limit the resultset. From what I remember, the server does a lot to optimize queries anyway, so things like this usually do not matter as much.

[Final Thoughts]

This example shows a method I use to test and debug stored procedures for SSRS reports that use multi-valued parameters. There isn’t any ground breaking functionality introduced, just a bunch of smaller things strung together to hopefully make life easier. I should mention there are other ways of doing this, some might be better than this one. At the time of this writing I was only using SQL Server 2005.

Please feel free to ask questions or suggest improvements.

Wednesday, February 11, 2009

IIS 7 ASP scripts (vbscript) fail to send email (web.com)

I recently had a horrible experience with web.com. They "upgraded" our web server that broke all my client's email functionality for his ASP scripts. These scripts ran flawlessly for years without issue, then all of the sudden none of them worked, and the web host seemed unwilling to help us.

After doing some research, I discovered that they upgraded their servers to MS IIS 7, which no longer directly supports sending emails from an ASP page using "CDO.Message" or "CDONTS". I'm hoping that other web.com users might come across this article in a google search and could save some themselves some time. There might also be other people who have another web host with similar problems.

From what I understand, according to all articles I have found around the web, IIS7 no longer supports sending emails from an ASP page using "CDO.Message" directly.

Here is my old code, which no longer works:

Sub SendMail(Subject,Body,EmailTo,EmailFrom)
Set Newmail = CreateObject("CDO.Message")
newmail.TextBody = Body
newmail.Subject = Subject
newmail.To = EmailTo
newmail.From = EmailFrom
newmail.Send
set newmail = nothing
end Sub


There are a few solutions out there, but the one that worked for me (as a web.com user) is to use CDOSYS. I found this through a series of searches, and my solution I present is just my flavor of the method recommended:

Regarding the Fields used for "CDO.Configuration", I had to play around to find the ones that my webhost seemed to care about. For instance, I have commented out cdoSendUsingPort and replaced it with a 2 (the actual value of cdoSendUsingPort). Most people online seem to be recommending putting the address of your SMTP server for the "smtpserver" setting, but I found that web.com only worked with "localhost".



Function SendEmail(Subject,Body,EmailTo,EmailFrom)
' SendEmail(Subject,Body,EmailTo,EmailFrom)
' 2/10/2009 JMJ: Uses CDOSYS method of sending email
'

Set cdoMessage = CreateObject("CDO.Message")
Set cdoConfig = CreateObject("CDO.Configuration")

Set Flds = cdoConfig.Fields

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

With cdoMessage
Set .Configuration = cdoConfig
.To = EmailTo
.From = EmailFrom
.Subject = Subject
.TextBody = Body
.Send
End With

Set cdoMessage = Nothing
Set cdoConfig = Nothing
Set Flds = Nothing

SendEmail = true

End Function 'SendEmail()


Coincidentally, there are a couple more solutions out there. One is to copy a couple dll’s onto the new server to provide support for legacy ASP scripts; another is to just send emails using the database. I’ve provided some links below that were helpful to me.

Tips for Classic ASP developers on IIS7

IIS7 - Classic ASP Forum

How to send e-mail with CDO

ASP Sending e-mail with CDOSYS