stored procedure that takes an array?

[earme]

I'm trying to create a stored procedure that will be used to create a Crystal Report. I want the @repno to be able to take multiple values, but I don't know how to do that. Can any one help me out? An example of the stored procedure is below.
Thanks!
Evie

CREATE PROC sp_OrderCount @date1 datetime, @date2 datetime, @repno smallint
AS
SELECT TOP 100 PERCENT dbo.FILE.code, COUNT(dbo.M.PONo) AS TotOrd, dbo.M.RepNo
FROM dbo.M RIGHT OUTER JOIN dbo.FILE ON dbo.M.code = dbo.FILE.code AND dbo.M.OrderDate >= @date1 AND dbo.M.OrderDate <= @date2 AND dbo.M.RepNo IN (@repno)
GROUP BY dbo.M.code, dbo.M.RepNo
GO

 

[essa2000]

Dear
you can send all repno in a string or varchar() datatype and use following method.
Just run this sample code to learn how can you use it. Then, change it for your Stored Procdure.


-- First Create Test table

Create Table TestRepNo (
RepNo smallint identity,
Data varchar(100)
)

-- Insert Dummay data

insert into TestRepNo (Data) values ('Report1')
insert into TestRepNo (Data) values ('Report2')
insert into TestRepNo (Data) values ('Report3')
insert into TestRepNo (Data) values ('Report4')
insert into TestRepNo (Data) values ('Report5')
insert into TestRepNo (Data) values ('Report6')
insert into TestRepNo (Data) values ('Report7')

-- Create Test Stored Procedure

Create Procedure stp_TestRepNo
@P_RepNo varchar(1000)

As

Begin

DECLARE @L_SQL nvarchar(4000)

SET @L_SQL = 'Select * from TestRepNo Where RepNo in (' + ltrim(rtrim(@P_RepNo)) + ')'

Print @L_SQL -- Optional

Exec (@L_SQL)

End


-- Execute that Stored Procedure in this way

Exec stp_TestRepNo '1,3,5,7'

For your case ;

CREATE PROC sp_OrderCount
@date1 datetime,
@date2 datetime,
@repno varchar(1000)
AS

BEGIN

DECLARE @L_SQL nvarchar(4000)


SET @L_SQL = 'SELECT TOP 100 PERCENT
dbo.FILE.code,
COUNT(dbo.M.PONo) AS TotOrd,
dbo.M.RepNo
FROM dbo.M RIGHT OUTER JOIN dbo.FILE
ON dbo.M.code = dbo.FILE.code
AND dbo.M.OrderDate >= @date1
AND dbo.M.OrderDate <= @date2
AND dbo.M.RepNo IN (' + ltrim(rtrim(@repno)) + ')
GROUP BY dbo.M.code, dbo.M.RepNo'

Print @L_SQL -- Optional check this query in query analyzer

Exec (@L_SQL)

END

GO

I hope it will work for you. if you have any confusion then let me inform.

Regards,
Muhammad Essa Mughal