September 21, 2011 at 8:06 am
Hi there,
We have a lot of dynamic TSQL queries. Typical syntax:
DECLARE @strSQL VARCHAR(MAX)
SET @strSQL = ' SELECT * FROM C' + CAST(@somePID AS CHAR) + ' WHERE name=''John'' '
EXEC (@strSQL)
It works, but it's not neat. But now I tried this and it works too!!!
DECLARE @strSQL VARCHAR(MAX)
SET @strSQL = 'CREATE SYNONYM T FOR myDB.dbo.C1'
EXEC(@strSQL)
SELECT * FROM T WHERE name='John'
DROP SYNONYM T
It looks like I can skip programming dynamic TSQL by using SYNONYM !!! Isn't life wonderful?
Question: is this the way to go? Or are there issues I should consider?
Thanks,
Raymond
September 21, 2011 at 8:26 am
I don't see how it avoids anything.
You still have to know which object to call. Which you'll still know exactly at the same time.
You'll still have to use a parameterized query, which would also solve the dynamic sql problem.
September 21, 2011 at 8:26 am
Boooh!!
One thing I see now is that the synonym is database-wide. Meaning, if I am running a query using synonym T, my colleague can't do a thing (except exactly the same thing) until I am finished and have dropped the synonym T. Bummer.
September 21, 2011 at 8:41 am
Raymond van Laake (9/21/2011)
Boooh!!One thing I see now is that the synonym is database-wide. Meaning, if I am running a query using synonym T, my colleague can't do a thing (except exactly the same thing) until I am finished and have dropped the synonym T. Bummer.
So what do you need to do?
September 21, 2011 at 8:47 am
The only thing I can think of is making the synonym name different per user (so if your employeenumber is "23", your synonym will be "T23").... meaning that I am bound to dynamic TSQL again......
Or do you have another idea?
The reason why I need so much dynamic TSQL is that my customers can create their own tables and columns. All queries on those tables need to be dynamic.... or not?
Thx,
Raymond
September 21, 2011 at 8:50 am
Raymond van Laake (9/21/2011)
The only thing I can think of is making the synonym name different per user (so if your employeenumber is "23", your synonym will be "T23").... meaning that I am bound to dynamic TSQL again......Or do you have another idea?
The reason why I need so much dynamic TSQL is that my customers can create their own tables and columns. All queries on those tables need to be dynamic.... or not?
Thx,
Raymond
How's the front end coded? What is is supposed to do.
The only way I can see this being "needed" is if the users create their own reports / queries. At that point I'd just use a parameterized query and be done with it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply