How to get the field name into a variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to get the field name into a variable

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-06 : 04:10:38

Hello,

I want to pass a field name as a variable so as to take it's count. My Stored Proc is

Create procedure sp_subjectExist
@sub varchar(10),
@sgCode varchar(3)
as
declare @sCnt int
begin
select Count(@Sub) from marks where regno in(select sregno from student where subjectgroupid=@sgCode)
select @sub
end
sp_subjectExist 'sub01', 'A'

it's count is not working.

Please help

Regards
Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-06 : 04:14:37

You need to use Dynamice sql. Is it necessary to pass column name as parameter?
Try this logic
http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 04:15:45

counting of records?

use
select count(*) from tablename
where subjectgroupid=@sgcode

or is this a join?

select count(*) from table1 t1
join table2 t2 on t1.regno=t2.regno
where t2.subjectgroupid=@sgcode

what's sub for?

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-06 : 04:16:03

Not sure what you want to do.
That's not setting @sub so
select @sCnt = Count(*) from marks where regno in(select sregno from student where subjectgroupid=@sgCode)
select @sCnt

probablt @sCnt should be an output variable.
Then the question is what do you want to do with @sub
maybe

select @sCnt = Count(*) from marks where regno in(select sregno from student where subjectgroupid=@sgCode) and subject = @sub

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 04:25:39

ceema,
can you post your marks table structure ?
if you need to do

sp_subjectExist 'sub01', 'A'
sp_subjectExist 'sub02', 'A'
etc

quote:
I want to pass a field name as a variable so as to take it's count

It seems to me that it is not normalized.

Also avoid naming your stored procedure begin with sp_. It will cause SQL Server to search the master db for the SP before the current DB which is not efficient.

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-06 : 04:34:07

Actually, I am trying to pass the field name by @Sub Variable. The last line is like

Exec sp_subjectExist 'sub01', 'A'

My field names may vary and Subject groupID too can vary. So, what I want is

I have to get sregno from student table according to SubjectGroupID.

Then, I have to get the count of any of the fields(here ,I am passing it from stored procedure call as 'sub01'(my fields name values are like sub01,sub02,sub03 etc)) from Marks Table whoes RegNo is in the first selection set.

Regards
Ceema

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 04:43:11

you are counting the rows as per record, unless you want to disregard null values for the field you specified

in this case, you will need dsql as Maddy suggested...

--------------------
keeping it simple...

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-06 : 04:53:45

Hello,

Thank you every one for the reply. I will try Dynamic Query. And 'khtan', My table structure is

RegNo Sub01 Sub02 Sub03 Sub04 ... Sub16

I know it's not normilized, but I am newly joined in this company and I can't change the existing system. Thank you for your advice in naming the stored proc.

Regards
Ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 05:39:48

You can create a view that is normalized and use the view in the stored procedure. Then you can avoid using Dynamic SQL.


create view v_marks
as
select RegNo, 'Sub01' as SubCode, Sub01 as Sub
from   marks
union all
select RegNo, 'Sub02' as SubCode, Sub02 as Sub
from   marks
union all
select RegNo, 'Sub03' as SubCode, Sub03 as Sub
from   marks
. . .


Create procedure usp_subjectExist
@sub varchar(10),
@sgCode varchar(3)
as
declare @sCnt int
begin
    select Count(*) from v_marks 
    where  SubCode = @sub
    and    RegNo in(select sregno from student where subjectgroupid=@sgCode)
end

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-06 : 07:45:04

Good idea khtan. ceema - I'd definitely go for that one...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-07 : 07:07:40

My requirement, I think no body is getting.

Actually, I want to pass the field name as the variable,
not a value to match to the variable.

I have 6 fields in my table

RegNo, Sub01,Sub02,Sub03,Sub04,Sub05

I want to make a query in such a way that,
I want to pass any of these field names(not a value to the field, it's exactly the field name only) ,

So, in my first query

Create procedure sp_subjectExist
@sub varchar(10),
@sgCode varchar(3)
as
declare @sCnt int
begin
select Count(@Sub) from marks where regno in(select sregno from student where subjectgroupid=@sgCode)
select @sub
end

Exec sp_subjectExist 'sub01', 'A' .....> Sub01 is not a value, but it's a field name.

For example

In Student table 3 values are there

SRegNo SgCode

111 A
222 B
333 A

Marks

RegNo Sub01 Sub02 Sub03 Sub04 Sub05

111 2 3 4 5 6
222 12 13 Null 15 16 .....> Here Null for Sub03
333 22 23 24 25 26

If I will pass

Exec sp_subjectExist 'sub01', 'A' it should give me the value fields without null count as 3
Exec sp_subjectExist 'sub03', 'A' it should give me the value fields without null count as 2

Hope that now I explained it in detail

Please help

Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-07 : 07:55:54

Try this


Create procedure sp_subjectExist
@sub varchar(10),
@sgCode varchar(3)
as
declare @sCnt int
begin 
Exec('select Count('+@Sub+') from marks where regno in
	(select sregno from student where subjectgroupid='''+@sgCode+''')' 

end

Exec sp_subjectExist 'sub01', 'A'

Also you need to read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources