[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Can a table column be used as a parameter?

SSciarrino

3/23/2007 2:29:00 PM

Hope I can explain this.

I have a simple query to check the period totals for each month for projects
that are closed.

Here is the query:

SELECT a.project_id,a.component_id,a.cost_element_id,project_amt_1

FROM DBSproj.dbo.component_monetary_balance AS a, DBSproj.dbo.project AS b
WHERE a.project_id = b.project_id
AND a.amt_class_type = 'ACTUAL'
AND a.curr_type = 'L'
AND a.fiscal_year = '2007'
AND b.project_status = 'C'
AND project_amt_1 <> 0.00'

In the component_monetary_balance table there are 12 columns project_amt_1
to project_amt_12.

I want to have that column be a parameter (as I am going to place this in a
stored procedure)

I looked at sp_executesql but unsure if this will work.

Is it possible to have a column and not a data value become the parameter
you can DECLARE?
5 Answers

Alejandro Mesa

3/23/2007 3:00:00 PM

0

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynami...


AMB


"ssciarrino" wrote:

> Hope I can explain this.
>
> I have a simple query to check the period totals for each month for projects
> that are closed.
>
> Here is the query:
>
> SELECT a.project_id,a.component_id,a.cost_element_id,project_amt_1
>
> FROM DBSproj.dbo.component_monetary_balance AS a, DBSproj.dbo.project AS b
> WHERE a.project_id = b.project_id
> AND a.amt_class_type = 'ACTUAL'
> AND a.curr_type = 'L'
> AND a.fiscal_year = '2007'
> AND b.project_status = 'C'
> AND project_amt_1 <> 0.00'
>
> In the component_monetary_balance table there are 12 columns project_amt_1
> to project_amt_12.
>
> I want to have that column be a parameter (as I am going to place this in a
> stored procedure)
>
> I looked at sp_executesql but unsure if this will work.
>
> Is it possible to have a column and not a data value become the parameter
> you can DECLARE?
>
>
>
>
>
>
>

Raymond D'Anjou

3/23/2007 4:21:00 PM

0

You didn't want to say it Alejandro so I will...

A normalized design of the tables would have made your query simple.

"Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
news:9745BCA3-FBE1-4F6D-A439-A4D89736B7C6@microsoft.com...
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynami...
>
>
> AMB
>
>
> "ssciarrino" wrote:
>
>> Hope I can explain this.
>>
>> I have a simple query to check the period totals for each month for
>> projects
>> that are closed.
>>
>> Here is the query:
>>
>> SELECT a.project_id,a.component_id,a.cost_element_id,project_amt_1
>>
>> FROM DBSproj.dbo.component_monetary_balance AS a, DBSproj.dbo.project AS
>> b
>> WHERE a.project_id = b.project_id
>> AND a.amt_class_type = 'ACTUAL'
>> AND a.curr_type = 'L'
>> AND a.fiscal_year = '2007'
>> AND b.project_status = 'C'
>> AND project_amt_1 <> 0.00'
>>
>> In the component_monetary_balance table there are 12 columns
>> project_amt_1
>> to project_amt_12.
>>
>> I want to have that column be a parameter (as I am going to place this in
>> a
>> stored procedure)
>>
>> I looked at sp_executesql but unsure if this will work.
>>
>> Is it possible to have a column and not a data value become the parameter
>> you can DECLARE?
>>
>>
>>
>>
>>
>>
>>


SSciarrino

3/23/2007 5:27:00 PM

0

And I failed to indicate that I wouldn't design the table this way. This is
an ERP table so I am stuck with the way it is designed....

I will read the information presented in the link...if anyone else can add
some insight as to how I could accomplish this it would be helpful.

"Raymond D'Anjou" wrote:

> You didn't want to say it Alejandro so I will...
>
> A normalized design of the tables would have made your query simple.
>
> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
> news:9745BCA3-FBE1-4F6D-A439-A4D89736B7C6@microsoft.com...
> > The Curse and Blessings of Dynamic SQL
> > http://www.sommarskog.se/dynami...
> >
> >
> > AMB
> >
> >
> > "ssciarrino" wrote:
> >
> >> Hope I can explain this.
> >>
> >> I have a simple query to check the period totals for each month for
> >> projects
> >> that are closed.
> >>
> >> Here is the query:
> >>
> >> SELECT a.project_id,a.component_id,a.cost_element_id,project_amt_1
> >>
> >> FROM DBSproj.dbo.component_monetary_balance AS a, DBSproj.dbo.project AS
> >> b
> >> WHERE a.project_id = b.project_id
> >> AND a.amt_class_type = 'ACTUAL'
> >> AND a.curr_type = 'L'
> >> AND a.fiscal_year = '2007'
> >> AND b.project_status = 'C'
> >> AND project_amt_1 <> 0.00'
> >>
> >> In the component_monetary_balance table there are 12 columns
> >> project_amt_1
> >> to project_amt_12.
> >>
> >> I want to have that column be a parameter (as I am going to place this in
> >> a
> >> stored procedure)
> >>
> >> I looked at sp_executesql but unsure if this will work.
> >>
> >> Is it possible to have a column and not a data value become the parameter
> >> you can DECLARE?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>
>
>

Raymond D'Anjou

3/23/2007 6:53:00 PM

0

Here's one method you can adapt (probably not the best):
DECLARE @col INT
SET @col = 2
SELECT CASE @col
WHEN 1 THEN project_amt_1
WHEN 2 THEN project_amt_2
WHEN 3 THEN project_amt_3
... (repeat 12 times for 12 columns)
END
FROM component_monetary_balance
....

You could also use a series of IFs and repeat the query for each column.

"ssciarrino" <ssciarrino@discussions.microsoft.com> wrote in message
news:56A06EB3-9991-4241-8A68-C7027287AED9@microsoft.com...
> And I failed to indicate that I wouldn't design the table this way. This
> is
> an ERP table so I am stuck with the way it is designed....
>
> I will read the information presented in the link...if anyone else can add
> some insight as to how I could accomplish this it would be helpful.
>
> "Raymond D'Anjou" wrote:
>
>> You didn't want to say it Alejandro so I will...
>>
>> A normalized design of the tables would have made your query simple.
>>
>> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in
>> message
>> news:9745BCA3-FBE1-4F6D-A439-A4D89736B7C6@microsoft.com...
>> > The Curse and Blessings of Dynamic SQL
>> > http://www.sommarskog.se/dynami...
>> >
>> >
>> > AMB
>> >
>> >
>> > "ssciarrino" wrote:
>> >
>> >> Hope I can explain this.
>> >>
>> >> I have a simple query to check the period totals for each month for
>> >> projects
>> >> that are closed.
>> >>
>> >> Here is the query:
>> >>
>> >> SELECT a.project_id,a.component_id,a.cost_element_id,project_amt_1
>> >>
>> >> FROM DBSproj.dbo.component_monetary_balance AS a, DBSproj.dbo.project
>> >> AS
>> >> b
>> >> WHERE a.project_id = b.project_id
>> >> AND a.amt_class_type = 'ACTUAL'
>> >> AND a.curr_type = 'L'
>> >> AND a.fiscal_year = '2007'
>> >> AND b.project_status = 'C'
>> >> AND project_amt_1 <> 0.00'
>> >>
>> >> In the component_monetary_balance table there are 12 columns
>> >> project_amt_1
>> >> to project_amt_12.
>> >>
>> >> I want to have that column be a parameter (as I am going to place this
>> >> in
>> >> a
>> >> stored procedure)
>> >>
>> >> I looked at sp_executesql but unsure if this will work.
>> >>
>> >> Is it possible to have a column and not a data value become the
>> >> parameter
>> >> you can DECLARE?
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>>
>>
>>


SSciarrino

3/23/2007 7:43:00 PM

0

thanks this points me in a direction

"Raymond D'Anjou" wrote:

> Here's one method you can adapt (probably not the best):
> DECLARE @col INT
> SET @col = 2
> SELECT CASE @col
> WHEN 1 THEN project_amt_1
> WHEN 2 THEN project_amt_2
> WHEN 3 THEN project_amt_3
> ... (repeat 12 times for 12 columns)
> END
> FROM component_monetary_balance
> ....
>
> You could also use a series of IFs and repeat the query for each column.
>
> "ssciarrino" <ssciarrino@discussions.microsoft.com> wrote in message
> news:56A06EB3-9991-4241-8A68-C7027287AED9@microsoft.com...
> > And I failed to indicate that I wouldn't design the table this way. This
> > is
> > an ERP table so I am stuck with the way it is designed....
> >
> > I will read the information presented in the link...if anyone else can add
> > some insight as to how I could accomplish this it would be helpful.
> >
> > "Raymond D'Anjou" wrote:
> >
> >> You didn't want to say it Alejandro so I will...
> >>
> >> A normalized design of the tables would have made your query simple.
> >>
> >> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in
> >> message
> >> news:9745BCA3-FBE1-4F6D-A439-A4D89736B7C6@microsoft.com...
> >> > The Curse and Blessings of Dynamic SQL
> >> > http://www.sommarskog.se/dynami...
> >> >
> >> >
> >> > AMB
> >> >
> >> >
> >> > "ssciarrino" wrote:
> >> >
> >> >> Hope I can explain this.
> >> >>
> >> >> I have a simple query to check the period totals for each month for
> >> >> projects
> >> >> that are closed.
> >> >>
> >> >> Here is the query:
> >> >>
> >> >> SELECT a.project_id,a.component_id,a.cost_element_id,project_amt_1
> >> >>
> >> >> FROM DBSproj.dbo.component_monetary_balance AS a, DBSproj.dbo.project
> >> >> AS
> >> >> b
> >> >> WHERE a.project_id = b.project_id
> >> >> AND a.amt_class_type = 'ACTUAL'
> >> >> AND a.curr_type = 'L'
> >> >> AND a.fiscal_year = '2007'
> >> >> AND b.project_status = 'C'
> >> >> AND project_amt_1 <> 0.00'
> >> >>
> >> >> In the component_monetary_balance table there are 12 columns
> >> >> project_amt_1
> >> >> to project_amt_12.
> >> >>
> >> >> I want to have that column be a parameter (as I am going to place this
> >> >> in
> >> >> a
> >> >> stored procedure)
> >> >>
> >> >> I looked at sp_executesql but unsure if this will work.
> >> >>
> >> >> Is it possible to have a column and not a data value become the
> >> >> parameter
> >> >> you can DECLARE?
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>