passing Date selection to Sp

Question

User279786496 posted

I have 2 drop down lists, where i bind the data using web service from sql server database .
Now in the front end i can see the data in the drop down list.
Here is the procedure that i bind the data in 2 drop down list - start date and end date

Alter procedure dbo.Dates
select top 100 Desc as start date , Desc as end date from dbo. DateSelection

data looks like this in the frontend Example - 
Start date end date
Wk 1 Jan 2010 Wk1 jan 2010
Wk 2 Jan 2010 Wk2 Jan 2010

Now the question is what ever dates i have selected in the drop down how can i pass this selection to below procedure where it has logic to filter.

Idea is Employee Id's which are selected in the front end will look like this
64747,84747,95858 etc....
on clicking on the Move button it will ask to select date parameter selection start date and end date..

I am trying to figure out how to pass date selection to the below procedure as well as Employee Id selection to below procedure..

ALTER PROCEDURE dbo.Employee
(
@IdList NVARCHAR(3500)
)

DECLARE
@SqlStatement NVARCHAR(4000) =
N'
INSERT INTO #Employee
(
EmployeeId 
)
SELECT 
EmployeeId 
FROM 
dbo.Employee
WHERE 
EmployeeId IN (' + @IdList + N');
';

IF ( OBJECT_ID('tempdb..#Employee') IS NOT NULL )
DROP TABLE #Employee;

CREATE TABLE #Employee
(
EmployeeId INTEGER NOT NULL
)

EXEC ( @SqlStatement )

DROP TABLE #Employee;

RETURN;
END

Answer 1

User1168443798 posted

Hi BerrySmith,

>> I have 2 drop down lists, where i bind the data using web service from sql server database

How did you bind drop down list to web service? How did you interop with database by web service?

>> I am trying to figure out how to pass date selection to the below procedure as well as Employee Id selection to below procedure..

Do you mean you have achieved passing Id from FrontEnd to procedure? If so, could you share us how did you achieve this?

It would be helpful if you could share us a simple project to reproduce your issue.

Best Regards,

Edward

Answer 2

User-271186128 posted

Hi BerrySmith,

how can i pass this selection to below procedure where it has logic to filter.

Suppose the stored procedure as below:

CREATE PROCEDURE dbo.usp_GetReportsWithinRange
    @Start_Date smalldatetime, 
    @End_Date smalldatetime
AS
BEGIN
    SET NOCOUNT ON;
    SELECT  *  --You can define columns that you want to pull
    FROM    dbo.Sales_Report
    WHERE   Sales_Date >= @Start_Date AND
        Sales_Date <= @End_Date
END
GO

First, you could get the DropDownList Property (SelectedItem.Text, SelectedItem.Value, SelectedValue).

Then, refer to the following code to call the stored procedure:

            //get connection string
            string constr = ConfigurationManager.ConnectionStrings["MyTestDBConnStr2"].ToString();
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(constr))
            {
                // the procedure name 
                using (SqlCommand cmd = new SqlCommand("usp_GetReportsWithinRange", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    //add parameters
                    cmd.Parameters.AddWithValue("@Start_Date", startdate);
                    cmd.Parameters.AddWithValue("@End_Date", enddate);
                    SqlDataAdapter ada = new SqlDataAdapter();
                    ada.SelectCommand = cmd;
                    ada.Fill(dt);
                }
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();

Best regards,
Dillion