UPDATE stored procedure - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE stored procedure

Author

Topic

noquintic
Starting Member

10 Posts

Posted - 2007-05-09 : 17:17:53

In this stored procedure I attempt to update the AGE and SERV_AGE fields with the days difference between a date parameter and a List Date and Service Date in any of a number of tables we have that contain these two fields. I attempt to pass in the date parameter and the table name to use.
=====================================
CREATE PROCEDURE up_UpdateAcctAge
@strTableName nvarchar(50),
@dteWeekDate datetime

AS
UPDATE @strTableName
SET AGE = DATEDIFF(D, ASSIGN_DT, CONVERT(DATETIME, @dteWeekDate, 102)),
SERV_AGE = DATEDIFF(D, SERV_DT, CONVERT(DATETIME,@dteWeekDate, 102))
GO
=========================================

Any idea why I am getting the following message when I check syntax?

Server: Msg 137, Level 15, State 2, Procedure up_UpdateAcctAge, Line 7
Must declare the variable '@strTableName'.

Any help you can give this novice is appreciated.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-09 : 17:29:59

Because you are trying to update a table variable which has not been declared.


UPDATE @strTableName <put the actual table name here>

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-09 : 17:44:15

You'll need to use dynamic SQL for this since you are dynamically passing in the table name.

This should get you started:

DECLARE @sql nvarchar(4000)

SET @sql = 'UPDATE ' + @strTableName + 'SET AGE = ...

EXEC (@sql)

By the way, this is not recommended for security and performance reasons. How many tables could be passed into this stored procedure?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 06:04:58

More on dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

noquintic
Starting Member

10 Posts

Posted - 2007-05-10 : 07:43:59

I have three tables where there are ages to be updated. This stored procedure will be called from a procedure in a VB application. Regarding security and performance, is it better to make a trigger that executes once the table is loaded?

Thanks

quote:
Originally posted by tkizer

You'll need to use dynamic SQL for this since you are dynamically passing in the table name.

This should get you started:

DECLARE @sql nvarchar(4000)

SET @sql = 'UPDATE ' + @strTableName + 'SET AGE = ...

EXEC (@sql)

By the way, this is not recommended for security and performance reasons. How many tables could be passed into this stored procedure?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources