database/table prefix - 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 2005 Forums

Transact-SQL (2005)

database/table prefix

Author

Topic

lromeijn
Starting Member

3 Posts

Posted - 2009-06-16 : 05:41:08

Hello i got a problem. I am writing stored procedures. But i want them to be generic. So i can use them on both my db's
i have for example the next select query
(on the test)
SELECT * FROM [COMPANY_NAV50TEST].[dbo].[COMPANY_NAV50TEST$Users]
(on the live)
SELECT * FROM [COMPANY_NAV50].[dbo].[COMPANY$Users]

i want to make something like this:
SELECT * FROM @Prefix+$Users]

i know i can put the entire select query in a variable and execute that one. but thats limited to 4000 chars.. and my query's are bigger

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-16 : 05:48:07

You should read this... -> http://www.sommarskog.se/dynamic_sql.html

- Lumbago

lromeijn
Starting Member

3 Posts

Posted - 2009-06-16 : 05:57:21

i know dynamic SQL. But if i make @SQL NVARCHAR(4000) or (MAX) its just 4000 chars of length..

if i would put it right in the exec. so for example: exec('this text represents a statement longer then 4000 chars')
would it work?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 06:01:46

Since you are using SQL Server 2005, you can use SYNONYMs.

E 12°55'05.63"
N 56°04'39.26"

lromeijn
Starting Member

3 Posts

Posted - 2009-06-16 : 07:17:02

got it... i should not put it in a variable but execute it right away

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-16 : 07:17:25

quote:
Originally posted by lromeijn

i know dynamic SQL. But if i make @SQL NVARCHAR(4000) or (MAX) its just 4000 chars of length..

if i would put it right in the exec. so for example: exec('this text represents a statement longer then 4000 chars')
would it work?

ON 2005 NVARCHAR(MAX) is 2GB in length. You aren't going to use that up in a dynamic string any time soon.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 07:21:16

But only 1,048,576 characters... ;-)

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-16 : 07:52:28

Sigh.

Yes only 262 times more characters than you were worried about filling up before.

You can havea variable of NVARCHAR(MAX) and populate it with more than 4000 characters and still dynamically execute it.

However, if you print it you won't see everything. It will all still be there but it does make it harder to debug.

Example:


DECLARE @sql NVARCHAR(MAX)
DECLARE @counter INT

SET @counter = 4000
SET @sql = ''

WHILE @counter > 0 SELECT @sql = @sql + '-- This is a comment' + CHAR(13) + CHAR(10), @counter = @counter - 1

SET @sql = @sql + 'SELECT ''Hello World'' AS [Message]'

-- Try and print @sql
PRINT @sql

-- How long is @sql
SELECT LEN(@sql) AS [Characters], DATALENGTH(@sql) AS [size]

-- execute it
EXEC (@sql)
EXEC sp_ExecuteSql @sql

@sql is 88033 characters long here.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 07:57:28

Did you read up about SYNONYM?

@ Company_Nav50Test
CREATE SYNONYM xyz FOR [dbo].[COMPANY_NAV50TEST$Users]

@ Company_Nav50
CREATE SYNONYM xyz FOR [dbo].[COMPANY$Users]

Then, in your code, you write

SELECT * FROM xyz

no matter if you are on test server or production server. You can easily port your code without changes.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-16 : 08:15:07

References:

BOL (2005)
http://msdn.microsoft.com/en-us/library/ms177544(SQL.90).aspx

First link in google:
http://www.developer.com/db/article.php/3613301

I've never used them in anger.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 08:52:46

Oh, the message wasn't meant for you. I was asking OP.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-16 : 09:15:29

quote:
Originally posted by Peso

Oh, the message wasn't meant for you. I was asking OP.

E 12°55'05.63"
N 56°04'39.26"

YUP got that -- the references weren't meant for you they were meant for OP. Think I'll stop posting now. Getting a little confused here.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources