CODEWITHSUNDEEP
datestored-proceduresparameters
user2104163
user2104163

Reputation: 85

Stored Procedure with date parameters

I'm trying to create a stored procedure that has date parameters upon execution. I want to be able to search for orders shipped between particular dates. I have this:

create procedure sp_orders_by_dates
        @startdate smalldatetime,
        @enddate smalldatetime
as
select  OrderID,
        o.CustomerID,

        c.CompanyName as CustomerCompany,
        s.ShipperID,
        s.CompanyName as ShipperCompany,
        ShippedDate

from    Orders o join Customers c
on      o.CustomerID = c.CustomerID join Shippers s
on      s.ShipperID = o.ShipperID
where @startdate = ShippedDate,
        @enddate = ShippedDate
order by ShippedDate

and to execute, i have to do it this way:

EXEC sp_orders_by_dates '1991-07-01', '1991-08-31'

I know this part is what's wrong but I just can't figure out how to make the "between" statement here:

where @startdate = ShippedDate,
        @enddate = ShippedDate

Upvotes: 3

Views: 44622

Answers (3)

Nir Katuni
Nir Katuni

Reputation: 1

"from" and "to" are values that need to be created as input when you call this function. The header of the function is not showing above in the c# code and should look like this: public HttpResponseMessage Get(string from, string to)

Upvotes: 0

Andrew McFall III
Andrew McFall III

Reputation: 83

Here's the grab in C#:

DataTable t = new DataTable();
//set up your connectionString beforhand
using(SqlConnection cn = new SqlConnection(conn))
        {
            //and isolating the work from everything else
            try
            {
                //configure the query apparatus, using the stored procedure
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "<StoredProcName>";

                //set up the parameters
                SqlParameter prmFrom = cmd.CreateParameter();
                prmFrom.Direction = ParameterDirection.Input;
                prmFrom.ParameterName = "@FromDate";
                prmFrom.IsNullable = true;
                SqlParameter prmTo = cmd.CreateParameter();
                prmTo.Direction = ParameterDirection.Input;
                prmTo.ParameterName = "@ToDate";
                prmTo.IsNullable = true;

                prmFrom.DbType = DbType.DateTime;
                prmFrom.SqlValue = from;

                prmTo.DbType = DbType.DateTime;
                prmTo.SqlValue = to;

                //make sure the command and the params go together from the app
                cmd.Parameters.Add(prmFrom);
                cmd.Parameters.Add(prmTo);
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //finally, fill the table so you can pass it back to the app
                da.Fill(t);
            }
            catch(Exception ex)
            {
               //error handling goes here
            }
        }

Upvotes: 3

xenedia
xenedia

Reputation: 314

where ShippedDate BETWEEN @startdate and @enddate

Upvotes: 9

Related Questions