Column name in functions

[Patrik]

Hi,

Can we use a parameter that is a column name in a function ?

Here's my function :

CREATE FUNCTION dbo.fn_counting (@colnumber varchar(2),@num ber
varchar(1))
RETURNS int AS

BEGIN
DECLARE @column varchar(2)
DECLARE @ColTotal int

SET @column = 'R' +@colnumber
(This next line WORKS !!!)
SELECT @ColTotal = COUNT(*) FROM dbo.Tbl_Answers WHERE R3 = @number
(This next one DOESN'T WORK - because of the ' it is treated as a
string)
SELECT @ColTotal = 'COUNT(*) FROM dbo.Tbl_Answers WHERE ' +@column +
'=' +@number

RETURN @ColTotal
END

Thank you

[David Portas]

Re: Column name in functions

No. But with good design you should never need to. Why wouldn't you know the
column name at design time?

--
David Portas
SQL Server MVP
--

[Patrik]

Re: Column name in functions

Because my data table is filled with 40 answers (columns) from a survey
(4,3,2,1) for different group. Then the user will tell me which group,
year, etc he needs the data for and I need to count the number of
4,3,2,1 for that groups for every answer (column). Not really clear !!!

But obviously you are right I will rethink my approach

Thank you for the answer

[David Portas]

Re: Column name in functions

For example, try this:

CREATE TABLE Survey (group_no INTEGER NOT NULL REFERENCES Groups (group_no),
year_no INTEGER NOT NULL, answer_no INTEGER NOT NULL CHECK (answer_no
BETWEEN 1 AND 40), response INTEGER NOT NULL CHECK (response BETWEEN 1 AND
4), PRIMARY KEY (group_no, year_no, answer_no))

SELECT response, COUNT(*)
FROM Survey
WHERE group_no = @group_no
AND year_no = @year_no
GROUP BY response

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: Column name in functions

Patrik (patrik.maheux@ umontreal.ca) writes:[color=blue]
> Because my data table is filled with 40 answers (columns) from a survey
> (4,3,2,1) for different group. Then the user will tell me which group,
> year, etc he needs the data for and I need to count the number of
> 4,3,2,1 for that groups for every answer (column). Not really clear !!!
>
> But obviously you are right I will rethink my approach[/color]

You should most certainly make the columns into rows instead. The way
databases work, it's a lot easier to handle repeating groups if they
are rows instead of columns.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Patrik]

Re: Column name in functions

I think cannot make my columns into rows because the data comes like
that from an optical reader in a text format that I import.Let me be
clearer :

My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
I can have 125 respondants(row s) for one code thus the autoid
DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
like these

Then I need to count the number of 4-3-2 and 1 for every personcode.

I will try the proposed solution and let the group know if it works

Thank you again for the help


Erland Sommarskog wrote:[color=blue]
> Patrik (patrik.maheux@ umontreal.ca) writes:[color=green]
> > Because my data table is filled with 40 answers (columns) from a survey
> > (4,3,2,1) for different group. Then the user will tell me which group,
> > year, etc he needs the data for and I need to count the number of
> > 4,3,2,1 for that groups for every answer (column). Not really clear !!!
> >
> > But obviously you are right I will rethink my approach[/color]
>
> You should most certainly make the columns into rows instead. The way
> databases work, it's a lot easier to handle repeating groups if they
> are rows instead of columns.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[David Portas]

Re: Column name in functions

The format the data is supplied in should not dictate the database design.
Design the database correctly and then develop a process to load the data
into that database from its external source.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: Column name in functions

Patrik (patrik.maheux@ umontreal.ca) writes:[color=blue]
> I think cannot make my columns into rows because the data comes like
> that from an optical reader in a text format that I import.Let me be
> clearer :
>
> My main table is autokey-year-personcode-Answer1 thru 40 (43 columns).
> I can have 125 respondants(row s) for one code thus the autoid
> DATA looks like: 2000-101-4-3-3-4-2-1-3-4-2-3-2...thousands of lines
> like these
>
> Then I need to count the number of 4-3-2 and 1 for every personcode.
>
> I will try the proposed solution and let the group know if it works[/color]

As David said, don't let the input format dictate your data model. That
format will give you a headache somewhere on the line, and I'm telling
you the earlier you handle it in the process, the less headache you will
get.

For this case, I would unpack the string with a list-to-table function,
see http://www.sommarskog.se/arrays-in-s...st-of-integers
for such a function. For your case you would have handle listpos 1, 2
and 3 individually, and then the answers would be everything above 4.
You could use the function as is, but you could also adapt it so it
directly unpacks into the format you need.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp