I know this will probably be a very easy question for a SQL guru out there, but I have not been able to figure this out. I have written a stored procedure to pull a listing of staff birthdays using a birthday day string to select multiple dates. Here is the SQL code which works fine.
USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[Lp_MCFTA_Staff_BDay_Today] Script Date: 01/14/2015 14:41:21 ******/
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
/*
lp_MCFTA_Staff_BDay_Today
@Bday_Month = '11 November',
@Bday_Day_str = '24,25,26,27,28,29,30'
*/
ALTER PROCEDURE [dbo].[Lp_MCFTA_Staff_BDay_Today]
@Bday_Month varchar(50),
@Bday_Day_str varchar(255)
AS
select distinct st.customer_no,
--cc.customer_no, cc.constituency, ct.description, ct.short_desc,
CASE WHEN cc.n1n2_ind = 2 THEN c.fname2
ELSE c.fname
END as fname,
CASE WHEN cc.n1n2_ind = 2 THEN c.lname2
ELSE c.lname
END as lname,
--st.department, st.title, st.hire_date, st.end_date,
--st.customer_no,
st.status,st.Bday_Month, st.Bday_Day,
CASE WHEN cc.n1n2_ind = 2 THEN c.fname2 +' ' + c.lname2
ELSE c.fname +' ' + c.lname
END as compname
from vxsb_const_cust cc
join vb_customer c
on c.customer_no = cc.customer_no
join tr_constituency ct
on ct.id = cc.constituency
right join lt_MCFTA_STAFF st
on st.customer_no = c.customer_no
where cc.constituency in (48,49)--,--50)
and st.status = 'Active'
--and st.customer_no = 97372
--order by c.lname
and Bday_Month = @Bday_Month
and charindex(',' + convert(varchar, st.Bday_Day) + ',' , ',' + @Bday_Day_str + ',') > 0
order by Bday_day
--and Bday_Day = @Bday_Day
Here are the results from the SQL procedure:
customer_no fname lname status Bday_Month Bday_Day compname
108253 Kimberly Dimond Active 11 November 24 Kimberly Dimond
167104 John Tabor Active 11 November 24 John Tabor
72391 Laura Argyle Active 11 November 27 Laura Argyle
127736 David Hadley Active 11 November 29 David Hadley
Here is how I have the report setup. In the SQL procedure, I select the BDay_Day from the LT_MCFTA_Staff table, but if I use that table in the parameter here, I get multiple listings of the same birthdates. Instead, I created a new table LT_BDay_Day which simply lists numbers 1-31.
When I try to run the report, if I select one date, the report will generate fine. If I select more than one date, it gives the error below.
I suspect there is a charindex/convert phrase that I need to add to this report setup to convert the multiple values into a string, but I have tried various things and cannot figure it out.
Any ideas?
Penny Tabor
Director of Technology
Midland Center for the Arts
Midland, MI 48640
(989) 631-5930 X 1207