SELECT g.*, c.course_name,
			CASE WHEN 
				(
					SELECT COUNT(*)
					FROM groups g2
					INNER JOIN group_players gp
					ON gp.group_id = g2.id
					INNER JOIN shots s
					ON s.player_id = gp.player_id
					AND s.round = g2.round
					AND s.hole_id IN (SELECT id FROM holes WHERE event_id = g2.event_id AND course_id = g2.course_id)
					WHERE g2.id = g.id
				) > 0 
				OR 
				(
					SELECT COUNT(*)
					FROM groups g2
					INNER JOIN group_players gp
					ON gp.group_id = g2.id
					INNER JOIN player_holes ph
					ON ph.player_id = gp.player_id
					AND ph.round = g2.round
					AND ph.hole_id IN (SELECT id FROM holes WHERE event_id = g2.event_id AND course_id = g2.course_id)
					WHERE g2.id = g.id
				) > 0 
			THEN 1
			ELSE 0
			END group_started
			
			FROM groups g
			INNER JOIN courses c
			ON c.id = g.course_id
     	WHERE g.event_id = 5003
      AND g.round = 1 
      ORDER BY g.group_number

      SELECT g.*, c.course_name, dbo.udf_checkGroupStarted(g.id) group_started
			FROM groups g
			INNER JOIN courses c
			ON c.id = g.course_id
     	WHERE g.event_id = 5003
      AND g.round = 1 
      ORDER BY g.group_number

USE [scoring_dev]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_checkGroupStarted]    Script Date: 12/03/2010 15:58:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[udf_checkGroupStarted] 
(
	-- Add the parameters for the function here
	@groupid int
)
RETURNS bit
AS
BEGIN
	-- Declare the return variable here
	DECLARE @started bit

	-- Add the T-SQL statements to compute the return value here
	
	DECLARE @group_shots int
	DECLARE @group_holes int
	
	SET @group_shots = (
		SELECT COUNT(*)
		FROM groups g
		INNER JOIN group_players gp
		ON gp.group_id = g.id
		INNER JOIN shots s
		ON s.player_id = gp.player_id
		AND s.round = g.round
		AND s.hole_id IN (SELECT id FROM holes WHERE event_id = g.event_id AND course_id = g.course_id)
		WHERE g.id = @groupid
	)

	SET @group_holes = (
			SELECT COUNT(*)
			FROM groups g
			INNER JOIN group_players gp
			ON gp.group_id = g.id
			INNER JOIN player_holes ph
			ON ph.player_id = gp.player_id
			AND ph.round = g.round
			AND ph.hole_id IN (SELECT id FROM holes WHERE event_id = g.event_id AND course_id = g.course_id)
			WHERE g.id = @groupid
	)

	IF (@group_shots > 0) 
		SET @started = 1
	ELSE IF (@group_holes > 0)
		SET @started = 1
	ELSE SET @started = 0
	--SELECT @started
	
	-- Return the result of the function
	RETURN @started

END

quote:

---

Originally posted by pixelwiz

Hi All,

I've been trying to learn how to properly use stored procedures and functions. My understanding was that using udf functions inside my SQL code should speed things up because they become pre-compiled code and can execute faster.
<snip>

---