Apostrophe's and Variables Headache

Question

Post reply

Apostrophe's and Variables Headache

Paul Clark-418949

SSC-Addicted

Points: 428
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 SSC-Addicted Points: 428 More actions · Answer 1

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: 323518 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: 39972 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 SSC-Addicted Points: 428 More actions · Answer 4

Hi Lowell,

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

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

Paul Clark-418949 SSC-Addicted Points: 428 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: 95460 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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Oh yeah Paul, apologies..i did not look deeply into the variables assigning part.. u need not do that.. sorry mate 🙂

Paul Clark-418949 SSC-Addicted Points: 428 More actions · Answer 8

No worries C0ldCoffee. 🙂

It's always good to have a second pair of eyes to check things over.

Apostrophe's and variables always seem to give me a headache though. Seems to be my Kryptonite as far as Transact is concerned lol. 😉

thanks for the quick response though much appreciated.

Regards

Paul

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

ColdCoffee

SSC-Dedicated

Points: 39972

April 15, 2010 at 10:06 am

#1152256

LOL 😀

Anyways, thanks and welcome mate 🙂