passing a column name to function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » passing a column name to function

passing a column name to function

Rate Topic

Display Mode

Topic Options

Author

Message

Prakash-485822

Posted Monday, May 12, 2008 10:47 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:46 AM
Points: 26, Visits: 110

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 .

Post #499300

Jeff Moden

Posted Monday, May 12, 2008 11:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 33,113, Visits: 27,041

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #499306

« Prev Topic | Next Topic »

Permissions