Using multiple select statements inside of a stored procedure

Question

I am setting up a database that will hold a transaction ticket. The transaction ticket is stored via a stored procedure.

The transaction item table has the following foreign keys:

Driver Id  
Producer Id   
Carrier Id

I would like to store the following based by using "SELECT" and "INSERT INTO" by referencing the corresponding foreign keys, ie. dbo.Driver DriverId

"dbo.Driver DriverFisrtName"    
"dbo.Driver DriverLastName"    
"dbo.Producer ProducerName"    
"dbo.Carrier CarrierName"

The problem I am having is understanding the best method to perform this inside of a stored procedure.

ALTER PROCEDURE [dbo].[TransactionTicket] 
    @EstBarrels INT,
    @DrvierId INT,
    @CarrierId INT,
    @ProducerId INT,
    @LaneId INT,
    @StartTime VARCHAR(50),
    @StartDate VARCHAR(50),
    @EndTime VARCHAR(50),
    @EndDate VARCHAR(50),
    @MeterGsvStart REAL,
    @MeterGsvStop REAL,
    @MeterNsvStart REAL,
    @MeterNsvStop REAL,
    @TransactionNetTotal REAL,
    @TransactionGsvTotal REAL,
    @AvgTemp REAL,
    @UserTicket INT,
    @Density REAL,
    @MeterFactor REAL,
    @AvgBsw REAL,
    @Id INT OUTPUT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @ProducerName VARCHAR(50),
    @CarrierName VARCHAR(50)      
AS
BEGIN
    SET NOCOUNT ON;

    --Declare @DriverId INT
    INSERT INTO @FirstName
        SELECT FirstName 
        FROM dbo.driver  
        WHERE DriverId = @DrvierId ;

    INSERT INTO @LastName
        SELECT LastName 
        FROM dbo.driver  
        WHERE DriverId = @DrvierId ;

    INSERT INTO @ProducerName
        SELECT ProducerName 
        FROM dbo.Producer  
        WHERE ProducerId = @ProducerId ;

    INSERT INTO @CarrierName
        SELECT CarrierName 
        FROM dbo.Carrier  
        WHERE CarrierId = @CarrierId ;

    -- Insert statements for procedure here
    INSERT INTO TransactionItem (DriverId, CarrierId, ProducerId, LaneId, 
AverageTemp, MeterStartGSV,
            MeterStopGSV, MeterStartNSV, MeterStopNSV, 
TotalBarrelsGSV, TotalBarrelsNSV, EstimatedBarrels,
            Density, MeterFactor, StartTime, StartDate, 
EndTime, EndDate, AvgBsw, FirstName, LastName, ProducerName, CarrierName)
    VALUES (@DrvierId, @CarrierId, @ProducerId, @LaneId, @AvgTemp, 
(@MeterGsvStart/100), (@MeterGsvStop/100), (@MeterNsvStart/100),
        (@MeterNsvStop/100), (@TransactionGsvTotal/100), 
(@TransactionNetTotal/100), @EstBarrels, @Density, @MeterFactor,
        @StartTime, @StartDate, @EndTime, @EndDate, @AvgBsw, 
@FirstName, @LastName, @ProducerName, @CarrierName);

    SET @Id = SCOPE_IDENTITY()
    RETURN @Id
END

Here are the errors I get:

Msg 1087, Level 16, State 1, Procedure TransactionTicket, Line 54
Must declare the table variable "@FirstName".

Msg 1087, Level 16, State 1, Procedure TransactionTicket, Line 56
Must declare the table variable "@LastName".

Msg 1087, Level 16, State 1, Procedure TransactionTicket, Line 58
Must declare the table variable "@ProducerName".

Msg 1087, Level 16, State 1, Procedure TransactionTicket, Line 60
Must declare the table variable "@CarrierName".

