| Author |
Topic |
|
WalkerDA
Yak Posting Veteran
61 Posts |
 Posted - 2004-06-07 : 15:11:44
|
| How do I store more than 255 characters with a single row of data. I read up on it slightly but couldn't get any worthwhile results when that yielded the results needed.Derrick Walker |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-07 : 15:13:32
|
| The data type just needs to support it.DECLARE @Var1 VARCHAR(7000)Tara |
 |
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-07 : 15:18:39
|
| I did that but when I insert long messages into the column and then select from the table only about 255 chars. are displayed.Derrick Walker |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-07 : 15:20:41
|
| Ah, you mean in like Query Analyzer. Go to Tools/Options/Results. Change the maximum characters per column field.Tara |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-07 : 15:27:33
|
| That did it! Thanks... Do you have any idea how to get the @tablename dynamically?SO, SELECT * FROM @TABLENAMEDerrick Walker |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-07 : 15:29:55
|
| You'll need to use dynamic sql. DECLARE @TableName SYSNAMEDECLARE @SQL VARCHAR(7000)SET @TableName = 'Table1'SET @SQL = 'SELECT Column1, Column2 FROM ' + @TableNameEXEC (@SQL)BTW, you need to be aware of the security and performance issues with this. You'll need to grant explicit permissions on the objects instead of just EXEC on the stored procedure. Negative performance impact will also be a factor.Tara |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-07 : 15:32:47
|
| How poorly will performance be? The tables are almost exact and all indexed pretty well. This is a guess but I'm thinking less than 1000 rows and about 6 columns per table.Derrick Walker |
|
|
|
vito1281
Starting Member
12 Posts |
Posted - 2004-06-07 : 15:33:09
|
You can use dynamic sql, like this:EXEC ('SELECT * FROM ' + @TABLENAME)Also, take a look at sp_executesql in BOL if you need more flexibility for the dynamic sql.quote:
Originally posted by WalkerDA
That did it! Thanks... Do you have any idea how to get the @tablename dynamically?SO, SELECT * FROM @TABLENAMEDerrick Walker
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-07 : 15:36:27
|
quote:
Originally posted by WalkerDA
How poorly will performance be?
You'll have to test that out to see. We were able to get a few seconds chopped off each of our dynamic queries. There are ways around dynamic sql, although not one for your specific question. You'd be better off explicitly defining the table name. How many different tables would you have to do this for? Why can't you hard code the table name in each query?Tara |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-07 : 15:40:34
|
| There's no real reason other than having four different procedures designed in the same fashion vice passing in a common identifier and grabing the tablename based on that identifier.Derrick Walker |
|
|
|
vito1281
Starting Member
12 Posts |
Posted - 2004-06-07 : 15:43:30
|
Tara,I just wanted to ask you a quick question regarding this matter.From what I understand, if one were to use the sp_executesql function with the table name as the parameter, the query optimizer would realize that the only thing changing is the table name, and would thus optimize the query (reuse execution plan). Is this correct? There's obviously overhead with dynamic sql as opposed to hard-coded names, but wouldn't sp_executesql get rid of most overhead?Thanksquote:
Originally posted by tduggan
quote:
Originally posted by WalkerDA
How poorly will performance be?
You'll have to test that out to see. We were able to get a few seconds chopped off each of our dynamic queries. There are ways around dynamic sql, although not one for your specific question. You'd be better off explicitly defining the table name. How many different tables would you have to do this for? Why can't you hard code the table name in each query?Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-07 : 15:46:47
|
| Yes that is true. But one should still avoid dynamic sql wherever possible. Of course, there are some instances where dynamic sql is unavoidable.Tara |
|
|
|
|
Long Messages in A Row