CODEWITHSUNDEEP
sqlsql-server
S Nash
S Nash

Reputation: 2499

Strange exec and sp_executesql different behavior

I have a small stored procedure (SQl Server 2012).

IF I run it using:

exec spSelRegion @bOver21 = 1

I Get different results than if I run it using:

  exec sp_executesql N'spSelRegion',N'@bOver21 bit',@bOver21=1

What is the difference?

 ALTER   PROCEDURE [dbo].[spSelRegion]
    (
        @ID int = NULL
        ,@Name varchar(128) = NULL
        ,@OrderBy varchar(16) = 'ID'
        ,@bOver21 bit = null
    )
    AS 
    SELECT  distinct
        r.[ID],
        r.[Name],
        r.[dInserted],
        r.[sInserted],
        r.[dUpdated],
        r.[sUpdated],
        r.[timestamp]
    FROM 
        [dbo].[tblRegion] r
            left outer join tblCountyRegion cr
            on r.ID = cr.RegionNbr
    WHERE
        (r.[ID] = @ID or @ID is null) 
        AND (r.[Name] = @Name or @Name is null)
        AND (@bOver21 is null and r.[ID] >20
        OR (@bOver21 = 1 and r.[ID] > 20 and cr.IsActive=1) 
        OR (@bOver21 = 0 and r.[ID] >= 21 and r.id < 31))

I don't want to make this more complecated than it is . But after a microsoft update today , some stored procedures are now runnig using sp_executesql instead of Exec and this is source of the issue.

Upvotes: 0

Views: 185

Answers (1)

Jesse
Jesse

Reputation: 873

You would need to run your dynamic SQL with the parameter specified. Without that, it assumes a null value passed. So this:

  exec sp_executesql N'spSelRegion @bOver21 = @bOver21',N'@bOver21 bit',@bOver21=1

Upvotes: 5

Related Questions