How to create a select, and then an update stored procedure in SQL Server 2012

titusx11

I'm trying to create a stored procedure that runs a select query and pulls an id (variable) and then do an update query to that id. Any help would be appreciated.

This is what I have:

CREATE PROCEDURE dbo.Lead_usp_getLead
    @LeadId int output
AS

   SELECT TOP 1  
       Leadid, LeadInitials, LeadFirstName, LeadSurname, 
       LeadHomeTelephoneNumber, LeadWorkTelephoneNumber,
       LeadCellularNumber, LeadEMailAddress, IsLocked, uploadedDate
   FROM 
       dbo.Lead
   WHERE 
       IsLocked = 'False'
   ORDER BY 
       uploadedDate;

   UPDATE dbo.Lead
   SET IsLocked = 'TRUE'
   WHERE LeadId = @LeadId

   DECLARE @leadid int

   EXEC dbo.Lead_usp_getLead @leadId;
Gordon Linoff

You can just combine them. No need to do two queries:

with toupdate as (
      SELECT TOP 1 l.*
      FROM dbo.Lead l
      WHERE l.IsLocked = 'False'
      ORDER BY l.uploadedDate
     )
Update toupdate
    SET IsLocked = 'TRUE';

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SELECT against stored procedure SQL Server

From Dev

SQL Server stored procedure select top 1 and update in one statement?

From Dev

SQL Server : create and call stored procedure

From Dev

Execute multiple statements and CTE in stored procedure in SQL Server 2012?

From Dev

How to create stored procedure in C#, then *save* it to SQL Server?

From Dev

In SQL Server 2012, how do I get the column name and data type from a view, function, or stored procedure?

From Dev

How to give table name dynamically in stored procedure in SQL Server 2012

From Dev

How to create a stored procedure in SQL Server Management Studio

From Dev

Create stored procedure in SQL Server from a MS Access update query

From Dev

SQL Server Stored Procedure select

From Dev

How to select the value of the first ID in SQL Server stored procedure?

From Dev

Rollback in single stored procedure for forloop & insert query in SQL Server 2012

From Dev

SQL Server : create and call stored procedure

From Dev

How to create dynamic parameters in SQL Server stored procedure

From Dev

SQL Server 2012 Insert Stored Procedure for N parameters

From Dev

SQL server create stored procedure syntax error

From Dev

How to create a stored procedure on SQL server 2014 for create table operation?

From Dev

tsql not firing (stored procedure via ssis) - SQL Server 2012

From Dev

Stored procedure in SQL Server 2012

From Dev

How to give table name dynamically in stored procedure in SQL Server 2012

From Dev

How to create a stored procedure in SQL Server Management Studio

From Dev

Create stored procedure on SQL Server

From Dev

SQL Server stored procedure update error

From Dev

Create and execute stored procedure in SQL Server

From Dev

Create and execute stored procedure with parameter in SQL Server

From Dev

How do you merge two different SQL Server 2012 database tables in single stored procedure?

From Dev

SQL Server 2012 - Pass stored procedure name to another stored procedure

From Dev

How to DROP and CREATE a table in SQL Server 2016 Stored Procedure

From Dev

How not to return select statements in SQL Server stored procedure if there is raiseerror below?

Related Related

  1. 1

    SELECT against stored procedure SQL Server

  2. 2

    SQL Server stored procedure select top 1 and update in one statement?

  3. 3

    SQL Server : create and call stored procedure

  4. 4

    Execute multiple statements and CTE in stored procedure in SQL Server 2012?

  5. 5

    How to create stored procedure in C#, then *save* it to SQL Server?

  6. 6

    In SQL Server 2012, how do I get the column name and data type from a view, function, or stored procedure?

  7. 7

    How to give table name dynamically in stored procedure in SQL Server 2012

  8. 8

    How to create a stored procedure in SQL Server Management Studio

  9. 9

    Create stored procedure in SQL Server from a MS Access update query

  10. 10

    SQL Server Stored Procedure select

  11. 11

    How to select the value of the first ID in SQL Server stored procedure?

  12. 12

    Rollback in single stored procedure for forloop & insert query in SQL Server 2012

  13. 13

    SQL Server : create and call stored procedure

  14. 14

    How to create dynamic parameters in SQL Server stored procedure

  15. 15

    SQL Server 2012 Insert Stored Procedure for N parameters

  16. 16

    SQL server create stored procedure syntax error

  17. 17

    How to create a stored procedure on SQL server 2014 for create table operation?

  18. 18

    tsql not firing (stored procedure via ssis) - SQL Server 2012

  19. 19

    Stored procedure in SQL Server 2012

  20. 20

    How to give table name dynamically in stored procedure in SQL Server 2012

  21. 21

    How to create a stored procedure in SQL Server Management Studio

  22. 22

    Create stored procedure on SQL Server

  23. 23

    SQL Server stored procedure update error

  24. 24

    Create and execute stored procedure in SQL Server

  25. 25

    Create and execute stored procedure with parameter in SQL Server

  26. 26

    How do you merge two different SQL Server 2012 database tables in single stored procedure?

  27. 27

    SQL Server 2012 - Pass stored procedure name to another stored procedure

  28. 28

    How to DROP and CREATE a table in SQL Server 2016 Stored Procedure

  29. 29

    How not to return select statements in SQL Server stored procedure if there is raiseerror below?

HotTag

Archive