Multiple parameters - pass to stored procedure 1

[giovi2002]

Hi,

When trying to pass the multiple parameter to my stored procedure the report remains empty.
I've used method 1 to pass an arry from this website
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

here's my proc:


CREATE PROCEDURE dbo.Qry_MSSRS_Myproc

@Begindate smalldatetime,
@Einddate smalldatetime,
@Line varchar(100),
@Empno varchar(100)

AS

SELECT *, { fn MONTHNAME(DATUM) } AS Maand,
YEAR(DATUM) AS jaar
FROM dbo.mytable
WHERE myfield=1 And
Datum>=@begindate And Datum<=@enddate And
line in (' + @Line + ') And
empno in (' + @empno + ')
GO

Fields line and empno are numeric fields

MSSQL 2005 finally has the option to pass multiple parameters. Defining multiple parameters within a query defined in the report is quite simple.
I do not use queries in the report because from an administrative point of view it's not as managable as stored procedures.


Thx

 

[xlbo]

how are you calling the stored proc ??

generally you just call the proc without parameters and then set the parameters up specifically in the Parameters property of the DataSet (not the report paramters section)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[giovi2002]

I've used a datasource which connects to the database containing this procedure. I've selected this procedure, the report automatically took over the parameters. Within report parameters i've set the parameters line and empno to multiple value

I'm trying to pass multiple values to the parameters @line and @empno .

The exact name of my thread should be passing multiple value parameters to a stored procedure.

 

[xlbo]

aaaaaah - apologies - I misread - I thought it was just multiple parameters you were having an issue with.

This could be a version issue as I don't believe that RS 200 accepts multiple values to a parameter - 2005 does however.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[unclerico]

within SQL, you need to create a function that will parse your multiple values to make it seem like you are passing an array...take a look at this:

CREATE FUNCTION dbo.ufn_Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (value nvarchar(4000))
AS

  --this function takes two parameters; the first is the delimited string, the second is the delimiter
    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
  
    IF @String IS NULL RETURN
    WHILE @INDEX !=0


        BEGIN    
            -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
            SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
            -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
            IF @INDEX !=0
                SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
            ELSE
                SELECT @SLICE = @STRING
            -- PUT THE ITEM INTO THE RESULTS SET
            INSERT INTO @Results(value) VALUES(@SLICE)
            -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
            SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
            -- BREAK OUT IF WE ARE DONE
            IF LEN(@STRING) = 0 BREAK
    END

    RETURN
END

and then you would simply use it like this:

CREATE PROCEDURE dbo.Qry_MSSRS_Myproc
 
@Begindate smalldatetime,
@Einddate smalldatetime,
@Line varchar(100),
@Empno varchar(100)
 
AS
 
SELECT     *, { fn MONTHNAME(DATUM) } AS Maand, 
           YEAR(DATUM) AS jaar
FROM         dbo.mytable
WHERE myfield=1 And
Datum>=@begindate And Datum<=@enddate And 
line in (SELECT value FROM dbo.ufn_Split(@Line,',')) And
empno in (SELECT value FROM dbo.ufn_Split(@empno,','))
GO

Hope this anwers your question

 

[unclerico]

sorry forgot to include a sample execution; if you were to run this from Query Analyzer it would go like this:

EXEC qry_mssrs_myproc '6/25/2006','6/30/2006','3,23,45,2,6','4563,32423,23342,34423'

not sure what you input looks like for @line or @empno but you can see that I pass in each value separated by a comma with no spaces, this coincides with the delimiter that I pass into the function
SELECT value FROM dbo.ufn_Split(@line,',') so if I wanted to have them separated by a semicolon I would just replace the comma in both the query string in the above code and in the input value for the function
SELECT value FROM dbo.ufn_Split(@line,';')

 

[giovi2002]

Thanks unclerico i will be giving it a try using the split funcion.
Xlbo, i'm using reporting services 2005 which makes it able to use multiple value parameters. Though passing the values to a sproc gives problems so i will be trying unclerico's idea

 

[giovi2002]

Thanks unclerico it worked!
All i need to change is when users select all, he probably generates a query with %.
All i need to do is to validate the parameters in my report procedure, if a % is being passed the where clause is different

 

[unclerico]

good news, I'm glad it worked.