| Author |
Topic |
|
thanksfor help
Posting Yak Master
106 Posts |
 Posted - 2005-04-18 : 13:43:13
|
| Hi,I need help in getting the return value from a select statement in a stored procedure.set @sql_cmd = 'select @max_sal= max(sal) from ' + @tbl_employeeexec (@sql_cmd)I have many employee table with different name in my project. Given the employee table it should give the max_sal. With that rest of the calculation is done in stored procedure.What I am missing in above statement in getting the @max_sal. The scope of the variable is not available outside the exec statement.Thanks for any help. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-04-18 : 14:19:35
|
use sp_executesql. It can handle output variables:declare @sql_cmd nvarchar(200) ,@max_sal int ,@tbl_employee varchar(50)select @tbl_employee = 'sysObjects' ,@max_sal = null ,@sql_cmd = 'select @max_sal= max(id) from ' + @tbl_employeeexec sp_executesql @sql_cmd, N'@max_sal int output', @max_sal = @max_sal outputselect @max_sal Be One with the OptimizerTG |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-04-18 : 20:43:27
|
| I still want to know why you have multiple employee tables...HTH=================================================================Every act of conscious learning requires the willingness to suffer an injury to one's self-esteem. That is why young children, before they are aware of their own self-importance, learn so easily; and why older persons, especially if vain or important, cannot learn at all. -Thomas Szasz, author, professor of psychiatry (1920- ) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-18 : 20:52:03
|
| >>I still want to know why you have multiple employee tables...Count me as curious as well ... why so many people make things hard on themselves?- Jeff |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-04-18 : 21:08:17
|
| >>>>I still want to know why you have multiple employee tables...>>Count me as curious as well I was afraid to ask, the answer can't be pretty.Be One with the OptimizerTG |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-18 : 21:23:37
|
 I didn't even help on this one, and I'm curious too...This may be the mark of denormalizedman , creator of chaos and denyor of service...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-18 : 21:34:27
|
| multiple employee tables = different companies/projects in one database?--------------------keeping it simple... |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-18 : 21:45:22
|
quote:
I was afraid to ask, the answer can't be pretty.
More likely than not, many employee tables/projects within one company. Pretty common mistake; each group creates an employee table for their project, even though they could just select SOME of the employees from a master table...Ahh, who needs efficiency, anyway?Just one of the employees(not really),AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
thanksfor help
Posting Yak Master
106 Posts |
Posted - 2005-04-20 : 14:10:35
|
| Tbl_employee is just a example given. The original information table is separated by each year, like tbl_employee_1990, tbl_employee_1991......... tbl_employee_2004. This done because each table have more than 20 million records and querying takes very long time for the various current updates that are done to the system.So we do by each year and make sure all the history information is updated. |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-04-20 : 20:25:30
|
20 Million records?! So you work for Microsoft,then... AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-20 : 22:41:56
|
| 20 million employees, wow that's a huge companyyou'd probably need archiving on this table so what's retained is the current information only--------------------keeping it simple... |
|
|
|
|
return value from select statement