Dynamic table processing

4 Nov 2005 2:46 AM

jrett

I'm writing a stored procedure and am trying to update a field which is found
in many tables, but I don't want to hard code all the tables into this stored
procedure. I am able to execute a select statement which identifies all the
tables which have this field. I can create a cursor to iterate over each of
these tables, but I cannot use the table name value in an update statement.
I'd of course prefer not to user cursors at all, but I know of no other way,
and this doesn't work anyway. This seems like it should be pretty darn easy,
but I cant for the life of me figure it out.

Here's some sample code to give you an idea of what I'm dealing with.

DECLARE @tblName sysname

declare tablesCursor cursor for
    select name from sysobjects where id in (select id from syscolumns where
name='puid')

open tablesCursor
    fetch next from tablesCursor into @tblName

while (@@FETCH_STATUS=0)
begin
    update @tblName set puid='18000' where puid='16000'
    fetch next from tablesCursor into @tblName
end

close tablesCursor
deallocate tablesCursor

When I attempt to execute that, I get:
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@tblName'.

Please find me the best way to do this?

Thanks.

--
Jaan

4 Nov 2005 7:17 AM

David Portas

Show quote

"jrett" <jr***@discussions.microsoft.com> wrote in message
news:25830EBB-E55C-4E7A-96BE-B88E6F7D56FA@microsoft.com...
> I'm writing a stored procedure and am trying to update a field which is
> found
> in many tables, but I don't want to hard code all the tables into this
> stored
> procedure. I am able to execute a select statement which identifies all
> the
> tables which have this field. I can create a cursor to iterate over each
> of
> these tables, but I cannot use the table name value in an update
> statement.
> I'd of course prefer not to user cursors at all, but I know of no other
> way,
> and this doesn't work anyway. This seems like it should be pretty darn
> easy,
> but I cant for the life of me figure it out.
>
> Here's some sample code to give you an idea of what I'm dealing with.
>
>
> DECLARE @tblName sysname
>
> declare tablesCursor cursor for
> select name from sysobjects where id in (select id from syscolumns where
> name='puid')
>
> open tablesCursor
> fetch next from tablesCursor into @tblName
>
> while (@@FETCH_STATUS=0)
> begin
> update @tblName set puid='18000' where puid='16000'
> fetch next from tablesCursor into @tblName
> end
>
> close tablesCursor
> deallocate tablesCursor
>
>
> When I attempt to execute that, I get:
> Server: Msg 137, Level 15, State 2, Line 11
> Must declare the variable '@tblName'.
>
>
>
> Please find me the best way to do this?
>
> Thanks.
> --
> Jaan

Why would you put such an UPDATE with fixed values into a proc? Isn't this
just a one-time UPDATE? Anyway, here's how I would do it:

SELECT 'UPDATE '
+QUOTENAME(table_schema)+'.'
+QUOTENAME(table_name)
+' SET puid = 18000 WHERE puid = 16000'
FROM information_schema.columns
WHERE column_name = 'puid' ;

Now you have a script you can paste into a proc.

If you really want to make this dynamic, then take a look at the following
proc:
http://vyaskn.tripod.com/sql_server_search_and_replace.htm

You'll probably have to modify the script slightly but it should give you an
idea how to do it. In general though, you should avoid using dynamic SQL
where you can and in this case it seems you can. Read about some of the
issues here: http://www.sommarskog.se/dynamic_sql.html

Now might also be a good time to review whether your database is as well
normalized as it ought to be. Non-key attributes for the most part should
appear in one place in your schema - not necessarily every time, but
typically that should be the rule. You appear to have a lot of columns of
the same name but if they were key columns then wouldn't some of your
UPDATEs fail on key violations?

Hope this helps.

--
David Portas
SQL Server MVP
--

4 Nov 2005 8:43 AM

jrett

Thanks for the info.
The fixed values in my sample code are just for the sample code. They
would actually be params to the sproc.

--
Jaan

Show quote

"David Portas" wrote:

