running sp_execute query epects parameter @statement

Question

Hi,

I'm not sure how to resolve this error:

*Procedure or Function 'sp_executesql' expects parameter '@statement', which was not supplied.*

for this query:

DECLARE @a INT 
DECLARE @b VARCHAR 
SET @a = 1

WHILE @a < 30
BEGIN
set @b = @a  
exec sp_executesql update source_temp set pmt_90_day = pmt_90_day + convert(money,'trans_total_'+@b)+N'
    N'where convert(datetime,'effective_date_'+@b)+N' <= dateadd(day,90,ORSA_CHARGE_OFF_DATE)
    and DRC_FLAG_'+@b = 'C'''

SET @a = @a + 1
END

Also, can you help me understand the proper usage of N' and if its done correctly in this statement.

Thanks for your assistance.

Answer 1

The "sp_executesql" stored proc expects a single string parameter @statement to execute.

Your string is totally out of whack here..... you need to prefix any "fixed" string parts with a N'......' to make them Unicode strings, but this is definitely not properly the case here.

I think you might want to try this:

DECLARE @a INT 
DECLARE @b VARCHAR(2)

SET @a = 1

DECLARE @statement NVARCHAR(500)

WHILE @a < 30
BEGIN
    SET @b = CAST(@a AS VARCHAR(2))

    SET @statement = 
     N'update source_temp set pmt_90_day = pmt_90_day + ' + 
             'convert(money, ''trans_total_' + @b + ''') ' + 
             'where convert(datetime, ''effective_date_' + @b + ''')' +
             ' <= DATEADD(DAY, 90, ORSA_CHARGE_OFF_DATE) ' +
             'and DRC_FLAG_' + @b + ' = ''C'''

    exec sp_executesql @statement

    SET @a = @a + 1
END

Does this work and do what you expect it to do??

Explanation: the N'.........' delimits the whole string which contains the SQL statement. Any apostrophe inside that has to be duplicated ("escaped"). I hope I understood your logic correctly.

Marc

Answer 2

The sp expects a variable of type string, not a SQL statement. Wrap you SQL in single quotes:

 exec sp_executesql 'some SQL statement';

When you do that, escape any single quotes in the statement by replacing each single quote with two single quotes:

exec sp_executesql 'select 'AB' from dual';  -- wrong

exec sp_executesql 'select ''AB'' from dual';  -- right

I have no idea what N is. What do you think it is? Is it some sort of cast to a character set? Why do you think it's necessary?