Finding and using value within stored procedure

Question

I am trying to find a value and then use this within another statement within the same SQL stored procedure . However I am lost with the syntax (if this is even possible).

Below I have posted my code as this will be the easiest way to explain fully.

CREATE PROCEDURE FindRuidForUser
    @Email nvarchar (160),
    @RUId int,
    @DeskLocation nvarchar(160),
    @ExtraInformation nvarchar(160),
    @PrimaryWorkStation nvarchar(160),
    @Date datetime
AS
    SELECT RUId 
    FROM RegisteredUsers
    WHERE Email = @Email

RUId = @RUId

Then

    INSERT INTO diffrenttable (RUId, PrimaryWorkStation, DeskLocation, ExtraInformation, TimeCreated)
    VALUES (@RUId, @PrimaryWorkStation, @DeskLocation, @ExtraInformation, @TimeCreated)

I would first like to find the RUId and then I would like to insert this into a separate table along with the rest of the variables I will be using.

Any help is greatly appreciated.

Thank you !

You should be able to use `SELECT TOP 1 RUId FROM RegisteredUsers WHERE Email = @Email` in place of `@RUId` in the `INSERT` statement. But you might want to add some check for if there actually is an `Email = @Email`. — Andrew Morton, Jan 29 '20 at 16:22

score 1 · Answer 1 · answered Jan 29 '20 at 16:24

A little adjustment in your stored proc: In your

select top 1 @RUId = RUId from RegisteredUsers
where Email = @Email

I added a top in case you have more RUI returned for one Email.

create procedure FindRuidForUser

@Email nvarchar (160),
@RUId int,

@DeskLocation nvarchar(160),
@ExtraInformation nvarchar(160),
@PrimaryWorkStation nvarchar(160),
@Date datetime


as

select top 1 @RUId = RUId from RegisteredUsers
where Email = @Email



Then

INSERT into diffrenttable (RUId,PrimaryWorkStation,DeskLocation,ExtraInformation, TimeCreated)
values (@RUId, @PrimaryWorkStation, @DeskLocation, @ExtraInformation, @TimeCreated)

score 1 · Answer 2 · edited Jan 29 '20 at 16:28

1

Where you have this following code:

select RUId from RegisteredUsers
where Email = @Email

RUId = @RUId

You should be able to do the following instead:

select @RUId = RUId from RegisteredUsers
where Email = @Email

Alternatively, you could have written:

set @RUId = (select RUId from RegisteredUsers
where Email = @Email)

Your current line of code wouldn't work, because the select statement isn't assigning your request to anything. Setting the parameter to what you're looking for should do the trick.

edited Jan 29 '20 at 16:28

SS_DBA

2,403
1
11
15

answered Jan 29 '20 at 16:25

Tiny Haitian

479
2
10

1

@SS_DBA, thanks for the catch. Definite oversight on my end. – Tiny Haitian Jan 29 '20 at 16:47

Suraj Kumar · Accepted Answer · 2020-01-29T16:37:06.243

I think you are looking for the syntax. Note this is just a syntax how I have used created a variable of the name @SomeNewRUID. After creating the value has been assigned and used in the insert statement.

create procedure FindRuidForUser
@Email nvarchar (160),
@RUId int,
@DeskLocation nvarchar(160),
@ExtraInformation nvarchar(160),
@PrimaryWorkStation nvarchar(160),
@Date datetime
as
Begin
   declare @SomeNewRUID int
   select @SomeNewRUID = RUId from RegisteredUsers where Email = @Email

   INSERT into diffrenttable (RUId,PrimaryWorkStation,DeskLocation,ExtraInformation, 
  TimeCreated)
   values (@SomeNewVariable, @PrimaryWorkStation, @DeskLocation, @ExtraInformation, @TimeCreated)
End

Here is another example from the reference to know more how to use the variable in a SQL Server stored procedure.

CREATE  PROC uspGetProductList(
    @model_year SMALLINT
) AS 
BEGIN
    DECLARE @product_list VARCHAR(MAX);

    SET @product_list = '';

    SELECT
        @product_list = @product_list + product_name 
                        + CHAR(10)
    FROM 
        production.products
    WHERE
        model_year = @model_year
    ORDER BY 
        product_name;

    PRINT @product_list;
END;

Note, Only one value should come from the select statement while assigning it into that variable otherwise, it will give the error like:

SQL Server Subquery returned more than 1 value error.

Finding and using value within stored procedure

3 Answers3