Apostrophe's and Variables Headache

Question

Post reply

Apostrophe's and Variables Headache

Paul Clark-418949

Old Hand

Points: 378
More actions
April 15, 2010 at 6:08 am

#156875

Hi All,

I have a simple SP that I'm trying to write that adds the row count of a number of tables into another table. I am just having a problem with Apostrophe's in my code and Variables. This always seems to give me headaches lol. Any help would be much appreciated.

The Code I have so far :

DECLARE @TSQL1 varchar (8000), @tsql2 varchar (8000), @TSQL3 varchar (8000), @TSQL4 varchar (8000), @SoftTable varchar (126), @HardTable varchar (126), @WideTable varchar (126), @CountSoft varchar (126), @CountHard varchar (126), @CountWide varchar (126)

-- Clear table db0.ImporttablesRowCount

TRUNCATE TABLE dbo.ImportTablesRowCount

DECLARE table_list CURSOR FOR

SELECT InitialImportTable FROM ptImportFileList WHERE FileToImport <> 'NA'

OPEN table_list

FETCH NEXT FROM table_list INTO @SoftTable

WHILE @@FETCH_STATUS = 0

BEGIN

-- Insert Table Name into dbo.ImportTablesRowCount

SET @TSQL1 = 'INSERT INTO dbo.ImportTablesRowCount (TableName) VALUES ('''+@SoftTable+''')'

EXEC (@TSQL1)

IF @@ERROR<>0 PRINT @TSQL1

-- Insert Row Count of SoftTable

SET @CountSoft = 'SELECT COUNT(*) FROM '+@SoftTable

SET @tsql2 = 'UPDATE dbo.ImportTablesRowCount SET SoftTable = ('+@CountSoft+') WHERE TableName = '''+@SoftTable''

EXEC (@TSQL2)

IF @@ERROR<>0 PRINT @tsql2

-- Insert Row Count of HardTable

SET @HardTable = 'h'+SUBSTRING(@SoftTable, 2, 126)

SET @CountHard = 'SELECT COUNT(*) FROM '+HardTable

SET @TSQL3 = 'UPDATE dbo.ImportTablesRowCount SET HardTable = ('+@CountHard+') WHERE TableName = '''+@HardTable''

EXEC (@TSQL3)

IF @@ERROR<>0 PRINT @TSQL3

-- Insert Row Count of WideTable

SET @Widetable = 'w'+@SoftTable

SET @CountWide = 'SELECT COUNT(*) FROM '+WideTable

SET @TSQL4 = 'UPDATE dbo.ImportTablesRowCount SET WideTable = ('+@CountWide+') WHERE TableName = '''+@WideTable''

EXEC (@TSQL4)

IF @@ERROR<>0 PRINT @TSQL4

FETCH NEXT FROM table_list INTO @SoftTable

END

CLOSE table_list

DEALLOCATE table_list[/color]

The Apostrophe's I'm having issues with are the ones that are on the end of the SET commands for the variables @tsql2, @TSQL3, @TSQL4.

The error I get is Incorrect syntax near ''.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul Clark-418949 Old Hand Points: 378 More actions · Answer 1

Paul Clark-418949

Old Hand

Points: 378

April 15, 2010 at 6:28 am

#1152054

Sorry to anyone who read this.

Hope it gave you a laugh, worked out the problem.....

PICNIC (Problem In Chair Not In Computer) on my part forgot the + between the last variable and the Apostrophe's!!!!

Hope this helps someone else out who is having a similar issue. lol

Lowell SSC Guru Points: 323482 More actions · Answer 2

Paul Clark-418949 (4/15/2010)

PICNIC (Problem In Chair Not In Computer)

hehe in my shop we call that a problem with the CKI (Chair to Keyboard Interface);

it can often be fixed witht he application of caffiene

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

Mate, provide us the create table script and sample rows to insert and check your code... and also i noticed that u are update a table using the count of table, but u are not executing the count(*) statement prior to the update statement... take care of that also...

like



-- Insert Row Count of SoftTable

SET @CountSoft = 'select '+@CountSoft+'COUNT(*) FROM '+@SoftTable

SET @tsql2 = 'UPDATE dbo.ImportTablesRowCount SET SoftTable = ('+@CountSoft+') WHERE TableName = '''+@SoftTable''

EXEC (@TSQL2)

IF @@ERROR<>0 PRINT @tsql2

Cheers!!

Paul Clark-418949 Old Hand Points: 378 More actions · Answer 4

Paul Clark-418949

Old Hand

Points: 378

April 15, 2010 at 7:07 am

#1152073

Hi Lowell,

the other one we use here is Code 18 .....

The problem is 18inches from the keyboard... lol

Paul Clark-418949 Old Hand Points: 378 More actions · Answer 5

Hi C0ldCoffee,

Thanks for the reply.....

Why would I need to do this?

I could get away without using that variable at all I suppose, and just put the COUNT statement straight into the UPDATE statement. I did this originally to try to simplify what I was looking at with the UPDATE Statement as I was getting cross eyed hence why I forgot the +.

Which is annoying as I was explaining how variables and the SET command work to a colleague in the following way:

SET @Variable1 = 'text'+@variable2+'text'+@variable2 etc.....

and obviously I needed the + at the end as I wanted to terminate the string with text not the variable! lol

Regards Paul

WayneS SSC Guru Points: 95405 More actions · Answer 6

Paul Clark-418949 (4/15/2010)

PICNIC (Problem In Chair Not In Computer)...

I've heard it called BKAC (Between Keyboard and Chair), and ID-Ten-T (ID10T). 🙂

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2