CODEWITHSUNDEEP
sqlsql-server
Arijit Ganguly
Arijit Ganguly

Reputation: 5

Passing Parameter in Stored Procedure 1

The SP is not treating @AgeBand parameter correctly. How do i pass that parameter?

Alter Procedure sp_Dialer_Analysis
    @AgeBand Varchar(50),
    @Gender Varchar(50),
    @Weekday Varchar(50)
AS
BEGIN

Select @AgeBand,@Gender,@Weekday,SUM(RPC)
from TableA a
left join TableB b
on a.[Contact Info] = b.MSI
where a.date >= '2017-01-01'
and b.gender = @Gender and b.AgeBand in (@AgeBand)
and DATENAME(WEEKDAY,a.date) = @Weekday

END

Exec sp_Dialer_Analysis "'50-54','55-59'",'F','Monday'

"'50-54','55-59'" is the issue. Kindly suggest some alternative.

Upvotes: 0

Views: 65

Answers (3)

Prabhat G
Prabhat G

Reputation: 3029

I think OP is asking about escape characters.

Please try this : Exec sp_Dialer_Analysis '''50-54'',''55-59''','F','Monday'

Upvotes: 0

Rachel Ambler
Rachel Ambler

Reputation: 1594

You cannot pass an array in to stored procedure like that, doubly so using double quotes (")

Your best bet is to either run the procedure multiple times (yuck, performance hit) or split the array out using either a home brewed Split function or the new String_Split function in Sql Server 2016

Perhaps something like this (not tested, off the top of my head)

Alter Procedure sp_Dialer_Analysis
    @AgeBand Varchar(50),
    @Gender Varchar(50),
    @Weekday Varchar(50)
AS
BEGIN

Select @AgeBand,@Gender,@Weekday,SUM(RPC)
from TableA a
Cross Apply String_Split(@AgeBand, ',') As s
left join TableB b
on a.[Contact Info] = b.MSI
where a.date >= '2017-01-01'
and b.gender = @Gender
And b.ageband = s.value
and DATENAME(WEEKDAY,a.date) = @Weekday

END

Exec sp_Dialer_Analysis '50-54,55-59','F','Monday'

Not tried SHD's answer but prima-facia I think it deserves merit... and may be a better answer

Upvotes: 0

SHD
SHD

Reputation: 409

Condition b.AgeBand in (@AgeBand) will not work,

try using CHARINDEX(b.AgeBand,@AgeBand) > 0

Upvotes: 1

Related Questions