SP_EXECUTESQL problem with parameter type INT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SP_EXECUTESQL problem with parameter type INT

Author

Topic

RobinF
Starting Member

4 Posts

Posted - 2015-05-06 : 06:45:43

Hi !

I need your help please, I read the forum but I'm still having a persistant problem without solution. I want to pass a variable into parameters of an SP_EXECUTESQL, but the result value of the passed variable is always ZERO in my SQL statement.

Here is my code :

 
declare @counter int
declare @sql nvarchar(max)

SET @counter = (
        select COUNT(CREATED_DT)
        from USERS
        where CAST(CREATED_DT as date) = CAST(@DATE_TIME as date)
)

SET @sql = N'update DATA_REPORT set COUNTER_COLUMN = @counterB';
EXEC sp_executesql @sql, N'@counterB int', @counterB = @counter;

I do not have errors on execution but in my SQL there is always :
COUNTER_COLUMN = 0 (I checked before that my @counter was not returning 0, and it returns perfectly miscenaneous good values).

Any clues about this issue ?
Thank you very much :)
Robin

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 07:19:17

I would put this as the very next statement after your SP_ExecuteSQL statement


PRINT	'@@ROWCOUNT=' + COALESCE(CONVERT(varchar(20), @@ROWCOUNT), '[NULL]')
	+ ', @counter=' + COALESCE(CONVERT(varchar(20), @counter), '[NULL]')
	+ ', @DATE_TIME=' + COALESCE(CONVERT(varchar(24), @DATE_TIME), '[NULL]')
	+ ', CAST(@DATE_TIME)=' + COALESCE(CONVERT(varchar(24), CAST(@DATE_TIME as date)), '[NULL]')
	+ ', @sql=' + COALESCE(@sql + '[', '[NULL]')

RobinF
Starting Member

4 Posts

Posted - 2015-05-06 : 08:43:58

Hi Kristen,
Thank you but I do not have any debug screen with PRINT possibility, I'm using a TSQL software without that. It makes things harder.
Any other clues ?
Thank you

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 09:21:08

In that case I would change the PRINT to a SELECT. If you cannot see the output in the end report (I don;t know if it just "displays" all resultsets, or whether it expects only specific, pre-configured, resultsets??) then I would create a table and INSERT into that.

We have a "general purpose logging table" which has columns like Date/Time, User, Source (e.g. the Sproc name), Error Number (0-no error, 1,2,3,... just unique numbers that we assign so that if, for example, there are two error logging locations in a specific SProc then the first is called "1" and the second "2" etc. Just something "unique" so that when you find an error message in the Logging table you know where to look for the cause!!

Then a VARCHAR(MAX) column for the "message"

I would add an IDENTITY column too, it may make it easier to delete old/stale entries. We have a routine that deletes all log values after 7 days. In our case we are deleting about 10,000,000 rows per day, and a delete of that size needs careful management otherwise it causes blocking problems for other users! and Transaction Log growth. We find the IDENTITY column makes it easier for us to delete in "batches".

RobinF
Starting Member

4 Posts

Posted - 2015-05-06 : 11:11:57

Thanks, I will checked about all that and make a report when I can.
Regards

RobinF
Starting Member

4 Posts

Posted - 2015-05-07 : 09:29:17

Thanks for the help.

I solved my issue that way :


   declare @dyn_col_account varchar(40);
   declare @DATE_TIME nvarchar(50)

   SET @counter = (
            select COUNT(CREATED_DT)
            from USERS
            where CAST(CREATED_DT as date) = CAST(@DATE_TIME as date)
    )
    DECLARE @query1 nvarchar(max) = '
            UPDATE DATA_REPORT 
            SET ['+@dyn_col_account+'] = '+ @counter +' 
            WHERE TIME_DAY=cast('''+@DATE_TIME+''' as date)' 

    EXECUTE sp_executesql @query1

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 14:42:26

Dunno what @dyn_col_account containers, but if it could ever conceivably contain data entered by an end user - e.g. the Account Name they register with - then what happens if the value is:

;DROP DATABASE MyDatabase;

You ought to pass the parameters for the DATA (i.e. not the column name, you do need dynamic SQL for that) as you did in your original example. That is far more efficient (run time) but also safe from errors propagated by unexpected data and "SQL injection".

At a minimum you should use


SET ' + QUOTENAME(@dyn_col_account) + ' =

to encode any square brackets etc.

Your cast('''+@DATE_TIME+''' as date) is also at risk from mis-parsing if the format is an unexpected form, or incompatible with the locale / language settings in operation.

This would be far better, more efficient and safe:


DECLARE @query1 nvarchar(max) = '
            UPDATE DATA_REPORT 
            SET ' + QUOTENAME(@dyn_col_account) + ' = @counter
            WHERE TIME_DAY=@dtDATE' 

    SELECT @dtDATE = cast(@DATE_TIME as date)

    EXECUTE sp_executesql @query1,
            N'@counter int, @dtDATE date',
            @counter = @counter,
            @dtDATE = @dtDATE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources