| Author |
Topic |
|
heze
Posting Yak Master
192 Posts |
 Posted - 2006-04-17 : 15:35:12
|
| HiDoes anybody know if if is possible to reference a variable from a string inside an exec statement to one outside the exec?iedeclare @var as intexec ('set @var=2')the output is :Server: Msg 137, Level 15, State 1, Line 1Must declare the variable '@var'.thannk you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 15:59:47
|
| That is not possible. Could you show us an actual example of what you are trying to do or explain what you want? Tara Kizeraka tduggan |
 |
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 16:15:45
|
| create proc somename@t intasexec(select * from ...where myfield=@t)exec(select * from ...where myfield=@t)...exec(select * from ...where myfield=@t)-- |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-17 : 16:15:53
|
| You can do that using sp_executesqldeclare @var as intexec sp_executesql N'set @var=2', N'@var int out', @var out==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 16:19:10
|
| exec('select * from ...where myfield=' + @t)Tara Kizeraka tduggan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 16:20:49
|
| I was only going to offer sp_executesql if there was no workaround for what he was trying to do. My reply shouldn't really have said that it's not possible, but more along the lines of: "it's possible, but before we show you the keys to the kingdom, show us why you want to do this".Tara Kizeraka tduggan |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 16:25:12
|
| sorry I omitted a detail...create proc somename@t intasexec('select * from MyTableRootName'+@t+ ' where myfield='+@t)exec('select * from MyTableRootName'+@t+ ' where myfield='+@t)...exec('select * from MyTableRootName'+@t+ 'where myfield='+@t)--the queries are actually select-into statements, about 100 temporary tables will be produced.So according to what yuoguys are writting, I have to include all the statement into the exec string. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 16:29:34
|
| Or you could fix your database design so that you don't have Table1, Table2, Table3...Tara Kizeraka tduggan |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 16:32:18
|
| okI started with cursors.. First.I want to flaten a foreign key table field and feed it to the primary keysay we have pk table:Term IdStudent2002 234and we have fk TableTerm IdStudent CourseTaken2002 234 aerodynamics2002 234 english2002 234 chemistryI want to add a new field in pk Table that shows:Term IdStudent FlatCoursesTaken2002 234 aerodynamics:english:chemistryI already developed an algorithm using cursors, testing some samples however it was going to take me 15 hours to complete the process. Forget cursors, I tried temp tables, went down to forecasted 10 hours, also forget it, too much. Then I will do very small tables and only do operations in them not on the large ones, for example the joining of pk and fk tables yields one million rows.thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 16:34:13
|
| That is a horrible design if you want that row in the pk table.Tara Kizeraka tduggan |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 16:39:27
|
| I know t, but its for a report they want. They want to know all the courses taken and they want it fast, I proposed this and they loved it. They have everything in the same place and can analyze the patterns of a given group of IDs with bare eye. I will further separate each course taken into a respective row given that only a maximum of 7 courses can be taken. But the procedures are similar using my current apporoach. |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 16:40:20
|
| Any suggestions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 16:44:09
|
| You just query the FK table for the information and display it the way you want it via the application. Do not store this data in SQL Server this way.Tara Kizeraka tduggan |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 16:54:47
|
| t,I will have to do that if this last try yields the same kind of performance. However, please take into consideraton that I am building a pivot table source. The analysts will extract the data from sql server into excel and bui;d a pivot table.The pivot table has demonstrated superior to any application we currently have for the specific analysis they are doing. Since the pivot table goes by fields, I am just creating hew fields with appropriate values ofr display. In a way I am building a very archaic analysis services tool. I went this way and not used analysis services because I was just not succesfull. I was also thinking about implementing an extended stored procedure using c but then I thought... I just want to get over this and move on in life. |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-04-17 : 23:23:47
|
| Hi,I finally was succesful with my last approach. What I bascally did was to create a table for pk and another for fk for each point in time. I had 30 points in time therefore I have 60 individual pk and fk tables. In the same process of table creation, I inner joined both tables. I then use a variable assigment select (this is just a descirpiotn mnot sure of the formal name) ie select @myfield1=@myfield1+coursecodefield from... I then updated the column of the partial pk table.At the end I am left with 30 updated pk tables(in the flatgrade field).I then generate a string that "unions" the 30 partial tables , then exec(@string) then I just materialize the table into my database and delete all the temporary tables that were produced.Again I tried with cursors: best case ~15hrswhile cycles using entire tables~10 hrsthis approach 1hr:59 mins |
|
|
|
|
shared variables between contexts