Stored Procedure error
-
Hi Guys / Gals
Newbie trying to create a stored procedure and I keep on getting the error:
Server: Msg 137, Level 15, State 2, Procedure Setdbval, Line 5
Must declare the variable '@dbname1'
Here is my code:
use accountsmerge
go
create procedure Setdbval
@dbname1 varchar(100)
with recompile
as
update @dbname1 set currentmonth = 0
where currentmonth is null
update @dbname1 set budgetmonth = 0
where budgetmonth is null
update @dbname1 set Actualytd = 0
where actualytd is null
update @dbname1 set lastyearmonth = 0
where lastyearmonth is null
update @dbname1 set budgetytd = 0
where budgetytd is null
update @dbname1 set lastyearytd = 0
where lastyearytd is null
go
Do you have any Ideas to what I am doing wrong?
Thanks for the help in advance
-
You can't specify a parameter with a dynamic name for the object to update. You would need to use dynamic sql. But what are you trying to accomplish here? There might be better solutions.
-
Yes, SQL's looking for a table-typed variable called @dbname1 to update, since an update statement must target a table, not a varchar variable.
More important is what are you trying to do?
If you're trying to pass in a table name into the stored procedure and have it update whichever table you pass, you'll have to use dynamic SQL. There are several disadvantages and problems with dynamic SQL. There's an article on the problems, I don't have the link handy, but I'm sure someone else will be happy to fill in...
As for what you're trying to do, this may work.
CREATE PROCEDURE Setdbval
@dbname1 VARCHAR(100)
as
DECLARE @sSQL VARCHAR(1000)
SET @sSQL = 'update ' + @dbname1 + ' set currentmonth = 0 where currentmonth is null'
EXECUTE (@sSQL)
....
If you're not trying to pass in a table name into the stored procedure and have it update whichever table you pass, please explain what you want to occur.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I have 10 branch tables that needs to be merge together on cerain fields problem is that in some of the tables (lets say currentyear) the accnr exists with data but in a previous year or in the buget for the year there might not be information or even the acc nr so I end up witn Null values in fields I am trying to use for calculations. The idea I had instaed if creating the above for 10 tables I could use a procedure and only call it once I need it in the main sql.
-
Look up ISNULL() in BOL.
Use it inline, instead of updating the base tables.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply