Passing an array to SQL Server using stored procedures (1 Viewer)

GloriaLuz

New member
Local time
Tomorrow, 02:56
Joined
Nov 8, 2005
Messages
7
Hello,
I have a form where the user can choose from 1 to n countries from a list box and from there a report is run based on the countries the user has chosen.

I am using a stored procedure to pass these parameters to the report; however I am not able to get any records. Any ideas why ?
Thanks in advance for your help
Gloria


My stored procedure looks like this:

********
Alter PROCEDURE repcountry @country nvarchar(10) AS
SELECT dbo.brnch.addr + N' ' + dbo.brnch.suburb + N' ' + dbo.brnch.state + N' ' + dbo.brnch.PIN AS Expr1, dbo.individuals.posit, dbo.individuals.phn, dbo.company.name, dbo.company.brch_No, dbo.company.bus_typ, dbo.company.staffno

FROM dbo.company INNER JOIN
dbo.brnch ON dbo.company.ID = dbo.brnch.com_ID INNER JOIN
dbo.individuals ON dbo.brnch.br_ID = dbo.individuals.br_ID INNER JOIN
dbo.country ON dbo.brnch.country = dbo.country.country_ID

WHERE dbo.brnch.country in (@country)
order by dbo.company.name
*********
 

FoFa

Registered User.
Local time
Today, 10:56
Joined
Jan 29, 2003
Messages
3,672
I do not know of a way to get that to work as written. If I have to do this I create a dynamic SQL statement and concatenate the values into the variables with the rest of the SQL, then exec the SQL statement.
Code:
SET @sSQL = 'INSERT INTO MyTable (KeyID, proc_company) '
SET @sSQL = @sSQL + 'SELECT KeyID, ''' + @CO + ''' from Table1 '
SET @sSQL = @sSQL + 'WHERE ((Category in (''F'',''FB'',''PB'') '
SET @sSQL = @sSQL + 'AND status <> ''Terminated'') '
SET @sSQL = @sSQL + 'OR (Category = ''N'' AND status = ''Terminated'')) '
SET @sSQL = @sSQL + ' AND CoCode in ' + @COC
EXEC(@sSQL)
The actual value of @COC is ('V1','V2') so it appears as valid SQL.
 

GloriaLuz

New member
Local time
Tomorrow, 02:56
Joined
Nov 8, 2005
Messages
7
Thank you Fofa,
I will try what you suggest and let you know how I go
Thanks again
Gloria
 

Users who are viewing this thread

Top Bottom