CODEWITHSUNDEEP
sqlsql-serverstored-procedures
GM_NHS_UK
GM_NHS_UK

Reputation: 1

Date used in SP missing single quotes

I'm making an SP and it's my first go with taking a user input.

The purpose of the script is to generate a list of staff that are not on duty. It works, in the sense that the generated list is correct. Though the SP does not

The problem I am having, once I have created it as an SP and then executing it, an error message arises, which is the result of the date not having the required single quotes around it.

Here is the SP code:

    CREATE PROCEDURE List_of_Staff @var_shiftdate DATE

    AS

    SELECT DISTINCT

    FirstName,
    LastName,
    Location,
    Grade

    FROM Vw_My_Staff_View

    WHERE Location LIKE 'Hospital_Ward_1'

    AND StaffID NOT IN 

    (SELECT StaffId FROM Tbl_List_of_Shifts WHERE ShiftDate = @var_shiftdate
    AND ShiftType not in ('Day Off', 'TOIL') 
    AND ShiftStatus IN ('Assigned','Redeployed'))

    ORDER BY LastName

    GO

The Error message is:

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '-'.

And the resultant code trying to execute the SP is this - and doesn't have single quotes around the date.

    USE [My_Database]
    GO

    DECLARE @return_value int

    EXEC    @return_value = [dbo].[List_of_Staff]
    @var_shiftdate = 2017-05-05

    SELECT  'Return Value' = @return_value

    GO

I would just like to know what I can do so that the user doesn't need to enter the single quotes around the date when the SP asks for the variable.

Thanks :)

Upvotes: 0

Views: 60

Answers (1)

Ravi
Ravi

Reputation: 1172

error exists here date should be in single quotes

USE [My_Database]
        GO
    Create table #temp(value int)

    insert into #temp
    EXEC [dbo].[List_of_Staff] '2017-05-05'

    SELECT * from #temp
    drop table #temp

    GO

Upvotes: 1

Related Questions