CREATE PROCEDURE [dbo].[ProcTest1] @Mode INT = 0, @UserId NVARCHAR(128), @SearchString NVARCHAR(4000) = NULL
AS

SET NOCOUNT ON

SELECT MySetId
	FROM dbo.MySet 

CREATE PROCEDURE [dbo].[ProcTest2] @Mode INT = 0, @UserId NVARCHAR(128), @SearchString NVARCHAR(4000) = NULL
AS

SET NOCOUNT ON

SELECT MySetId, SetName
	FROM dbo.MySet 

CREATE PROCEDURE [dbo].[ProcTest3] @Mode INT = 0, @UserId NVARCHAR(128), @SearchString NVARCHAR(4000) = NULL
AS

SET NOCOUNT ON

CREATE TABLE #Sets (Id NVARCHAR(128))
INSERT INTO #Sets VALUES ('1')
INSERT INTO #Sets VALUES ('2')
INSERT INTO #Sets VALUES ('3')
INSERT INTO #Sets VALUES ('4')

SELECT t1.MySetId, t1.Name AS SetName, t1.Description, t1.ModelYear, t2.Name AS CategoryName, t2.ImageUrl AS CategoryImageUrl,
	FROM dbo.MySet t1 (NOLOCK)
		INNER JOIN dbo.MySetCategory t2 (NOLOCK)
		ON t1.MySetCategoryId = t2.MySetCategoryId
	WHERE t1.MySetId IN (SELECT Id FROM #Sets)
	ORDER BY t2.Name, t1.ModelYear DESC, t1.Name

CREATE PROCEDURE [dbo].[ProcTest4] @Mode INT = 0, @UserId NVARCHAR(128), @SearchString NVARCHAR(4000) = NULL
AS

SET NOCOUNT ON

SELECT t1.MySetId, t1.Name AS SetName, t1.Description, t1.ModelYear, t2.Name AS CategoryName, t2.ImageUrl AS CategoryImageUrl,
	FROM dbo.MySet t1 (NOLOCK)
		INNER JOIN dbo.MySetCategory t2 (NOLOCK)
		ON t1.MySetCategoryId = t2.MySetCategoryId
	ORDER BY t2.Name, t1.ModelYear DESC, t1.Name