1

I have a few table's in my DB.

collection of db tables

I am trying to update the rota table using a SQL stored procedure. I want the user to be able to type in, for example;

Name: Adam Brookes

Shift: Middle Day

On Call: Secondary

and using a subquery have it look up the id's of those and place them into the rota table. At the moment I am testing in Postman where I can define the 'date' and 'datekey' manually.

My Stored Procedure currently looks like this;

ALTER PROCEDURE [dbo].[RotaUpdateItems]
    @date_key int,
    @date date,
    @user_id varchar(max),
    @shift_type_id int,
    @type_call_id int

AS
BEGIN

    SET NOCOUNT ON;

    UPDATE rota
    SET date = @date,
        user_id = (
        SELECT user_id 
        FROM staff s
        WHERE s.first_name LIKE 'ABC%'
        ),
        shift_type_id = (
        SELECT shift_type_id 
        FROM shifts h
        WHERE h.shift_type LIKE 'ABC%'
        ),
        type_call_id = (
        SELECT type_call_id 
        FROM on_call c 
        WHERE c.type_of_call LIKE 'ABC%')
    WHERE date_key = @date_key
END

I have referenced this question to get the LIKE 'ABC%' code to see if that would help convert "Primary" into '1', but this did not fully answer my question

I also researched this answer from John to see if a join is necessary when running an update query but this did not fully answer my question.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
Ryan
  • 11
  • 1
  • Why are you looking up the `type_call_id` inside a sub-query? You already have a parameter `@type_call_id` with an integer value in it? – MatBailie Feb 12 '18 at 10:47
  • I did this because I thought I want to select the type_call_id from the table ''on_call'', where I check the ''type_of_call'' against what the user has input (Secondary) – Ryan Feb 12 '18 at 10:55
  • So, update your question to reflect what you're ***actually trying to do***. As I understand it you want the parameters to be `VARCHAR(32)` or similar? And then the sub-queries to have `c.type_of_call = @type_of_call`, for example? – MatBailie Feb 12 '18 at 11:01
  • Sorry I thought I made it clear with the paragraph but it's not clear enough. I have trouble wording these things. I have changed the parameters from int to varchar and tried your example but the test still puts back `''Validation failed for parameter 'shift_type_id'. Invalid Number` – Ryan Feb 12 '18 at 11:16
  • So what is the question? – holder Feb 12 '18 at 11:30
  • For example using user_id as an example. I want the user to type in ''Adam'', and have the subquery look through the `staff` table, see that ''Adam'' is staff.user_id #2, and then place `2` into the rota row which I have already selected to update. Same principle for Shift = 'Middle Day' and On Call = 'Secondary' – Ryan Feb 12 '18 at 11:46

0 Answers0