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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-12-02 : 08:26:39
|
| Ryland writes "I'm using the crosstab DSQL (Dynamic SQL) material you published and am incorporating the new SQL2K Table variable type you have discussed on your site. (Both were great articles, BTW.) I'm working on SQL2000 developer at the moment.My immediate issue is that I'm trying to use table variables (@tblname) instead of temp tables (#tblname) because I expect the hits to be high (the SP will return to a ASPX page) and I don't want to cause so much disk I/O. Doing it in memory should also be faster, right?However... when I to use the @tablename syntax (and chased the errors I got) I found that EXEC(string) and SP_EXECUTESQL(string) both fail because they can't see variables outside of (string), with some not relevant exceptions in the case of SP_EXECUTESQL (variables are handled but only as literal substitution; scope does not change).Now I'm trying to pass table variables around between functions or procedures so I can encapsulate functionality and work around the problem from the last paragraph. But Table variables seem only to be able to be used within one procedure; you can't pass them between proc's or functions. For example the following concept does not work:CREATE FUNCTION fxn1 (@source TABLE (col1 INT)) RETURNS VARCHAR(20) Is this right? (Not my syntax, just the concept.)I grew up on Access VB. The functionality I'm trying to reproduce is passing an in-memory recordset between subs to process it differently. In the first case, I want a procedure to tell me the distinct values from my recordset. In the second, I want to use the list of distinct values from my recordset to create my DSQL select statement to create my pseudo-crosstab. Both need to reference the in-memory table.Please help! Thanks,-R" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-02 : 09:29:04
|
| Unfortunately, you have discovered for yourself why the routine (and all the others) were written with temp tables instead of table variables. You can't do it ! There are some limitions on the scope of table variables that make this seemingly impossible. FYI -- you also cannot pass in a table variable as a parameter to a function or stored procedure, so you are out of luck with that as well.believe me, we've all tried !! but it is great that you are out there giving it your best shot as well.- Jeff |
 |
|
|
|
|
|
Table Variables