T-SQL USE statement and local variable

Ginger

Hi folks

We have a database that I need to update. Thing is depending on the site it may be have a different name. Now the possibility is low but still significant enough to have to code for it

So I wrote the following code (code has been sanitized so XXXXX and YYYYY are actual values etc)

DECLARE @str_DBName VARCHAR(255)

SELECT @str_DBName = [name] FROM sysdatabases WHERE [name] LIKE '%YYYYYY%' OR [name] LIKE '%XXXXX%'

PRINT 'The database that is being updated is ' + @str_DBName

USE @str_DBName

BEGIN TRANSACTION

UPDATE [dbo].[XXXXXX]
SET [USER_PWD] = 'XXXXX'
WHERE[ USER_ID] ='XXXXX'

IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
	END
ELSE
	BEGIN
		COMMIT TRANSACTION
	END
GO

Prints the correct name of DB but gets an error on the the USE command.

So I modded it to use an EXEC with a string that is the USE command

That worked but the issue then was that it couldnt find the table even though if the code is ran against the DB it works for the login that I am using.

I am a bit stumped as to why the USE command is causing a problem or not changing the database as expected. Normally when you use the USE command the database dropdown in SQL changes to the database that you selected in the USE command.

The SQL Engine is SQL Server 2000 SP4 and the client is the SQL Server 2005 Management Studio (different machines)

Any ideas?

samelterrance

Hi,

I just tried using the net command from SSMS to a SQL 2000 machine (SP4) and had no problem.

What is the error you are getting ?

Ginger

Well if I use the EXEC(@strSQL)

@strSQL = 'USE ' + @str_DBName

it continues fine except when I do the update and it tells me the table doesnt exist

You were saying you used the net command?

samelterrance

Ginger wrote:

Well if I use the EXEC(@strSQL)

@strSQL = 'USE ' + @str_DBName

it continues fine except when I do the update and it tells me the table doesnt exist

You were saying you used the net command?

sorry, I meant the USE command, yeah, just tried it and got the following;

DECLARE @str_DBName VARCHAR(255)

SELECT @str_DBName = [name] FROM sysdatabases WHERE [name] LIKE 'CABS'

PRINT 'The database that is being updated is ' + @str_DBName

USE @str_DBName


Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@str_DBName'.




I'll see what I can dig up.

Ginger

Change the code to the following

DECLARE @strSQL VARCHAR(300)

SELECT @strSQL = 'USE ' + @str_DBName

EXEC (@strSQL)

That will work

Now try and do a simple select from your CABS db. Should say table doesnt exist

Thinking I may have to dismiss the USE command and access it by the DBNAME.dbo.TABLE

Ginger

Works with the following code

DECLARE @str_DBName VARCHAR(255)
DECLARE @strSQL VARCHAR(1000)

SELECT @str_DBName = [name] FROM sysdatabases WHERE [name] LIKE '%XXXXXXX%' OR [name] LIKE '%XXXXXX%'

PRINT 'The database that is being updated is ' + @str_DBName

SELECT @strSQL =  'UPDATE [' + @str_DBNAME + '].[dbo].[XXXXXX] SET [USER_PWD] = ''XXXXXX'' WHERE [USER_ID] =''XXXX'''

PRINT @strSQL

BEGIN TRANSACTION

EXEC(@strSQL)

IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
	END
ELSE
	BEGIN
		COMMIT TRANSACTION
	END
GO

Still wondering about the USE command

samelterrance

yeah, it's wierd,

this worked for me.

DECLARE @str_DBName VARCHAR(255)
SELECT @str_DBName = [name] FROM sysdatabases WHERE [name] LIKE 'CABS'
PRINT 'Database is: ' + @str_DBName
--USE @str_DBName
Exec('use ' + @str_DBName + '; select * from ct00')

seamus

What happens if you issue a "GO" command after the USE statement?

samelterrance

seamus wrote:

What happens if you issue a "GO" command after the USE statement?

still the same error

Ginger

Yup same issue, originally thought it wasnt changing DB and it still seems thats the case .. so its a bit strange. I have it working, I would be interested as to why it doesnt work

samelterrance

Ginger wrote:

Yup same issue, originally thought it wasnt changing DB and it still seems thats the case .. so its a bit strange. I have it working, I would be interested as to why it doesnt work

if you can interpret "scoped" here, from Microsoft.
The USE command is only scoped to that EXEC.

db

I agree that it is a scoping issue - the change of database is only active within the context of the dynamic sql exec(). Include the update within the exec() and it will work.

I also see a potential issue with the select that assigns the db name - there is potential for more than one row to be returned. This statement would not compile in Oracle.

Ginger

Well the DB name is either one or the other it will never be both

scab-e

Ginger wrote:

Well the DB name is either one or the other it will never be both

In this case you should proably use an IF statement and execute the appropriate UPDATE depending on the database selected. This way you won't have to resort to dynamic SQL with its various drawbacks.

If you have a single UPDATE to carry out then autocommit will take care of the commit and rollback and you don't need an explicit transaction.

Ginger

Autocommit is not an option here as this will be ran outside of query analyser and the reason I want an explicit transaction is that there will be other things running inside it

The sample there was just a proof of concept for something a wee bit more complex

scab-e

avoid dynamic sql if at all possible

Ginger

Ah yes, i am quite aware of that.. The reason for the dynamic SQL in this case is that I dont want to have to rewrite all the code each time I make a change. Invaribly it will mean something is missed whereas in this case, only 1 change is made and all dbs are affected.

In this case there are 2 database names and I dont have to maintain 2 versions of the SQL code. Considering this will be for an update routine that will be ran once, i am not overally worried about performance, just the fact that it will be easy to maintain and implement

Smoggy

i found the same problem trying to do dynamic sql and changing the database (using USE), the only way I solved it was to have all the code passed to the exec statement as one string.