passing a column name to function

Question

passing a column name to function

Prakash-485822

SSC Eights!

Points: 982
More actions
May 12, 2008 at 10:47 pm

#70366

Dear All
I have the following function that the returns the value with comma delimitor
ex:
FormCode ITem-Code
RS-001 IT-0001,IT-0002,IT-003
CREATE FUNCTION dbo.ItemList_fn
( @FormId int )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @TempOEDetails table
( FormCodeDetails varchar(1000) )
DECLARE @FormCodeList varchar(1000)
DECLARE @TempFormCodeList varchar(1000)
SET @FormCodeList = ''
INSERT INTO @TempOEDetails
SELECT Items.ITemCode
FROM MainTable
Inner Join Items on MainTable .FormId = Items.FormId
WHERE MainTable.FormId = @FormId
And Items.StuffingId is not null
IF @@ROWCOUNT > 0
UPDATE @TempOEDetails
SET @FormCodeList = ( @FormCodeList + FormCodeDetails + ', ' )
IF(len(@FormCodeList)>0)
BEGIN
Set @TempFormCodeList= substring( @FormCodeList, 1, ( len( @FormCodeList ) - 1 ))
END
ELSE
BEGIN
SET @TempFormCodeList = ''
END
RETURN @TempFormCodeList
END
But now i want to have pass the column name to the function
eg: if i passes the to function
select Formid,dbo.dbo.ItemList_fn (FormId,'Color') Item Name
it should return me
FormCode ITem-Code
RS-001 Bule,Green,Yellow
and
select Formid,dbo.dbo.ItemList_fn (FormId,'Quantity') Item Name
it should return me
FormCode ITem-Code
RS-001 5,10,15
Please help me to solve this .

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

First, the function you have uses a cursor/while loop and it's going to be relatively slow. See the following for a couple of tips how to do it another way while avoiding some common pitfalls...

http://www.sqlservercentral.com/articles/Test+Data/61572/

Second, to make the column a parameter would require the use of Dynamic SQL. That requires an EXEC in one form or another and only extended stored procedures can be executed with EXEC from within a function. In English, what you ask cannot be done in a function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)