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]')
Go to Top of Page

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
Go to Top of Page

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".
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -