0

I have created two stored procedure from a single table:

Database name : sampleDemo

USE sampleDemo
GO

ALTER PROCEDURE employeeList    
    @city VARCHAR(50)
AS
BEGIN
    SELECT
        emp_id, emp_dob
    FROM 
        employee
    WHERE 
        emp_city = @city
    ORDER BY 
        emp_id
END;

This one working completely fine but the other one:

USE sampleDemo
GO

ALTER PROCEDURE sp_listofemp 
    @city VARCHAR(50)
AS
BEGIN
    SELECT emp_id, emp_name, emp_city, emp_dob
    FROM employee
    WHERE emp_city = @city
    ORDER BY emp_id
END;

Though both the procedure are almost same still the second one is throwing error

Msg 208, Level 16, State 6, Procedure sp_listofemp, Line 2 [Batch Start Line 2]
Invalid object name 'sp_listofemp'

Can someone help me where I am doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tech_sharma
  • 117
  • 2
  • 11
  • 3
    `ALTER PROC` only works with existing procedures. It sounds like the second one doesn't exist yet. Also, [best avoid `sp_`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15#arguments): "Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break..." – Damien_The_Unbeliever Apr 27 '20 at 12:41
  • Does this answer your question? [sql server invalid object name - but tables are listed in SSMS tables list](https://stackoverflow.com/questions/1362531/sql-server-invalid-object-name-but-tables-are-listed-in-ssms-tables-list) – GregD Apr 27 '20 at 12:43
  • 1
    As of SQL Server **2016 SP1** or newer, you can use `CREATE OR ALTER (procedurename)` to handle both cases - `CREATE` in case the stored procedure doesn't exist yet, `ALTER` if it does - in a single command – marc_s Apr 27 '20 at 12:57
  • as the SP was not present, that's why it was throwing invalid object . Now I have changed it to CREATE and it's working. Thanks all – Tech_sharma Apr 27 '20 at 13:04

0 Answers0