best practive to pass multiple table to stored procedure in sql server

Breakable Bug answered on May 10, 2020 Popularity 8/10 Helpfulness 2/10

answer best practive to pass multiple table to stored procedure in sql server

related best practive to pass multiple table to stored procedure in sql server

best practive to pass multiple table to stored procedure in sql server

Comment

DECLARE @primaryTVP primary_tbltype
DECLARE @relatedTVP related_tbltype

INSERT INTO @primaryTVP values (1, 'John', 'Cleese')
INSERT INTO @primaryTVP values (2, 'Eric', 'Idle')
INSERT INTO @primaryTVP values (3, 'Graham', 'Chapman')

INSERT INTO @relatedTVP values (1, '29310918', 28934.33)
INSERT INTO @relatedTVP values (2, '123123', 3418.11)
INSERT INTO @relatedTVP values (2, '33333', 666.66)
INSERT INTO @relatedTVP values (3, '554433', 22.22)
INSERT INTO @relatedTVP values (3, '239482', 151515.15)


EXEC MySproc @primaryTVP, @relatedTVP;

Popularity 8/10 Helpfulness 2/10 Language sql

Source: stackoverflow.com

Tags: procedure sql sql-server

Link to this answer
Share Copy Link

Contributed on May 10 2020

Breakable Bug

0 Answers Avg Quality 2/10

Closely Related Answers

best practive to pass multiple table to stored procedure in sql server

Comment

CREATE PROCEDURE MySproc
    @PrimaryTable primary_tbltype READONLY,
    @RelatedTable related_tbltype READONLY
AS
BEGIN
DECLARE @CurrentKey INT
DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)
DECLARE @AccountTotal MONEY

DECLARE PersonCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT personkey, firstname, lastname FROM @PrimaryTable
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
WHILE @@FETCH_STATUS= 0 BEGIN

    SELECT @AccountTotal = SUM(accountbalance) FROM @RelatedTable 
        WHERE fk_personkey = @CurrentKey

    PRINT @FirstName + ' ' + @LastName + ' - account total: ' + CONVERT(VARCHAR(30), @AccountTotal)

    FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
END
END;

Popularity 8/10 Helpfulness 1/10 Language sql

Source: stackoverflow.com

Tags: sql procedure sql-server

Link to this answer
Share Copy Link

Contributed on May 10 2020

Breakable Bug

0 Answers Avg Quality 2/10

best practive to pass multiple table to stored procedure in sql server

Contents

More Related Answers

best practive to pass multiple table to stored procedure in sql server

Closely Related Answers

best practive to pass multiple table to stored procedure in sql server

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.