table variable with other parameter insert

Question

Here is my procedure

CREATEPROCEDURE mysp
    @id int
    @param1 int
    @param2 int
    @param3 datetime
    @param4 bit
AS
    DECLARE @tv TABLE
    ( 
         param1 INT
    )
    INSERT INTO @tv ( param1 )
    SELECT param1 FROM tbl1 WHERE id = @id and IsActive = 1


    INSERT INTO tbl2
    (   
     id
    ,param1 
    ,param2 
    ,param3 
    ,param4 
    )
    VALUES
    (
     // all recors from @tv
    ,@param1 
    ,@param2 
    ,@param3 
    ,@param4 
   )

@tv will have multiple records i want to insert each value of param1 in @tv insert into another table along with other parameter param1, param2, param3, param4. Something like this

Is there a way of doing this. i dont want to use any cursor for this .

Thanks. help will be really appreciated.

I'm not quite understanding - you're not using your @paramX parameters in your SP, only the [@id]. — Dave.Gugg
– Dave.Gugg, Commented Dec 16, 2014 at 17:12

Dave.Gugg · Accepted Answer · 2014-12-16 17:27:12Z

1

It looks to me like you can simplify the whole thing to:

CREATE PROCEDURE mysp
    @id INT ,
    @param1 INT ,
    @param2 INT ,
    @param3 DATETIME ,
    @param4 BIT
AS
    INSERT  INTO tbl2
            ( id ,
              param1 ,
              param2 ,
              param3 ,
              param4
            )
            SELECT  param1 ,
                    @param1 ,
                    @param2 ,
                    @param3 ,
                    @param4
            FROM    tbl1
            WHERE   id = @id
                    AND isActive = 1

    GO

answered Dec 16, 2014 at 17:27

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

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

1 Comment

DotNetBeginner Over a year ago

That is a great idea. That prevents me from using a table variable thanks man. really appreciate it.

Pரதீப் · Accepted Answer · 2014-12-16 17:42:02Z

0

change your second insert like this.

INSERT INTO tbl2
            (id,param1,param2,param3,param4)
SELECT param1,@param1,@param2,@param3,@param4
FROM   @tv

or You can directly do a insert from tbl1 no need to declare a table variable (@tv)

INSERT INTO tbl2
            (id,param1,param2,param3,param4)
SELECT param1,@param1,@param2,@param3,@param4
FROM   tbl1 WHERE id = @id and IsActive = 1

edited Dec 16, 2014 at 17:42

answered Dec 16, 2014 at 17:24

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

table variable with other parameter insert

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related