Send string list as stored procedure parameter

Question

Post reply

Send string list as stored procedure parameter

Tjaard

SSC-Addicted

Points: 443
More actions
June 27, 2008 at 7:43 am

#99163

Hi guys,
I have a stored procedure with Paramater @BPCodes of type nvarchar.
Please take a look at stored proc below:
create proc SP_Balance_BF
@BPCode as nvarchar(max),
@fDate as nvarchar(30),
@tDate as nvarchar(30),
@DateTP as nvarchar(30)
as
set nocount on
declare @FinalStr as nVarchar(max)
Declare @WhereStr1 as nVarchar(max)
set @WhereStr1 = ' where (T2.ShortName in (' + @BPCode + '))'
set @FinalStr = 'Select T2.ShortName as [CardCode],
Amount = case
when T2.BalDueCred <> 0 then T2.BalDueCred
when T2.BalDueDeb <> 0 then T2.BalDueDeb * -1
when T2.BalDueCred = 0 and T2.BalDueDeb = 0 then 0
end
into #Temp
from (OJDT T0 inner join JDT1 T2 on T0.TransID = T2.TransID)
' + @wherestr1 + ' order by T2.ShortName
Select CardCode, Sum(Amount)
from #Temp
group by CardCode'
print (@FinalStr)
exec (@FinalStr)
The problem is in the where clause: where (T2.ShortName in (' + @BPCode + '))
T2.ShortName is a string value,
Thus when executed the where clause should look as follows:
where (T2.ShortName in ('A1','A2',A3,'A4'))
My question is what is the correct syntax for the execute?
exec SP_Balance_BF 'A1,A2,A3,A4','','','' is wrong
exec SP_Balance_BF ''A1','A2','A3','A4'','','','' is wrong
I need a single parenthisis before and afer each BPCode.
Please help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

If you populate a temporary table with @BPCode then you can use where ShortName in (Select Shortname from #temptable)

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

Unless there is a specific reason otherwise, you don't need to run this kind of thing as dynamic SQL.

Use a string parser (there are some on the Scripts page of this site), parse the delimited list into a table, use that table in your In statement. Much easier.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

But if you want to stay with this, call it like this:

[font="Courier New"]

EXEC sp_Balance_BF '''A1'',''A2'',''A3'',''A4''', '', '', ''[/font]

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Tjaard SSC-Addicted Points: 443 More actions · Answer 4

Thanx RBarryYoung, that's exactly what I was looking for. Must have been having a slow brain day when I popped the question.... 😉

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

What? You couldn't remember that it goes quote, quote, quote, value one, quote, quote, comma, quote, quote, value two, quote, quote, comma, quote, quote, value three, quote, quote, comma, quote, quote, ... 😀

Seriously though, nesting quoted parameter lists can be beastly.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]