CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user3172765
user3172765

Reputation: 25

Update SQL Server stored procedure - switch columns in the parameters

I am trying to create a basic stored procedure as below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spUpdate1]
    @registration_id NCHAR(8),
    @registration_status INT
AS
BEGIN
    UPDATE dbo.Test1
    SET Registration_Status = @registration_status
    WHERE Registration_id = @registration_id;
END

This stored procedure works without issue, however the registration_id isn't the parameter I would like to pass in.

I have a three columns in my table: 

Reference_Number, Registration_id, Registration_Status

I would like to be able to pass in the Reference_Number as the parameter, from here the statement selects the registration_id from the relevant row in the table, then this is passed to the update statement to perform the update

Upvotes: 0

Views: 126

Answers (2)

Milney
Milney

Reputation: 6417

Based on your comment you can probably do this;

ALTER PROC [dbo].[spUpdate1]    
 @registration_status int,
 @reference_number nvarchar(max)      
AS    
BEGIN    
    UPDATE t1
    SET    registration_status = @registration_status 
    FROM   dbo.Test1 t1
    WHERE  CAST(t1.reference_number as NVARCHAR(MAX)) = @reference_number     
END

Although I recommend changing the data type of Reference_Number to nvarchar(max) instead of text...

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

this is what you wanted ? based on Reference_Number to get the Registration_id ?

ALTER PROC [dbo].[spUpdate1]

 @registration_id nchar(8),
 @registration_status int,
 @Reference_Number nchar(8)   -- added this

AS

BEGIN

    update t2
    set    Registration_Status = @registration_status 
    from   dbo.Test1 t1
           inner join dbo.Test1 t2 on t1.Registration_id = t2.Registration_id 
    where  t1.Reference_Number  = @Reference_Number 

END

Upvotes: 1

Related Questions