Is this mysql or sql-server ? These are two different RDBMS. — GMB
– GMB, Commented Jan 27, 2019 at 2:08
You are trying to insert values in tables, which do not exist. — Slava Murygin
– Slava Murygin, Commented Jan 27, 2019 at 2:29
@Slava Murygin I dont understand? all tables in the posted code exist or am I mis understanding? — Alan Carey
– Alan Carey, Commented Jan 27, 2019 at 2:45
All your insert into @blah should be select @blah=blah from dbo.blahblah — Alex Kudryashev
– Alex Kudryashev, Commented Jan 27, 2019 at 3:24

marc_s · Accepted Answer · 2019-01-27 07:38:25Z

1

The INSERT INTO statement only works against a table - not against a single variable.

So instead of this:

INSERT INTO @FirstName
    SELECT FirstName 
    FROM dbo.driver  
    WHERE DriverId = @DrvierId ;

you really need this:

SELECT @FirstName = FirstName 
FROM dbo.driver  
WHERE DriverId = @DrvierId ;

and also, you can set several variables at the same time from a SELECT - like this:

SELECT 
    @FirstName = FirstName,
    @LastName = LastName
FROM dbo.driver  
WHERE DriverId = @DrvierId ;

answered Jan 27, 2019 at 7:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sheela w · Accepted Answer · 2019-01-27 10:49:08Z

You can use cursors to fetch multiple fields data from table into variables.

ALTER PROCEDURE [dbo].[TransactionTicket] 
@EstBarrels INT,
@DrvierId INT,
@CarrierId INT,
@ProducerId INT,
@LaneId INT,
@StartTime VARCHAR(50),
@StartDate VARCHAR(50),
@EndTime VARCHAR(50),
@EndDate VARCHAR(50),
@MeterGsvStart REAL,
@MeterGsvStop REAL,
@MeterNsvStart REAL,
@MeterNsvStop REAL,
@TransactionNetTotal REAL,
@TransactionGsvTotal REAL,
@AvgTemp REAL,
@UserTicket INT,
@Density REAL,
@MeterFactor REAL,
@AvgBsw REAL,
@Id INT OUTPUT,
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@ProducerName VARCHAR(50),
@CarrierName VARCHAR(50)      
AS
BEGIN
    SET NOCOUNT ON;

    Declare nextctr cursor for
    SELECT FirstName ,LastName
    FROM dbo.driver  
    WHERE DriverId = @DrvierId
    Open nextctr
    Fetch next from nextctr into @FirstName, @LastName
    Close nextctr
    Deallocate nextctr

    --or you can use set operator for retrieving single value
    Set @ProducerName =(
    SELECT ProducerName 
    FROM dbo.Producer  
    WHERE ProducerId = @ProducerId)

    Set @CarrierName = 
    (SELECT CarrierName 
    FROM dbo.Carrier  
    WHERE CarrierId = @CarrierId)

    -- Insert statements for procedure here
    INSERT INTO TransactionItem (DriverId, CarrierId, 
    ProducerId, LaneId, 
    AverageTemp, MeterStartGSV,
    MeterStopGSV, MeterStartNSV, MeterStopNSV, 
    TotalBarrelsGSV, TotalBarrelsNSV, EstimatedBarrels,
    Density, MeterFactor, StartTime, StartDate, 
    EndTime, EndDate, AvgBsw, FirstName, LastName, 
    ProducerName, CarrierName)
    VALUES (@DrvierId, @CarrierId, @ProducerId, @LaneId, 
   @AvgTemp, 
   (@MeterGsvStart/100), (@MeterGsvStop/100), 
   (@MeterNsvStart/100),
   (@MeterNsvStop/100), (@TransactionGsvTotal/100), 
   (@TransactionNetTotal/100), @EstBarrels, @Density, 
   @MeterFactor,
   @StartTime, @StartDate, @EndTime, @EndDate, 
   @AvgBsw, 
   @FirstName, @LastName, @ProducerName, 
   @CarrierName);

    SET @Id = SCOPE_IDENTITY()
    RETURN @Id
END

You can. But I wouldn't advise it. There is a large performance penalty and it isn't something you typically want to do. Insert into #temp table or define and insert into a table variable (you usually don't want a table variable either - temp table is a table variable until it has x rows - will most-often perform better)

Collectives™ on Stack Overflow

Using multiple select statements inside of a stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related