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

Dynamic SQL madness on SQL 2008

Path	csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder3.hal-mli.net!news.glorb.com!postnews.google.com!m5g2000vbe.googlegroups.com!not-for-mail
From	JAW <willjamu@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Dynamic SQL madness on SQL 2008
Date	Fri, 30 Sep 2011 11:59:13 -0700 (PDT)
Organization	http://groups.google.com
Lines	81
Message-ID	<67a6ef76-3dbb-4225-9372-b93efcfa36ed@m5g2000vbe.googlegroups.com> (permalink)
NNTP-Posting-Host	65.243.68.237
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1
X-Trace	posting.google.com 1317409154 13506 127.0.0.1 (30 Sep 2011 18:59:14 GMT)
X-Complaints-To	groups-abuse@google.com
NNTP-Posting-Date	Fri, 30 Sep 2011 18:59:14 +0000 (UTC)
Complaints-To	groups-abuse@google.com
Injection-Info	m5g2000vbe.googlegroups.com; posting-host=65.243.68.237; posting-account=5-qvrwoAAADE2AtfD1xgU3Rd3ZSqU1Of
User-Agent	G2/1.0
X-Google-Web-Client	true
X-Google-Header-Order	ARLUEHNKC
X-HTTP-UserAgent	Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB7.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; InfoPath.1; MS-RTC LM 8; MS-RTC EA 2; .NET4.0C; .NET4.0E),gzip(gfe)
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:690

Show key headers only | 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