> "jrett" <jr***@discussions.microsoft.com> wrote in message
> news:25830EBB-E55C-4E7A-96BE-B88E6F7D56FA@microsoft.com...
> > I'm writing a stored procedure and am trying to update a field which is
> > found
> > in many tables, but I don't want to hard code all the tables into this
> > stored
> > procedure. I am able to execute a select statement which identifies all
> > the
> > tables which have this field. I can create a cursor to iterate over each
> > of
> > these tables, but I cannot use the table name value in an update
> > statement.
> > I'd of course prefer not to user cursors at all, but I know of no other
> > way,
> > and this doesn't work anyway. This seems like it should be pretty darn
> > easy,
> > but I cant for the life of me figure it out.
> >
> > Here's some sample code to give you an idea of what I'm dealing with.
> >
> >
> > DECLARE @tblName sysname
> >
> > declare tablesCursor cursor for
> > select name from sysobjects where id in (select id from syscolumns where
> > name='puid')
> >
> > open tablesCursor
> > fetch next from tablesCursor into @tblName
> >
> > while (@@FETCH_STATUS=0)
> > begin
> > update @tblName set puid='18000' where puid='16000'
> > fetch next from tablesCursor into @tblName
> > end
> >
> > close tablesCursor
> > deallocate tablesCursor
> >
> >
> > When I attempt to execute that, I get:
> > Server: Msg 137, Level 15, State 2, Line 11
> > Must declare the variable '@tblName'.
> >
> >
> >
> > Please find me the best way to do this?
> >
> > Thanks.
> > --
> > Jaan
>
> Why would you put such an UPDATE with fixed values into a proc? Isn't this
> just a one-time UPDATE? Anyway, here's how I would do it:
>
> SELECT 'UPDATE '
> +QUOTENAME(table_schema)+'.'
> +QUOTENAME(table_name)
> +' SET puid = 18000 WHERE puid = 16000'
> FROM information_schema.columns
> WHERE column_name = 'puid' ;
>
> Now you have a script you can paste into a proc.
>
> If you really want to make this dynamic, then take a look at the following
> proc:
> http://vyaskn.tripod.com/sql_server_search_and_replace.htm
>
> You'll probably have to modify the script slightly but it should give you an
> idea how to do it. In general though, you should avoid using dynamic SQL
> where you can and in this case it seems you can. Read about some of the
> issues here: http://www.sommarskog.se/dynamic_sql.html
>
> Now might also be a good time to review whether your database is as well
> normalized as it ought to be. Non-key attributes for the most part should
> appear in one place in your schema - not necessarily every time, but
> typically that should be the rule. You appear to have a lot of columns of
> the same name but if they were key columns then wouldn't some of your
> UPDATEs fail on key violations?
>
> Hope this helps.
>
> --
> David Portas
> SQL Server MVP
> --
>
>
>

4 Nov 2005 8:46 AM

Erland Sommarskog

=?Utf-8?B?anJldHQ=?= (jr***@discussions.microsoft.com) writes:

> I'm writing a stored procedure and am trying to update a field which is
> found in many tables, but I don't want to hard code all the tables into
> this stored procedure. I am able to execute a select statement which
> identifies all the tables which have this field. I can create a cursor
> to iterate over each of these tables, but I cannot use the table name
> value in an update statement. I'd of course prefer not to user cursors
> at all, but I know of no other way, and this doesn't work anyway. This
> seems like it should be pretty darn easy, but I cant for the life of me
> figure it out.

No, there is no reason to be assume that this should be darn easy,
because it's an unusual thing to do.

To actually use the table name as a variable, you need to use dynamic SQL:

SELECT @sql = 'UPDATE ' + @tblname + ' SET ...'
EXEC (@sql)

But there are several caveats with dynamic SQL. I have an article on
my web site about this: http://www.sommarskog.se/dynamic_sql.html.

For a one-off thing, or at least for a maintenance sort of job, dynamic
SQL is fair game.

But if this is part of application code that is to be run daily, be a good
boy and write explicit UPDATE statements. Particularly if the code is
to be run by non-privileged users. But in this case, there may be all
reason the data model.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

7 Nov 2005 6:05 AM

jsfromynr

Hi jrett ,

> I'm writing a stored procedure and am trying to update a field

which is found

> in many tables

If I understood your statement properly ,may be ON UPDATE CASCADE
is what you are looking for.
With Warm Regards
Jatinder Singh