CODEWITHSUNDEEP
sql-server
Alan Carey
Alan Carey

Reputation: 23

Using multiple select statements inside of a stored procedure

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".

Upvotes: 0

Views: 625

Answers (2)

sheela w
sheela w

Reputation: 39

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

Upvotes: 0

marc_s
marc_s

Reputation: 754488

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 ;

Upvotes: 1

Related Questions