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

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

Purge Utility

Show all headers | View raw

I have written a PURGE utility SPROC that works fine on simple SQL.

I am trying to compose complex SQL.

delete from PurgeUtility where TableName = 'TAB1'

INSERT INTO [DB].[dbo].[PurgeUtility]([TableName],[PurgeRange])
VALUES ('TAB1','BA IN (SELECT BA  FROM  [TAB1].[dbo].[ACCT] (nolock) WHERE  OFFICE > '  + '''001''' + '  OR  TYPE > 5)');


TableName	PurgeRange
TAB1	BA IN (SELECT BA  FROM  [DB].[dbo].[ACCT] (nolock) WHERE  OFFICE > '001'  OR  TYPE > 5)

Block of the code code

I use the table name in the PurgTable to generate the WHERE clause for the remainder of code.

BEGIN TRY
		 -- Load the data critera of rows (TABLOCK required for MINIMAL LOG) REP_TABLE
		 SET   @VSQLQuery = 'REP_' + @I_TABLE 
		 PRINT 'Fill REP Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
         SET   @VSQLQuery = 'INSERT INTO ' + @VSQLQuery + ' WITH (TABLOCK) SELECT * FROM TEMP_' + @I_TABLE+ ' WHERE ' + @VParameterDefinition
		 PRINT '===================================================='	
		 PRINT @VSQLQuery
		 PRINT '===================================================='	
         IF @I_MODE = 'SIMULATE'
		  BEGIN
		   PRINT 'Simulation mode this step is skipped'
		   PRINT '===================================================='
          END
		 ELSE
		  BEGIN
		   EXECUTE (@VSQLQuery)
 	       SELECT @Vcount = @@ROWCOUNT
		   PRINT 'Rows Loaded to ' + @VSQLQuery + ' = ' + CAST(@Vcount AS VARCHAR(10))  
		   PRINT '===================================================='	
	     END
		End TRY

When I run code with the apostrophes like the above I get an error saying that a syntax error exists.

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

Thread

Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-13 12:44 -0800
  Re: Purge Utility Erland Sommarskog <esquel@sommarskog.se> - 2013-02-13 22:25 +0100
  Re: Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-14 08:10 -0800
    Re: Purge Utility Erland Sommarskog <esquel@sommarskog.se> - 2013-02-14 21:00 +0100
  Re: Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-14 08:22 -0800
  Re: Purge Utility JAW <willjamu@gmail.com> - 2013-02-21 17:38 -0800
    Re: Purge Utility rja.carnegie@gmail.com - 2013-02-21 18:04 -0800

csiph-web