Prev: Monitoring CF server web service traffic.
Next: Breaking out of coldfusion code
From: scorpionn_king on 8 Apr 2008 18:58
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>

[b]
<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>[/b]

From: GArlington on 9 Apr 2008 04:32
On Apr 8, 11:58 pm, "scorpionn_king" <webforumsu...(a)macromedia.com>
wrote:
> 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>
>
> [b]
> <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>[/b]

You create SQL function on SQL server and RUN it from CF...
From: GArlington on 9 Apr 2008 04:33
On Apr 8, 11:58 pm, "scorpionn_king" <webforumsu...(a)macromedia.com>
wrote:
> 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>
>
> [b]
> <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>[/b]

And I think that you meant procedure, NOT function...
 | 
Pages: 1
Prev: Monitoring CF server web service traffic.
Next: Breaking out of coldfusion code