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 - 2005-09-19 : 07:42:13
|
| Mack writes "CREATE Proc getReportId(@tableName char(3),@rid int output)As Declare @tmp int Declare @tName varchar(4) Declare @sql varchar(54) Set @tName = @tableName SELECT @sql = 'Select @tmp = IsNull(max(reportid),0)+1 FROM ' + @tName + '_tbl' EXEC(@sql) Set @rid = @tmp Return==========================================when i try to execute this stored procedure, i get the error message"Must declare the variable '@tmp'"I guess i declared this variable inside the stored procedure.Please assist. Thanks very much." |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-19 : 08:35:06
|
| you are going to want to look up sp_executesql as much as I hate saying that.keep in mind that using dynamic sql in stored procedures can cause the storing of multiple execution plans for a stored procedure in the procedure cache. When your procedure is fired, the optimizer makes an attempt to find the best execution plan but if it determines that searching through all of the plans is too much work it will find a "good enough" plan which may run like garbage and may not be the most inefficient.====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
 |
|
|
|
|
|
Stored Procedure Error