error for dynamic sql on sql server

i made a Stored procedure for dropping tables older than 6 months, it was running ok by failed recently and the error is:
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’

Here is the STP itself:
USE [OLAPPERF]
GO
/****** Object: StoredProcedure [dbo].[stp_removetbls] Script Date: 02/09/2009 12:52:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[stp_removetbls]
— Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON

DECLARE @table_name varchar( 128 )

DECLARE cur_tablenames CURSOR FOR select name from olapperf.sys.all_objects where type = ‘U’ and create_date<dateadd(month,-6,getdate())

DECLARE @sqlstring varchar(1000)

OPEN cur_tablenames
FETCH NEXT FROM cur_tablenames INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlstring = ‘drop table ‘+ @table_name

exec sp_executesql @sqlstring

FETCH NEXT FROM cur_tablenames INTO @table_name
END

CLOSE cur_tablenames
DEALLOCATE cur_tablenames
END

After searching the solution i found it is very easy, like here said(http://dotnetjunkies.com/WebLog/richard.dudley/archive/2004/09/29/27169.aspx):
If you are building dynamic SQL, you should declare your parameter as one of the accepted types (e.g., declare @sql nvarchar(4000)). If you are using a direct statement as your query, you need to preface it with the letter N, as seen below.

incorrect:
execute sp_executesql ‘select * from pubs.dbo.employee where job_lvl = @level’,
‘@level tinyint’,
@level = 35

correct:
execute sp_executesql N’select * from pubs.dbo.employee where job_lvl = @level’,
N’@level tinyint’,
@level = 35

So what i did is just change my sqlstring definition from varchar(1000) to nvarchar(1000), done! Everything works again.

Advertisements