Stuck - Dynamic Variables & Syntax error

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 2000 Forums

Transact-SQL (2000)

Stuck - Dynamic Variables & Syntax error

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-06-01 : 15:37:43

Hi, I think this should work but I get this syntax error and I can't figure it out. Maybe someone out there can tell me what I am missing.

So I need to make the tablename Dynamic therefore I will only have to maintain one stored procedure. Here is a small sample of what I would like.

Declare @DQOR_DBNAME varchar(30)
Declare @NICHE_DBNAME varchar(30)

SET @DB1_DBNAME = case @@SERVERNAME WHEN 'DEV' THEN 'DB1_TEST' ELSE 'DB2' END

SELECT ID FROM @DB2_DBNAME.TBL_VEHICLE

This is my error....

Incorrect syntax near '.'.

Any help would be greatly appreciated

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:39:44

Do yourself a favor and don't do this dynamically. You are going to receive a performance penalty for this and also it'll require lax security on the tables. Just use an IF statement:

IF @@SERVERNAME = 'DEV'
SELECT...
ELSE
SELECT...

Tara

jaymedavis
Starting Member

12 Posts

Posted - 2005-06-01 : 16:05:22

How complicated is your query? Your post says "small sample of what I would like". If that query is extremely complicated and long... it might be worth it to invest in the dynamic naming. However, if it is as simple as your post describes, you definitely want to use tduggan's advice.

Jayme

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-06-15 : 15:08:41

Well I tried the IF.. Else.. solution. Yes it does work of course.. but is alot of work for my case.

Is there any other possible solution. How can I investigate in dynamic naming.. Is there any documentation or examples?

My SQL statements are long and we have over 20 stored procedures to maintain. So I am interested in dynamic naming.

Here is a sample of one of the stored procs (as you can see there are two database in each select statement). Unfortunately DB1 is named DB1_test in development and DB1 in Production. It would be alot easier if I can dynamically have the database change according to what server it is running on (dev or prod). Can I do something like this .. IF @@SERVERNAME = 'DEV' BEGIN SET @DBNAME = N'USE Db1_TEST' END ELSE BEGIN SET @DBNAME = N'USE Db1' END??? Your help is very much appreciated!

sELECT @DBNAME
EXEC SP_EXECUTESQL @DBNAME

DECLARE @STEP_NAME VARCHAR(35)
DECLARE @LAST_GOOD_RUN DATETIME

SELECT @LAST_GOOD_RUN = MAX(LAST_GOOD_RFRSH_DT)
FROM db1.DBO.tbl_PROCESS
WHERE LAST_GOOD_RFRSH_DT IS NOT NULL

SELECT @STEP_NAME = 'SP_E5000_PROP - INSERTING'
UPDATE DQOR_test.DBO.DQOR_PROCESS
SET LAST_PROCESS = 'STARTED-' + @STEP_NAME + ' AT ' + CONVERT(CHAR(8),CURRENT_TIMESTAMP,108)
WHERE ACTIVE_RECORD = 1
print @STEP_NAME

INSERT INTO Db1.DBO.tbl2
( column names...)
SELECT
column names...
FROM db2.dbo.TBL_3 as NY
left outer JOIN db2.dbo.TBL_4 as NP
ON NP.ID=NY.ID
where NP.ID NOT IN
(SELECT db1.id
FROM db1.DBO.tbl1)

UPDATE db1.DBO.tbl1
SET RECORD_STATUS= 'D'
FROM db1.DBO.tbl1
WHERE RECORD_STATUS <> 'D' AND
tbl2_ID NOT IN
(SELECT ID
FROM db2.DBO.TBL_3)

UPDATE db1.DBO.tbl1
SET db1.column_name = db2.column_name
FROM db2.DBO.TBL3 AS NP INNER JOIN
db2.DBO.TBL4 AS NY ON NP.ID=NY.ID INNER JOIN
db1.DBO.tbl1 AS U ON NP.ID = U.PROP_ID
WHERE NY.MODTIME > @LAST_GOOD_RUN
GO

quote:
Originally posted by jaymedavis

How complicated is your query? Your post says "small sample of what I would like". If that query is extremely complicated and long... it might be worth it to invest in the dynamic naming. However, if it is as simple as your post describes, you definitely want to use tduggan's advice.

Jayme

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-15 : 15:12:23

No, you won't be able to use the USE statement with dynamic SQL. It is due to dynamic SQL running in a different context than the other code you are going to execute. Besides, what you are doing is considered to be a very, very bad practice. 20 stored procedures is a very small amount of code. Most of us here have 100s if not 1000s in our databases. Why do you need to switch databases in the first place? Why can't you just do SELECT * FROM Table1? Why do you need to use the 3 part naming convention? Only the 2 part is recommended.

Why even bother with stored procedures in the first place if you are going to do this? You should just handle this in your application since you are not getting any benefit of stored procedures with this approach. Just use inline SQL in your app.

Do some reading on the negatives of dynamic SQL before thinking that this is the way to go.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources