CODEWITHSUNDEEP
c#report
user1777672
user1777672

Reputation: 1

Passing C# parameters to a stored procedure report

I am trying to trigger a report to run with "select all" checked for a field FiscalYearId, which is a string type. I can successfully pass one string such as "2" and the report will find it. However, when I try to pass "select all" or "2,3,4", the multi-check field does not pick up on this.

Here is part of the stored procedure from the report:

ALTER PROCEDURE [dbo].[...]
@FiscalYearId varchar (100) = null ,
...
select ...
where ...
and (@FiscalYearID IS NULL OR p.FiscalYearId IN (SELECT * FROM SPLIT(@FiscalYearID, ',')))

my c# code which triggers the report to pop up in a radwindow is:

`string years = "2,3,4";
newWindow.NavigateUrl = "../Reporting/SingleReport.aspx?Report=AdHocSourcingReport&VendorID=" 
+ vendorId + "&VendorReport=true" + "&FiscalYearId="+years;`

Currently, the vendorId string and VendorReport boolean pull through successfully, it is the string separated by commas that is not populating when the report is run. I initially tried to send a "select all", which is what I want.. Any help is greatly appreciated!!

Upvotes: 0

Views: 231

Answers (2)

Mike Cofoed
Mike Cofoed

Reputation: 151

This is how I've handled splitting comma delimited strings in the past:

CREATE FUNCTION 
    [dbo].[GetItemTable] 
    (
    @Items VARCHAR(1000)
    )
RETURNS 
    @ItemTable TABLE 
    (
    RowID INT IDENTITY(1,1) PRIMARY KEY,
    item VARCHAR(30)
    )
AS
BEGIN

    DECLARE @ProcessItems VARCHAR(1000)
    DECLARE @CurrentItem VARCHAR(30)

    SET @ProcessItems = REPLACE(@Items, '''', '')

    IF SUBSTRING(@ProcessItems, LEN(@ProcessItems), 1)  ','
        SET @ProcessItems = @ProcessItems + ','

        WHILE CHARINDEX(',', @ProcessItems) > 0
        BEGIN

            SET @CurrentItem = LTRIM(CAST(SUBSTRING(@ProcessItems, 0, CHARINDEX(',', @ProcessItems)) AS VARCHAR(30)))

            INSERT INTO 
                @ItemTable 
                (
                item
                ) 
            VALUES
                (
                @CurrentItem
                )

            SET @ProcessItems = SUBSTRING(@ProcessItems, CHARINDEX(',', @ProcessItems) + 1, LEN(@ProcessItems))

        END -- While Schedule   

    RETURN 
END

Obviously, you can change the current item size to whatever you happen to need it to be.

Then just use it like this:

WHERE (p.FiscalYearId IN(SELECT item FROM dbo.GetItemTable(@FiscalYearID)))

Upvotes: 1

rizalp1
rizalp1

Reputation: 6534

I remember doing something similar couple years ago and having same kinds of problem with the list.

Can you try this...

ALTER PROCEDURE [dbo].[...] @FiscalYearId varchar (100) = null , 
... select... where... and 
(@FiscalYearID IS NULL OR p.FiscalYearId IN (@FiscalYearID))

Also, if you are sending "Select all" string, you might need to have a condition in your procedure to check that and change the query to "Select id from fiscalyeartable"

Upvotes: 0

Related Questions