Problem with parameters passing in dynamic t-sql

Question

What do I do incorrectly with passing parameters in this stored procedure?:

ALTER PROCEDURE [dbo].[Unload]
    @branch_id INT,
    @fl_close BIT
AS
BEGIN
    SET NOCOUNT ON;
    IF object_ID('TempDb..##ttable','U') IS NOT NULL
        DROP TABLE ##ttable;

    DECLARE @ExecStr varchar(4000),
            @paramlist varchar(4000);
    SElECT @ExecStr = 'CREATE TABLE #ttables
    (
         numid INT identity(1,1),
         uso_id INT
    )     

    INSERT INTO #ttables (uso_id)
    SELECT u2.uso_id FROM Uso u2 WHERE u2.branch_id = @branch_id;'

    SElECT @ExecStr = @ExecStr + 'SELECT identity(int,1,1) as id, c.client_code,
          a.account_num, u.uso_id
    INTO ##ttable
    FROM accounts a INNER JOIN Clients c ON 
          c.id = a.client_id INNER JOIN Uso u ON c.uso_id = u.uso_id INNER JOIN 
          Magazin m ON a.account_id = m.accoun t_id INNER JOIN #ttables tt 
          ON u.uso_id = tt.uso_id 
    WHERE m.status_id IN (1,5) AND a.fl_close = @fl_close AND 
          u.branch_id = @branch_id';

    SElECT @ExecStr = @ExecStr + ';  
            SELECT  id,
                client_code,
                abs_account_num,
                account_new_num,
                account_new_open_date,
                account_close_date,
                uso_id
            FROM ##ttable;
            DROP TABLE #ttables;
            DROP TABLE ##ttable;'

   SELECT @paramlist = N'@branch_id INT, @fl_close BIT';
   EXEC sp_executesql @ExecStr, @paramlist, @branch_id, @fl_close;

@Ocaso Protal: no, there is not. But parameters values don't pass. — DmitryB
– DmitryB, Commented May 25, 2011 at 6:53
@Ocaso Protal: I brought only a part of the stored procedure to test — DmitryB
– DmitryB, Commented May 25, 2011 at 6:56
What happens when you use this: EXEC sp_executesql @ExecStr, @paramlist, @branch_id=@branch_id, @fl_close=@fl_close; — Ocaso Protal
– Ocaso Protal, Commented May 25, 2011 at 7:00

Martin Smith · Accepted Answer · 2011-05-25 07:01:19Z

3

The parameter values do pass fine.

CREATE PROCEDURE [dbo].[Unload]
    @branch_id INT,
    @fl_close BIT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ExecStr NVARCHAR(4000),
            @paramlist NVARCHAR(4000);
    SELECT @ExecStr = 'select @branch_id, @fl_close'

   SELECT @paramlist = N'@branch_id INT, @fl_close BIT';

   EXEC sp_executesql @ExecStr, @paramlist, @branch_id, @fl_close

   END

  GO

  EXEC [dbo].[Unload] 20,1

Returns

----------- -----
20          1

answered May 25, 2011 at 7:01

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Collectives™ on Stack Overflow

Problem with parameters passing in dynamic t-sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related