Member Avatar for voidyman

Hi,
Is there a way i can pass variable column names to a stored proc?
Here's what i want to do:
i have lots of tables and for each of those i want to select out a few columns.
e.g
from table titles i want to pick out title_name and cost
then maybe i want to go to publishers and pick out mean cost and country.
I have the table names and the column names in a perl script. Can i pass these as variables to a generic stored proc?
here's what i tried:

create procedure test_proc
@file VARCHAR(256)
@table VARCHAR(50)
as
select @file
from @table

In the above example, i will pass in titles as the @table variable and "title_name, cost" as the @file variable.
but this does not seem to work as it considers @file to be a character string(as it should).
Any work arounds for this?
Thanks :)

  • 2 Contributors
  • 1 Reply
  • 610 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by debasisdas
Member Avatar for debasisdas

You have to frame the SQL at run time and execute the same using as dynamic SQL in a stored procedure.

Read more here and here.

commented: nice thanks:) +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.