Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #690

Dynamic SQL madness on SQL 2008

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Dynamic SQL madness on SQL 2008 JAW <willjamu@gmail.com> - 2011-09-30 11:59 -0700
  Re: Dynamic SQL madness on SQL 2008 Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-09-30 21:33 +0200

csiph-web