How do I retrieve the results of a dynamic query within a store procedure

How do I retrieve the results of a dynamic query within a store procedure

Post by Harry K Anderson, II » Sat, 17 Feb 2001 23:53:56



Many pieces of code are supplied to help you help me......

Heres the Stored Procedure that gets called:
Alter Procedure CheckThis

As

return

CODE IN USE:



    declare tab_cursor  cursor
         for SELECT substring(b.name,1,30) as tableName
         FROM syscolumns a, sysobjects b
         WHERE a.name = 'USER_ID' and b.name not in ('Address', 'staff',
'user_auth')
         and a.id=b.id order by tablename

    open tab_cursor


    begin

where user_id = 193"



    end
    close tab_cursor
    deallocate tab_cursor

Purpose:
    the field USER_ID appears in about 20 tables, I need to ability to
determine if the USER_ID is used in any of the tables except those I
excluded.  Being basically lazy, plus I want a flexible solution, should
someone add another table that uses USER_ID I have done the following:

1) Query the syscolumns table for USER_ID and join with sysobjects to
determine TableName

3)  execute the dynamically generated query and return the value (in this
case a count for that User_id in that table)
4) Loop through all tables that use User_id

Problem:
    How can I get the count from the query and evaluate it, All I currently
get are 0's, and there are many tables where it is much greater than 0.

Thanks in advance for help.

 
 
 

How do I retrieve the results of a dynamic query within a store procedure

Post by DaveSat » Sun, 18 Feb 2001 00:23:26


you should be able to do this using sp_executesql  rather than EXEC();
otherwise, take a look at this tip I wrote (for 6.5):
http://www.devx.com/free/tips/tipview.asp?content_id=2821
--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------



> Many pieces of code are supplied to help you help me......

> Heres the Stored Procedure that gets called:
> Alter Procedure CheckThis

> As

> return

> CODE IN USE:



>     declare tab_cursor  cursor
>          for SELECT substring(b.name,1,30) as tableName
>          FROM syscolumns a, sysobjects b
>          WHERE a.name = 'USER_ID' and b.name not in ('Address', 'staff',
> 'user_auth')
>          and a.id=b.id order by tablename

>     open tab_cursor


>     begin

> where user_id = 193"



>     end
>     close tab_cursor
>     deallocate tab_cursor

> Purpose:
>     the field USER_ID appears in about 20 tables, I need to ability to
> determine if the USER_ID is used in any of the tables except those I
> excluded.  Being basically lazy, plus I want a flexible solution, should
> someone add another table that uses USER_ID I have done the following:

> 1) Query the syscolumns table for USER_ID and join with sysobjects to
> determine TableName
> 2) now I want to check each of these tables for a particular user, ie the

> 3)  execute the dynamically generated query and return the value (in this
> case a count for that User_id in that table)
> 4) Loop through all tables that use User_id

> Problem:
>     How can I get the count from the query and evaluate it, All I
currently
> get are 0's, and there are many tables where it is much greater than 0.

> Thanks in advance for help.