Johnathan Johnathan - 5 months ago 19
SQL Question

I am trying to get all the SQL caseID between two datetimes

I am building a webservice in ASP.NET that will call a stored procedure to get all the cases between two dates. I am using SQL Management Studio Here is my code:

ALTER PROCEDURE spGetNewCases
(@startDate datetime, @endDate datetime)
AS
BEGIN
SELECT
caseID
FROM
tblCases
WHERE
dateOpened BETWEEN @startDate and @endDate
END
GO
EXEC spGetNewCases


However, I get the following error:


"Could not find object spGetNewCases".


In the IDE , the words "between" and "and" are highlighted in light gray instead of dark blue. Moreover, I would like to test it with two dates, say 2015-01-01 and 2015-01-04.

The format of my datetime in my SQL server is datetime2((0),null). E.g.

2015-01-02 00:00:00.


I would greatly appreciate the community's feedback. Thank you!

EDIT:

Thank you everyone for your great help! Here is my new code that works!

-- procedure
USE [vetDatabase_Wizard]
GO
ALTER PROCEDURE sp_GetNewCases (@startDate datetime, @endDate datetime)
AS
BEGIN
SELECT caseID FROM tblCases
WHERE dateOpened BETWEEN @startDate and @endDate
END
GO
EXEC sp_GetNewCases '2015-01-01', '2015-04-04'

Answer

If your SP is created then check your DB context. In SSMS there is a dropdown menu, you can also USE DBNAME. In ASP code it is in your connection string.

If it is not created you will need to Create Procedure first and then you can Alter Procedure.

You can call the SP like so : EXEC spGetNewCases '2016-03-01', '2016-05-01'

Side note: System Stored Procedures use the sp prefix you probably should choose something different like sp_ or usp.

Comments