SQL SERVER Input Parms Not Workin

[earljgray]

I have the following SQL as a stored proc:

@Branch varchar(255)
AS
Select
MTGTERMS.LOAN_NUM as loan_number,
br.BRANCH as branch
from
XX_MTGTERMS as MtgTerms
INNER JOIN XX.BRANCH_INFO
as br on MtgTerms.lnkey = br.lnkey
where
br.BRANCH IN (@Branch)

(This is abbreviated, but forms the core of the issue).

@Branch is loaded when the proc is called with input from the user - via Crystal Reports (which actually calls the proc) with a JOINed array of values (all string values) such as 204,458,789.

The report, when run, shows that the user input is being captured by Crystal and passed on to the SP, but there are no results.

If I run the SQL with the
WHERE
br.BRANCH IN ('0248','4372','5202')

I get a raft of data.

When I do an EXEC
[dbo].[SLS045_Registration_Quality] @Branch = '0248,4372,5202'

as a test, I receive no data.

This fails:
exec [dbo].[SLS045_Registration_Quality] @Branch = "0248,4372,5202"
as does this:
exec [dbo].[SLS045_Registration_Quality] @Branch = "'0248','4372','5202'"

ltimately, I'm trying to get
br.BRANCH IN (@Branch)
to work....

any clues as to where to go from here?

Thanks

 

[DaveInIowa]

The 'IN' logical operator 'Determines whether a specified value matches any value in a subquery or a list.' Your @Branch parameter as it is when it is passed into your stored procedure is a single list item whose value is just one long string of '0248,4372,5202'. SQL doesn't know these are 3 separate values. You need to either manually split these into separate values by writing your own Split function (there are many examples available) or you can use this trick (I'm not sure about its efficiency) ...

WHERE ',' + @Branch + ',' LIKE '%,' + br.BRANCH + ',%'

 

[SQLScholar]

I would say your better off with a split function.

Loads about try here:

http://www.sqlservercentral.com/search/?q=split&t=s

for a good selection.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------