Dynamic SQL error

Question

I am trying to run the following stored procedure.

CREATE PROCEDURE NWR.GRADER 
    @YEAR AS NVARCHAR(4)
AS
BEGIN 
    DECLARE @sql1 as nvarchar(500) = 
        N'select a.*, b.pts as W_GRD_PTS
          into nwr.atp_matches_' + @YEAR + N'WGP
          from nwr.atp_matches_' + @YEAR + N' a 
          left join NWR.RNK_VAL as b on a.winner_rank >= low 
                                     and a.winner_rank<= high

          alter table nwr.atp_matches_' + @YEAR + N'WGP
             add L_GRD_PTS smallint  null

          UPDATE nwr.atp_matches_' + @YEAR + N'WGP
          SET L_GRD_PTS = C.pts 
          FROM NWR.RNK_VAL C 
          WHERE loser_rank >= LOW AND loser_rank <= HIGH;'

    --print (@sql1);
    EXEC sys.sp_execute @sql1;
end;

exec nwr.GRADER @year='2016';

However I'm getting the following error

Msg 214, Level 16, State 2, Procedure sp_execute, Line 1
Procedure expects parameter '@handle' of type 'int'.

But when I switch to the Print rather than EXEC I get the code exactly as I expected as below:

select a.*, b.pts as W_GRD_PTS
into nwr.atp_matches_2016WGP
from nwr.atp_matches_2016 a 
left join NWR.RNK_VAL as b on a.winner_rank >= low and a.winner_rank <= high

alter table nwr.atp_matches_2016WGP
    add L_GRD_PTS smallint  null

UPDATE nwr.atp_matches_2016WGP
SET L_GRD_PTS=C.pts 
FROM NWR.RNK_VAL C 
WHERE loser_rank>= LOW AND loser_rank<= HIGH;

Can anyone explain what I'm doing wrong?

It's this... EXEC sys.sp_execute @sql1... It should be this... EXEC sys.sp_executesql @sql1; — Jason A. Long
– Jason A. Long, Commented Sep 2, 2017 at 1:00
Also: it's always a bad idea to mix DML (data manipulation - select, insert, delete) with DDL (data definition - alter table etc.) in a single query. Every time you run this stored procedure, a new column L_GRD_PTS will be added to your table...... actually, this will only work once, and then fail.... don't do this! Keep you DDL separate from your other code! — marc_s
– marc_s, Commented Sep 2, 2017 at 7:13

Jason A. Long · Accepted Answer · 2017-09-02 01:50:23Z

3

The reson for the error is that you're using sys.sp_execute instead of sys.sp_executesql.

On a side note, I couldn't help noticing that it looks like your dynamic query can be simplified a bit... Just throwing it out there...

CREATE PROCEDURE NWR.GRADER 
    @YEAR AS NVARCHAR(4),
    @Debug BIT = 0      -- 0=execute d-sql & 1=print d-sql...
AS
BEGIN 
    SET NOCOUNT ON;

    DECLARE @sql1 as nvarchar(500) =N'
SELECT 
    a.*, 
    W_GRD_PTS = b.pts,
    L_GRD_PTS = c.pts
    into nwr.atp_matches_' + @YEAR + N'WGP
FROM 
    nwr.atp_matches_' + @YEAR + N' a 
    LEFT JOIN NWR.RNK_VAL as b
        ON a.winner_rank >= b.low 
        AND a.winner_rank <= b.high
    LEFT JOIN NWR.RNK_VAL c
        ON a.loser_rank >= c.low 
        AND a.loser_rank<= c.high;'

    IF @Debug = 1
    BEGIN
        PRINT(@sql1);
    END;
    ELSE
    BEGIN 
        EXEC sys.sp_executesql @sql1;
    END;
END;
GO

edited Sep 2, 2017 at 1:50

answered Sep 2, 2017 at 1:05

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

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

1 Comment

S3S Over a year ago

Throw that strike!

Collectives™ on Stack Overflow

Dynamic SQL error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related