column & table names question

column & table names question

Post by Beth » Tue, 19 Sep 2000 04:00:00



Hi all-

I have a stored procedure that I'd like to pass column names, as well as table
names, if possible, so that I can use the same procedure over & over again.  The
sp has a cursor and it creates ranks for a specific dataset (I'll paste it
below).  Again, what I'd like to be able to do is have a variable that holds the

possible?  I was reading the BOL, and it doesn't appear that I can do it quite so
straight forward.  Any suggestions?

Here's the sp, just FYI:


AS








declare ranks cursor for

[msafips] not like '0000' order by labor desc

open ranks
fetch next from ranks






begin

                begin



                end
        else
                begin

                end

                begin


                end
        else
                begin

                fetch next from ranks

                end
end

close ranks
deallocate ranks


 
 
 

column & table names question

Post by Brad Irb » Tue, 19 Sep 2000 04:00:00


You can't use variable names for objects, such as tables and fields.  You
CAN construct a statement and EXEC it, though.

create procedure parmtest

as

go

parmtest 'sysobjects'
go

Brad Irby
CRSD Inc.
www.crsdinc.com


Hi all-

I have a stored procedure that I'd like to pass column names, as well as
table
names, if possible, so that I can use the same procedure over & over again.
The
sp has a cursor and it creates ranks for a specific dataset (I'll paste it
below).  Again, what I'd like to be able to do is have a variable that holds
the


this
possible?  I was reading the BOL, and it doesn't appear that I can do it
quite so
straight forward.  Any suggestions?

Here's the sp, just FYI:


AS








declare ranks cursor for


[msafips] not like '0000' order by labor desc

open ranks
fetch next from ranks






begin

begin



end
else
begin

end

begin


end
else
begin

fetch next from ranks

end
end

close ranks
deallocate ranks



 
 
 

column & table names question

Post by Erland Sommarsk » Tue, 19 Sep 2000 04:00:00



>I have a stored procedure that I'd like to pass column names, as well as
>table names, if possible, so that I can use the same procedure over & over
>again.  The sp has a cursor and it creates ranks for a specific dataset
>(I'll paste it below).  Again, what I'd like to be able to do is have a
>variable that holds the table name & column name in the select statement,

>be passed in from ASP) .. is this possible?  I was reading the BOL, and it
>doesn't appear that I can do it quite so straight forward.  Any
>suggestions?

I would definitely look into making this a set-based operation.
Basically it appears that you are doing

   SELECT labor, COUNT(*)
   FROM   moest  


     AND  msafips NOT LIKE '0000'  
   GROUP  BY labor

Except for the fact that you terminate the loop as soon as msafips
is equal to the input parameter. And since msafips appears to be
unrelated to the labor column, this makes the above somewhat tricky.
Then again, if labor and msafips are completely unrelated, your SP
makes no sense to me at all. You are traversing the records according
to labor, but you quit at some random place?

Quote:

>Here's the sp, just FYI:

>CREATE PROCEDURE sp_msaLAUSranks

Don't use sp_ in store procedures that you write, unless you really
are wrting your own system stored procedures.

--

 
 
 

1. Max Table Name & Column Name

What is the maximum allowable length for  table names and column name in
Oracle, Sysbase and Ingress?

I know that in Informix it is 18 bytes
                      DBase IV         10 bytes

Thanx

Eric Lieber
DeskTalk Systems
Torrance CA
(310) 323-5998

2. Average stocking days

3. How to get table & column names

4. Sort Order

5. Naming Tables & Columns

6. Getting a grip on everything

7. 2 tables & duplicate column names

8. Database performance monitoring tools

9. Tables & Columns Names using ADO

10. Naming PK column & FK column

11. Newbie question: Using ODBC to retrieve the name and count of columns in a Table

12. Result Column Names to include table name!

13. syslogins table, name column and domain qualified login names