Using Loops in stored procedure?

[sillysod]

Hello.

I have a table called TABLE_CFD

Fields are
CFD_CODE
CFD_TYPE
CFD_NAME

The tables holds strings which are grouped into types, each type has 10 strings
and rows are as follows

CODE TYPE NAME
CF00 CUSTOMER Anything
CF01 CUSTOMER Blah
CF02 CUSTOMER Test
CF03 CUSTOMER Howdy
and so on upto CF09
SF00 SUPPLIER Something
SF01 SUPPLIER Owt
SF02 SUPPLIER Somestring
SF03 SUPPLIER thingy
again upto SF09

i want a stored procedure which will accept the type as an input paramter and will return 10 output parameters which contain the values in name field for the given type

im very new to stored procedures, in fact this is pretty much the first i have attempted beyond returning single rows
could someone nudge me in the right direction

 

[gmmastros]

What you are asking for can probably be done.

As I understand it, when you pass 'CUSTOMER' you want a result set like...

[tt]
Name1 Name2 Name3 Name4 Name5
-------- ----- ----- ----- -----
Anything Blah Test Howdy Etc[/tt]

The problem with this approach is that in about 3 months, your boss/customer/user will say, we want to store and return 20 items.

I suggest you create a stored procedure that returns the data in records instead of fields.

Create Procedure GetRecordsForType
  @Type VarChar(50)
AS
Select CFD_Name
From   Table_CFD
Where  CFD_Type = @Type
Order By CFD_Code

This will return the data you are looking for, but in record form. It would be simple to present the data to the user any way you want in whatever language you are using for presentation. This way will be more flexible for the future.

If, for some reason, this is unacceptable, then let me know and I will help you more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sillysod]

The number of fields will never increase, in my customer table i have 10 extra fields named Custom1 through Custom0
The CFD table holds the Names and come extra stuff like IsMandatory etc.

The reason i wanted to put this into an sp is because the field names will be called from lots of different places in my application sometimes requiring all the field names, sometimes only 1.

I could write a function to process this as you suggest, but that seems like adding an extra layer when i could put it all together in a sp

The other thing that i though about was when i am writing reports, i may add the custom fields as columns in a report, and to get away from needing a sub report to list the custom field values, i could call this stored procedure to set the column headings.

 

[gmmastros]

Well then, here you go.

Select Min(Case When CFD_CODE Like '%0' Then Name End) As Field0,
       Min(Case When CFD_CODE Like '%1' Then Name End) As Field1,
       Min(Case When CFD_CODE Like '%2' Then Name End) As Field2,
       Min(Case When CFD_CODE Like '%3' Then Name End) As Field3,
       Min(Case When CFD_CODE Like '%4' Then Name End) As Field4,
       Min(Case When CFD_CODE Like '%5' Then Name End) As Field5,
       Min(Case When CFD_CODE Like '%6' Then Name End) As Field6,
       Min(Case When CFD_CODE Like '%7' Then Name End) As Field7,
       Min(Case When CFD_CODE Like '%8' Then Name End) As Field8,
       Min(Case When CFD_CODE Like '%9' Then Name End) As Field9
From   Table_CFD
Where  CFD_Type = 'Customer'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

What about:

SELECT Type,
       MAX(CASE WHEN Code = 'SF00'
            THEN Name
            ELSE '' END) AS Name1,
       MAX(CASE WHEN Code = 'SF01'
            THEN Name
            ELSE '' END) AS Name2,
--etc to name10
FROM TABLE_CFD
GROUP BY Type

Borislav Borissov

 

[sillysod]

Select Min(Case When CFD_CODE Like '%0' Then Name End) As Field0,
Min(Case When CFD_CODE Like '%1' Then Name End) As Field1,
Min(Case When CFD_CODE Like '%2' Then Name End) As Field2,
Min(Case When CFD_CODE Like '%3' Then Name End) As Field3,
Min(Case When CFD_CODE Like '%4' Then Name End) As Field4,
Min(Case When CFD_CODE Like '%5' Then Name End) As Field5,
Min(Case When CFD_CODE Like '%6' Then Name End) As Field6,
Min(Case When CFD_CODE Like '%7' Then Name End) As Field7,
Min(Case When CFD_CODE Like '%8' Then Name End) As Field8,
Min(Case When CFD_CODE Like '%9' Then Name End) As Field9
From Table_CFD
Where CFD_Type = 'Customer'


This worked great, thanks alot for the help

 

[gmmastros]

You're welcome.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom