Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

passing a column name to function Expand / Collapse
Author
Message
Posted Monday, May 12, 2008 10:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 8, 2014 6:58 AM
Points: 28, Visits: 124
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
Posted Monday, May 12, 2008 11:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #499306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse