0 Replies Latest reply on Apr 8, 2008 3:58 PM by scorpionn_king

    sql function in COLDFUSION

    scorpionn_king Level 1
      Does anybody can suggest me how do i write SQL FUNCTIONS in Coldfusion, i tried to run a SQL FUNCTION in a <cfquery> but that returned me an Coldfusion Error. do i need to use <cfstoredProc> for that or. any other <tag available>


      <cfquery name="blahblah" datasource="#dsn#">

      CREATE FUNCTION [dbo].[ConcatUserGroups](@UserID int)
      RETURNS VARCHAR(8000)
      AS
      BEGIN
      DECLARE @groupname Varchar(2000)
      SET @groupname = ''
      SELECT @groupname = @groupname + ', ' + CONVERT(varchar(20), u.group_name)
      FROM [user] A
      JOIN UserGroup_combo UG
      ON UG.ug_userID = a.[USER_ID]
      JOIN usergroup U
      ON UG.ug_groupID = u.group_ID
      WHERE A.[USER_ID] = @userID
      AND LEN(ISNULL(U.group_name,'')) > 0
      ORDER BY U.GROUP_NAME

      IF(LEN(@groupname) >= 2) BEGIN
      SET @groupname = RIGHT(@groupname,(LEN(@groupname) - 2))
      END
      RETURN @groupname
      END
      GO
      </cfquery>