I am working on an SPROC that I want to pass dynamic SQL to purge
tables. Using SELECT to test.

http://msdn.microsoft.com/en-us/library/ms187926.aspx


The proc compiles. It works if I don't use dynamic SQL.

ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine
ErrorMessage
214 16 2 sp_executesql 1 Procedure expects parameter '@statement' of
type 'ntext/nchar/nvarchar'.



create table arch_test (data varchar(20), timestamp datetime)

go
--
declare @counter int
set @counter = 0
while @counter <= 100
begin
set @counter = @counter + 1
insert into arch_test values (@counter,getdate() - @counter)
print 'The counter is ' + cast(@counter as char)
end

go

select * from arch_test

go
--
IF OBJECT_ID ( 'dbo.usp_PurgeAgedTables', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.usp_PurgeAgedTables;
GO

CREATE Procedure dbo.usp_PurgeAgedTables
( @I_Table varchar(128), @I_PurgeCol varchar(128), @I_PurgeRange
smallint )
AS
BEGIN
SET NOCOUNT ON;
DECLARE @V_SQLQuery VARCHAR(400),
@V_ParameterDefinition VARCHAR(100);
--@V_PurgeRange SMALLINT;

SET @V_SQLQuery = 'SELECT * FROM ' + @I_Table + ' WHERE ' +
@I_PurgeCol + ' < getdate() - @V_PurgeRange';
SET @V_ParameterDefinition = '@V_PurgeRange SMALLINT'
PRINT @V_SQLQuery;
PRINT @V_ParameterDefinition;
/* Execute Transact-SQL String */
BEGIN TRANSACTION;
BEGIN TRY
--SELECT COUNT(*) FROM ARCH_TEST;
-- Clean up errors.
EXECUTE sp_executesql @V_SQLQuery, @V_ParameterDefinition,
@V_PurgeRange=@I_PurgeRange;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END;

go

exec dbo.usp_PurgeAgedTables 'arch_test','Timestamp',30

Hi JAW,

The error message tells you what the problem is. The parameter for
sp_executesql that holds the statement should be of type
ntext/nchar/nvarchar. You have declared it as varchar. Change that to
nvarchar (and ditto for the parameter that holds the parameter list)
and it will work.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis