set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SPs_GetEventFilteredSorted]
	-- Add the parameters for the stored procedure here
	@BUID int = null,
	@Name nvarchar(MAX) = null,
	@DateTimeFrom datetime = null,
	@DateTimeFrom2 datetime = null,
	@Reporter nvarchar(MAX) = null,
	@Crew nvarchar(MAX) = null,
	@sql nvarchar(MAX),
	@sortExpression nvarchar(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @BUID = 0
		SET @BUID = null

	IF LEN(@Name) = 0
		SET @Name = null
	ELSE
		SET @Name = '%' + @Name + '%'

	IF LEN(@Reporter) = 0
		SET @Reporter = null
	ELSE
		SET @Reporter = '%' + @Reporter + '%'

	IF LEN(@Crew) = 0
		SET @Crew = null
	ELSE
		SET @Crew = '%' + @Crew + '%'

    -- Insert statements for procedure here
	SET @sql = 'SELECT 
					EventID, Name, Description, BUID, Location, DateTimeFrom, DateTimeTo, 
					Embargo, Reporter, Crew, RSVP, GuestOfHonour, Remarks, Status, 
					CreatedDate, CreatedBy, UpdatedDate, UpdatedBy 
				FROM Event 
				WHERE     
					(BUID LIKE COALESCE(@BUID,BUID)) AND 
					(Name LIKE COALESCE(@Name,Name)) AND 
					(DateTimeFrom >= COALESCE(@DateTimeFrom,DateTimeFrom)) AND 
					(DateTimeFrom <= COALESCE(@DateTimeFrom2,DateTimeFrom)) AND 
					(Reporter LIKE COALESCE(@Reporter,Reporter)) AND 
					(Crew LIKE COALESCE(@Crew,Crew)) 
				ORDER BY ' + @sortExpression

	-- Execute the SQL query
	EXEC sp_executesql @sql

END

USE [CAS]
GO

EXEC	[dbo].[SPs_GetEventFilteredSorted]
		@BUID = 0,
		@Name = N'Event',
		@DateTimeFrom = N'2007-10-17',
		@DateTimeFrom2 = N'2007-10-18',
		@Reporter = N'Reporter',
		@Crew = N'Crew',
		@sql = NULL,
		@sortExpression = N'EventID'

GO

quote:

---

Originally posted by jackv

The way to execute it as :
USE [CAS]
GO

EXEC [dbo].[SPs_GetEventFilteredSorted]
0,
'Event',
'2007-10-17',
'2007-10-18',
'Reporter',
'Crew',
NULL,
'EventID'

GO

---

USE [CAS]
GO

EXEC	[dbo].[SPs_GetEventFilteredSorted]
		@BUID = 0,
		@Name = N'Event',
		@DateTimeFrom = N'2007-10-17',
		@DateTimeFrom2 = N'2007-10-18',
		@Reporter = N'Reporter',
		@Crew = N'Crew',
		@sql = NULL,
		@sortExpression = N'EventID'

GO

USE [CAS]
GO

EXEC [dbo].[SPs_GetEventFilteredSorted]
0,
'Event',
'2007-10-17',
'2007-10-18',
'Reporter',
'Crew',
NULL,
'EventID'

GO

Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@BUID".

quote:

---

Originally posted by Kristen

I don't think you need to use Dynamic SQL for this at all

Kristen

---

ORDER BY 
	CASE WHEN @sortExpression = 'EventID' THEN EventID END,
	CASE WHEN @sortExpression = 'Col2' THEN Col2 END DESC,
	...

quote:

---

Originally posted by madhivanan

Instead of dynamic sql, you can do conditional sorting

Order by case when @var='somevalue then col1 else col2 end

EDIT : Fixed typo

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Kristen

"EDIT : Fixed typo"

You going to fix it properly then?

---

quote:

---

Originally posted by thatzlim

quote:

---

Originally posted by madhivanan

Instead of dynamic sql, you can do conditional sorting

Order by case when @var='somevalue then col1 else col2 end

EDIT : Fixed typo

Madhivanan

Failing to plan is Planning to fail

---

it is not going to be 2 columns of sorting. that's why i'm using dynamic sql.

---