error at variable name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

error at variable name

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 06:24:45

im trying to test a sql script. i have broken it out and im just trying to get this part working. im getting errors at my update part at the ' + @FieldName + ' . What do i have to put there to get passed the error

-- Preform replacement (note we are replacing in the whole field not just the end)
	SELECT @FieldName = 'SecurityName' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringReplacement = '' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringToFind = '' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)

	
	UPDATE dbo.SSCLXWorkingDataloadFile3
	SET ' + @FieldName +  '  = ' + ' " " + ' + @FieldName +  ' + FROM dbo.SSCLXWorkingDataloadFile3
	WHERE ' + @FieldName + ' IS NOT NULL

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 06:56:59

have it like this now



-- Declare varibles 
DECLARE
@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)

Select PK_ID
INTO   #StringReplace 
FROM   dbo.SSCLXWorkingNamingTable

--Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1
-- Loop thru the table 
WHILE @pk <= @maxPK
BEGIN
-- Preform replacement (note we are replacing in the whole field not just the end)
	SELECT @FieldName = 'SecurityName' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringReplacement = 'Equinix ' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringToFind = 'Equinix Inc' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


	UPDATE dbo.SSCLXWorkingDataloadFile3
	SET @FieldName  = @FieldName  FROM dbo.SSCLXWorkingDataloadFile3
	WHERE @FieldName IS NOT NULL

	UPDATE dbo.SSCLXWorkingDataloadFile3
	SET @FieldName = REPLACE('  @FieldName  ',' + @Quote +  @StringToFind  +  @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
	FROM dbo.SSCLXWorkingDataloadFile3
	WHERE @FieldName IS NOT NULL
	
	
	
	 
END

SET @pk = 1

-- Loop thru the table 
WHILE @pk <= @maxPK
BEGIN

	UPDATE dbo.SSCLXWorkingDataloadFile3
	SET @FieldName = LTRIM(RTRIM(@FieldName)) FROM dbo.SSCLXWorkingDataloadFile3
	WHERE @FieldName IS NOT NULL
	
	
	 
	 Select @pk = @pk + 1
END

but get the following error
there is already an object named '#StringReplace' in the database.

any ideas why im getting this

jleitao
Posting Yak Master

100 Posts

Posted - 2015-05-07 : 07:07:06

your problem is here:

Select PK_ID
INTO #StringReplace
FROM dbo.SSCLXWorkingNamingTable

you are creating a temp table ( #StringReplace ) and the table already exists

you need drop your temp table first. You can add a IF command to check if the table exists or not and drop it before execute the Select INTO ....

however the temporary table just exist while "your connection" is open. If you close your connection and open another and then execute the script again you will not receive that error.

------------------------
PS - Sorry my bad english

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 07:15:35

thanks just spotted that after i posted it.

does the code look right? after that from the select to the update?

seems to run but takes a while make its because the table is so big

jleitao
Posting Yak Master

100 Posts

Posted - 2015-05-07 : 07:37:11

Can you explain what are you trying to do?

Post a few data samples to help us to understand.

------------------------
PS - Sorry my bad english

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 07:39:48

it looms like it working ok up to this point

UPDATE dbo.SSCLXWorkingDataloadFile3
SET @FieldName = REPLACE(' @FieldName ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
FROM dbo.SSCLXWorkingDataloadFile3
WHERE @FieldName IS NOT NULL

jleitao
Posting Yak Master

100 Posts

Posted - 2015-05-07 : 07:55:36

if you don't tell us what are you trying to do i can't help you.

but, you forgot increment your @pk variable in the first WHILE. However, that will not solve your problem.

------------------------
PS - Sorry my bad english

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 07:59:52

im trying to find a certain string in a table and replace it with another string from another table.

ya i saw that and have it in thanks.

is on this line i think thats causing an error
SET @FieldName = REPLACE(' @FieldName ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')

as soon as it steps into that the @FieldName changes from secuirtyname which is correct back to @FieldName

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 09:15:51

anyone any ideas why this is happening

jleitao
Posting Yak Master

100 Posts

Posted - 2015-05-07 : 09:38:49

what column are you trying update?

You need specify the column. You are updating a variable not a column.

UPDATE T set
<COLUMN> = value
FROM Table T

You can solve your script problem, to don't return any error, however the script will do nothing.

if you explain your problem (with samples) its more easier for us, help you with your problem and performance problem.

I don't understand why you need a loop in your query.

I don't understand why you are doing this:

SELECT @FieldName = 'SecurityName' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringReplacement = 'Equinix ' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringToFind = 'Equinix Inc' from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)

I dont't understand why are you doing this:

UPDATE dbo.SSCLXWorkingDataloadFile3
SET @FieldName = @FieldName FROM dbo.SSCLXWorkingDataloadFile3
WHERE @FieldName IS NOT NULL

etc...

So if you need help, you need help us to understand your problem. take a look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=201020

------------------------
PS - Sorry my bad english

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 09:39:06

I think you have your quotes in the wrong place. What this statement says is this:

1. Look in the string ' @FieldName ' (not the variable @FieldName, because it is quoted)
2. If you see any occurrences of the string ' + @Quote + @StringToFind + @Quote + ' (again no variable expansion, since this is quoted)
3. Replace occurrences found in step 2 with the string ' + @Quote + @StringReplacement + @Quote + ' (no variable expansion here either)

Since the string ' + @Quote + @StringToFind + @Quote + ' is not found in the string ' @FieldName ', the variable @FieldName is set to the string ' @FieldName '

Gerald Britton, MCSA
Toronto PASS Chapter

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 09:50:47

because there is not direct link from the naming table to the dataload table.

naming table
pk_id Stringtofind stringreplacement field
1 TESTED TEST SecurityName
2 tesing te SecurityName

so i want to update the column SecurityName i the dataload table

PK-ID inestmenttype securityname colum 1 column 2 etc
200 rrr tested a b
900 rr testing c d

so i get it to loop through the naming table to find the macthed and make the changes

so in end it lloks like this
PK-ID inestmenttype securityname colum 1 column 2 etc
200 rrr test a b
900 rr te c d

thats what im tryin to do if possible

this is someone else code im trying to fix so not sure why they did it all the way they did

i also made a mistake in the select code part this is how it looks now

-- Preform replacement (note we are replacing in the whole field not just the end)
	SELECT @FieldName = Field from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringReplacement =  StringReplacement  from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringToFind = StringToFind  from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 09:57:52

[code]
SELECT @FieldName = Field from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringReplacement = StringReplacement from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringToFind = StringToFind from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
[/code]

is not valid SQL code. If you correct the syntax, we can probably help you the rest of the way.

Gerald Britton, MCSA
Toronto PASS Chapter

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 10:00:17

really as that part works in the code

it fills in the
@FieldName = secuirtname which is the column i want it to look at
@StringReplacement = test
@StringToFind = Tested

so thats what i want to happen then when it goes to the update i want it to rrepace the words but thats when it doesnt seem to work

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 10:02:08

like when you put all the code togtehr is it still wrong?


Select PK_ID
INTO   #StringReplace 
FROM   dbo.SSCLXWorkingNamingTable

Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1
-- Loop thru the table 
WHILE @pk <= @maxPK
BEGIN
-- Preform replacement (note we are replacing in the whole field not just the end)
	SELECT @FieldName = Field from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringReplacement =  StringReplacement  from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringToFind = StringToFind  from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


--	UPDATE dbo.SSCLXWorkingDataloadFile3
	--SET @FieldName  = @FieldName  FROM dbo.SSCLXWorkingDataloadFile3
	--WHERE @FieldName IS NOT NULL

	UPDATE dbo.SSCLXWorkingDataloadFile3
	SET @FieldName = REPLACE(' @FieldName ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
	FROM dbo.SSCLXWorkingDataloadFile3
	WHERE @FieldName IS NOT NULL
	
	Select @pk = @pk + 1
	
	 



END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 10:25:44

quote:
Originally posted by rjhe22

really as that part works in the code

it fills in the
@FieldName = secuirtname which is the column i want it to look at
@StringReplacement = test
@StringToFind = Tested

so thats what i want to happen then when it goes to the update i want it to rrepace the words but thats when it doesnt seem to work

Sorry, my bad I read "SELECT" as "SET"

Gerald Britton, MCSA
Toronto PASS Chapter

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 10:29:00

OK -- I explained why


SET @FieldName = REPLACE(' @FieldName ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')

won't do what you want.

do you mean:


SET @FieldName = REPLACE(@FieldName,@Quote + @StringToFind + @Quote,@Quote + @StringReplacement + @Quote)

Gerald Britton, MCSA
Toronto PASS Chapter

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 10:37:30

using this code and stepping through it it seems to run over all the column in the naming table and tries to updates the other table with out updating it so how can i get it to commit the changes to the data base if the code is right


-- Declare varibles 
DECLARE
@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)

SELECT @SQLStmt = '
Select PK_ID
INTO   #StringReplace 
FROM   dbo.SSCLXWorkingNamingTable


Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1
-- Loop thru the table 
WHILE @pk <= @maxPK
BEGIN
-- Preform replacement (note we are replacing in the whole field not just the end)
      SELECT @FieldName = Field from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
      SELECT @StringReplacement =  StringReplacement  from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
      SELECT @StringToFind = StringToFind  from dbo.SSCLXWorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


       UPDATE dbo.SSCLXWorkingDataloadFile3
      SET @FieldName  = @FieldName  FROM dbo.SSCLXWorkingDataloadFile3
      WHERE @FieldName IS NOT NULL

      UPDATE dbo.SSCLXWorkingDataloadFile3
      SET @FieldName = REPLACE(@FieldName ,' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
      FROM dbo.SSCLXWorkingDataloadFile3
      WHERE @FieldName IS NOT NULL
       
      Select @pk = @pk + 1
      
      END'
      EXECUTE(@SQLStmt)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 10:52:53

[code]
SET @FieldName = REPLACE(@FieldName ,' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
[/code]
is still wrong

Also
[code]
UPDATE dbo.SSCLXWorkingDataloadFile3
SET @FieldName = @FieldName FROM dbo.SSCLXWorkingDataloadFile3
WHERE @FieldName IS NOT NULL
[/code]

will only change the variable @FieldName. It will not update anything in the table dbo.SSCLXWorkingDataloadFile3

Gerald Britton, MCSA
Toronto PASS Chapter

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-05-07 : 10:55:43

ya thats ok but shouldnt it update it here
UPDATE dbo.SSCLXWorkingDataloadFile3
SET @FieldName = REPLACE(@FieldName,@Quote + @StringToFind + @Quote,@Quote + @StringReplacement + @Quote)
FROM dbo.SSCLXWorkingDataloadFile3
WHERE @FieldName IS NOT NULL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 11:02:33

NO! You're still only updating the variable


UPDATE dbo.SSCLXWorkingDataloadFile3
SET @FieldName = REPLACE(@FieldName,@Quote + @StringToFind + @Quote,@Quote + @StringReplacement + @Quote)
FROM dbo.SSCLXWorkingDataloadFile3
WHERE @FieldName IS NOT NULL

Does not reference any column in the table dbo.SSCLXWorkingDataloadFile3. @FieldName is a variable, not a column name. If you think SQL will resolve @FieldName to a column name at execution time, sadly, that is not the case.

Gerald Britton, MCSA
Toronto PASS Chapter

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources