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 |
|
purnima.m
Starting Member
3 Posts |
 Posted - 2006-03-04 : 01:50:27
|
| Hi ,I am new to SQL Server.1.Can anyone please tell methe scope of local variables used in SQL Scripts?2.If a local variable is being used in a Cursor'swhile loop do we need to Reintialise the local variablesto prevent old values being retained?Thank YouPunima |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-04 : 14:19:15
|
| Local variables are limited in scope to the procedure in which they are declared. They cannot be referenced by either a calling procedure, or within a sub-procedure, or withing a dynamic sql statement.The variable's scope is continuous within a cursor's loop, or any loop within the variable's procedure. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-04 : 15:28:27
|
| Since you're new to sql server you may be interested to hear that about 99 times out of a hundred, new sql developers use some type of loop/cursor solution when they should be using a set based solution. We'll be happy to show you a specific example if you want to post your existing cursor code. The performance difference can be like a thousand times faster as well as better for concurrent operations.Be One with the OptimizerTG |
|
|
|
purnima.m
Starting Member
3 Posts |
Posted - 2006-03-05 : 05:16:13
|
| Well i have a table with around 30 fieldsfrom which i fetch only 21 fields andusing the codes in the field populatemy warehouse dimension withcorresponding description from other tables...I use the cursor to populate local variableswith data from each record and then insert the proper description into Dimension.Eg. Select @District=Dist_namefrom District where Dist_code=@distPurnima |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-05 : 05:25:10
|
| Post your table's DDL and your existing cursor script.----------------------------------'KH' |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-05 : 09:50:51
|
| Generally the idea in this case would be to join your <30colulmn> table to your "corresponding..other" tables in a select statement and use the result as the basis of your insert into your Dimension table. You control the row selection with your JOIN and WHERE criteria. You control the column selection with your SELECT <colList>. If you want help with it, post the info KH suggested. (DDL just means the create table scripts that you can generate from sql server)Be One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-05 : 10:18:32
|
quote:
Originally posted by purnima.m
Well i have a table with around 30 fieldsfrom which i fetch only 21 fields andusing the codes in the field populatemy warehouse dimension withcorresponding description from other tables...I use the cursor to populate local variableswith data from each record and then insert the proper description into Dimension.Eg. Select @District=Dist_namefrom District where Dist_code=@distPurnima
Exactly the situation TG was talking about. Post your code and we can show you a better way to do this. |
|
|
|
|
|
|
Local Variables