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

Purge Utility

X-Received	by 10.224.72.199 with SMTP id n7mr13662123qaj.5.1360788272331; Wed, 13 Feb 2013 12:44:32 -0800 (PST)
X-Received	by 10.49.1.43 with SMTP id 11mr1495187qej.29.1360788272309; Wed, 13 Feb 2013 12:44:32 -0800 (PST)
Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder2.hal-mli.net!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!p13no16187708qai.0!news-out.google.com!k2ni24985qap.0!nntp.google.com!p13no14987641qai.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Wed, 13 Feb 2013 12:44:32 -0800 (PST)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=65.243.68.237; posting-account=gkZ_jAgAAACs0Bql-4AE5JwCuqQkEOoD
NNTP-Posting-Host	65.243.68.237
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<695679d9-3eb9-44c8-a7fb-2e60ef2b8373@googlegroups.com> (permalink)
Subject	Purge Utility
From	JAW <jwilliam@aglresources.com>
Injection-Date	Wed, 13 Feb 2013 20:44:32 +0000
Content-Type	text/plain; charset=ISO-8859-1
X-Received-Bytes	2618
Xref	csiph.com comp.databases.ms-sqlserver:1385

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