CODEWITHSUNDEEP
sql-serverstored-procedures
Leigh P
Leigh P

Reputation: 19

Stored Procedure Recordset not matching SQL

I have a very simple stored procedure, essentially a SELECT statement based on a view based on a single table.

When I run the SELECT statement separately, I am getting one set of results, and when I run the stored procedure, I am getting a different one (actually a subset of the first).

This is using SQL Server 2008 Management Studio.

Does anybody have any ideas how this can happen?

I have tried recompiling the stored procedure and also dropping and recreating it, and the values do not change.

The underlying table is actually a SYNONYM to a table in a different database but on the same server.

CREATE PROCEDURE [dbo].[usp_RPT_SelectCostCentre2]
     (@DataAreaId nvarchar(3))
AS
BEGIN
    SELECT 
        'All' AS 'NUM',
        ' All Cost Centres' AS 'Description'

    UNION

    SELECT DISTINCT 
        d.NUM,
        CASE
           WHEN d.DESCRIPTION='' THEN d.NUM
           ELSE d.DESCRIPTION
        END AS 'Description'
    FROM 
        v_BAS2_Dimensions AS d
    WHERE 
        d.DataAreaId = @DataAreaId
        AND d.DIMENSIONCODE=1
    ORDER BY 
        NUM
END

Individual SQL Statement run to test:

    @DataAreaId nvarchar(4)
    --
    SET @DataAreaID='bkt2'

        SELECT 'All' AS 'NUM'
              ,' All Cost Centres' AS 'Description'
        UNION
        SELECT DISTINCT d.NUM
                       ,CASE
                          WHEN d.DESCRIPTION='' THEN d.NUM
                          ELSE d.DESCRIPTION
                        END AS 'Description'
          FROM v_BAS2_Dimensions AS d
          WHERE d.DataAreaId=@DataAreaId
            AND d.DIMENSIONCODE=1
          ORDER BY NUM

Upvotes: 1

Views: 46

Answers (1)

gofr1
gofr1

Reputation: 15987

You run 

DECLARE @DataAreaId nvarchar(4)

And in SP:

@DataAreaId nvarchar(3)

Just fix in SP nvarchar(4) or even use nvarchar(max)

Upvotes: 1

Related Questions