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),@number
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]

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]

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]

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]

Patrik (pa***********@umontreal.ca) writes:

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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Patrik]

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(rows) 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:

Patrik (pa***********@umontreal.ca) writes:

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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

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]

Patrik (pa***********@umontreal.ca) writes:

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(rows) 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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp