CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresssms
strongmmc
strongmmc

Reputation: 315

T-SQL different result between code in stored and same code in query pane

I'm working on a procedure that should return a o or a 1, depending on result from parameter calculation (parameters used to interrogate 2 tables in a database).

When I excute that code in a query pane, it gives me the results i'm expecting. code looks like:

SELECT TOP 1 state, updDate INTO #history
FROM [xxx].[dbo].[ImportHystory]    WHERE (db = 'EB') ORDER BY addDate DESC;

IF (SELECT state FROM #history) = 'O' 
BEGIN       
        SELECT TOP 1 * INTO #process_status
        FROM yyy.dbo.process_status WHERE KeyName = 'eb-importer';

        IF(SELECT s.EndDate FROM #process_status s) IS NOT NULL
            IF (SELECT s.EndDate FROM #process_status s) > (SELECT h.updDate FROM #history h)
                BEGIN
                    IF (SELECT MessageLog from #process_status) IS NOT NULL SELECT 1;
                    ELSE SELECT 0;
                END
            ELSE 
                SELECT  1;
        ELSE
            SELECT 1;
    END
ELSE
    SELECT 0

I'm in the situation where EndDate from #process_status is null, so the execution returns 1. Once i put the SAME code in a SP, and pass 'EB' and 'eb-importer' as parameters, it returns 0.

And I exec the procedure with the data from the table right in front of me, so i know for sure that result is wrong.

Inside the procedure:

    ALTER PROCEDURE [dbo].[can_start_import] (@keyName varchar, @db varchar, @result bit output)
    DECLARE @result bit;

and replace every

    SELECT {0|1}

with

    SELECT @result = {0|1}

Executed from the Query pane:

    DECLARE @result bit;
    EXEC [dbo].[can_start_import] @KeyName = 'eb-importer', @db = 'EB', @result = @result OUTPUT
    SELECT @result AS N'@result'

Why does this happen?

Upvotes: 0

Views: 197

Answers (2)

strongmmc
strongmmc

Reputation: 315

So, apparently 2 things needed to be done:

  • set the length of the varchar parameter with a higher length,
  • filter with ' like ' instead of ' = ' for god knows what reason

Now it work as i expected to do, but i still don't get the different results between the query pane and the procedure if i use the equal...

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You are doing a top(1) query without an order by. That means SQL Server can pick any row from table1 that matches the where clause.

If you want to guarantee that the result is the same every time you execute that code you need an order by statement that unambiguously orders the rows.

Upvotes: 6

Related Questions