CODEWITHSUNDEEP
sql-servert-sql
thequadge
thequadge

Reputation: 79

Dynamic SQL error

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?

Upvotes: 1

Views: 1534

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

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

Upvotes: 3

Related Questions