I currently have an issue where I can run my dynamic stored procedure locally in SQL Server Management Studio without issues. My problem mainly starts when I start running my application, once the scripts start getting compiled it breaks. The application stops compiling and complains about:
Incorrect syntax near begin statement
IF EXISTS(SELECT * FROM sys.objects WHERE NAME = 'users')
BEGIN
DROP PROCEDURE IF EXISTS [users];
END;
GO
CREATE PROCEDURE [users]
(@Username nvarchar(500),
@Progress nvarchar(500),
@StartDate nvarchar(500),
@EndDate nvarchar(500),
@DisplayName nvarchar(500),
@CourseName nvarchar(500),
@Search nvarchar(500),
@Sort nvarchar(500),
@Page int,
@PageSize int)
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT Off
SET QUOTED_IDENTIFIER ON
DECLARE @offsetcount nvarchar(500)
DECLARE @pagecount nvarchar(500)
DECLARE @SQL nvarchar(500)
DECLARE @SortFilter nvarchar(500)
SET @offsetcount = convert(nvarchar,(@PageSize * (@Page - 1)))
SET @pagecount = convert(nvarchar,(@PageSize))
SET @SQL = 'SELECT [u].[Name], [u].[Username],
SUM(CASE WHEN [c].[Status] = ''Published'' THEN 1 ELSE 0 END) AS Total,
sum(case when [ec].[UserCourseStatus] = ''In Progress'' AND [c].[Status] = ''Published'' then 1 else 0 end) AS Pending,
sum(case when [ec].[UserCourseStatus] = ''Completed'' AND [c].[Status] = ''Published'' then 1 else 0 end) AS Completed
FROM (([db].[UserCourse] As [ec]
INNER JOIN [db].[User] As [u]
ON [u].[Id] = [ec].[UserId])
INNER JOIN [db].[Course] As [c]
ON [c].[Id] = [ec].[CourseId])
WHERE ([u].[StatusCode] = 2 OR [u].[StatusCode] IS NULL) AND [c].[Status] = ''Published''';
IF @DisplayName IS NOT NULL AND LEN(@DisplayName) > 0
SET @SQL = @SQL + ' AND [u].[Name] = ''' + @DisplayName + ''''
IF @Progress IS NOT NULL AND LEN(@Progress) > 0
SET @SQL = @SQL + ' AND [ec].[UserCoursestatus] = ''' + @Progress + ''''
IF @StartDate IS NOT NULL AND LEN(@StartDate) > 0
SET @SQL = @SQL + ' AND ([ec].[DateFirstStarted] >= ''' + @StartDate + ''' AND [ec].[DateFirstCompleted] <= ''' + @EndDate + ''')'
IF @CourseName IS NOT NULL AND LEN(@CourseName) > 0
SET @SQL = @SQL + ' AND [c].[Name] = ''' + @CourseName + ''''
IF @Search IS NOT NULL AND LEN(@Search) > 0
SET @SQL = @SQL + ' AND ([e].[Name] like ''%' + @Search + '%'' or' + '[e].[Username] like ''%' + @Search + '%'')'
SET @SortFilter = CASE
@Sort
WHEN 'UserAsc' THEN '[u].[Name] ASC'
WHEN 'UserDesc' THEN '[u].[Name] DESC'
WHEN 'In Progress' THEN '[ec].[UserCourseStatus] DESC'
WHEN 'Completed' THEN '[ec].[UserCourseStatus] ASC'
WHEN 'CreatedDesc' THEN '[ec].[DateFirstStarted] DESC, [ec].[DateFirstCompleted] DESC'
WHEN 'CreatedAsc' THEN '[ec].[DateFirstStarted] ASC, [ec].[DateFirstCompleted] ASC'
ELSE '[u].[Name] DESC'
END
SET @SQL = @SQL + ' GROUP BY [u].[Name], [u].[Username]'
SET @SQL = @SQL + ' Order BY ' + @SortFilter
IF @Page IS NOT NULL AND LEN(@Page) > 0 AND @Page != 0
SET @SQL = @SQL + ' OFFSET ' + @offsetcount + ' ROWS FETCH NEXT ' + @pagecount + ' ROWS ONLY'
ELSE
SET @SQL = @SQL + ' OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY'
EXEC(@SQL)
END
GO
Rather than injecting the actual search values, you need to pass them all through as parameters, via sp_executesql. The ORDER BY is the only one that must be injected.
You should also pick better data types. Also don't bother quoting with [] unless you have to, it's much more difficult to read.
CREATE OR ALTER PROCEDURE [users]
@Username nvarchar(500),
@Progress nvarchar(500),
@StartDate datetime,
@EndDate datetime,
@DisplayName nvarchar(500),
@CourseName nvarchar(500),
@Search nvarchar(500),
@Sort nvarchar(500),
@Page int,
@PageSize int
AS
SET NOCOUNT ON;
DECLARE @SQL nvarchar(max);
DECLARE @SortFilter nvarchar(max)
SET @SQL = '
SELECT
[u].[Name],
[u].[Username],
SUM(CASE WHEN [c].[Status] = ''Published'' THEN 1 ELSE 0 END) AS Total,
sum(case when [ec].[UserCourseStatus] = ''In Progress'' AND [c].[Status] = ''Published'' then 1 else 0 end) AS Pending,
sum(case when [ec].[UserCourseStatus] = ''Completed'' AND [c].[Status] = ''Published'' then 1 else 0 end) AS Completed
FROM [db].[UserCourse] As [ec]
INNER JOIN [db].[User] As [u] ON [u].[Id] = [ec].[UserId]
INNER JOIN [db].[Course] As [c] ON [c].[Id] = [ec].[CourseId]
WHERE ([u].[StatusCode] = 2 OR [u].[StatusCode] IS NULL)
AND [c].[Status] = ''Published''
';
IF @DisplayName IS NOT NULL AND LEN(@DisplayName) > 0
SET @SQL += ' AND [u].[Name] = @DisplayName
';
IF @Progress IS NOT NULL AND LEN(@Progress) > 0
SET @SQL += ' AND [ec].[UserCoursestatus] = @Progress
';
IF @StartDate IS NOT NULL
SET @SQL += ' AND ([ec].[DateFirstStarted] >= @StartDate AND [ec].[DateFirstCompleted] <= @EndDate)
';
IF @CourseName IS NOT NULL AND LEN(@CourseName) > 0
SET @SQL += ' AND [c].[Name] = @CourseName
';
IF @Search IS NOT NULL AND LEN(@Search) > 0
SET @SQL += ' AND ([e].[Name] like ''%'' + @Search + ''%'' or [e].[Username] like ''%'' + @Search + ''%'')
';
SET @SQL += 'GROUP BY [u].[Name], [u].[Username]
';
SET @SortFilter = CASE
@Sort
WHEN 'UserAsc' THEN '[u].[Name] ASC'
WHEN 'UserDesc' THEN '[u].[Name] DESC'
WHEN 'In Progress' THEN '[ec].[UserCourseStatus] DESC'
WHEN 'Completed' THEN '[ec].[UserCourseStatus] ASC'
WHEN 'CreatedDesc' THEN '[ec].[DateFirstStarted] DESC, [ec].[DateFirstCompleted] DESC'
WHEN 'CreatedAsc' THEN '[ec].[DateFirstStarted] ASC, [ec].[DateFirstCompleted] ASC'
ELSE '[u].[Name] DESC'
END;
SET @SQL += ' Order BY ' + @SortFilter + '
';
IF @Page IS NOT NULL AND @Page <> 0
SET @SQL += ' OFFSET @PageSize * (@Page - 1) ROWS FETCH NEXT @pagecount ROWS ONLY';
ELSE
SET @SQL = @SQL + ' OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
END;
EXEC sp_executesql
@SQL,
N'
@Username nvarchar(500),
@Progress nvarchar(500),
@StartDate datetime,
@EndDate datetime,
@DisplayName nvarchar(500),
@CourseName nvarchar(500),
@Search nvarchar(500),
@Page int,
@PageSize int
',
@Username = @Username,
@Progress = @Progress,
@StartDate = @StartDate,
@EndDate = @EndDate,
@DisplayName = @DisplayName,
@CourseName = @CourseName,
@Search = @Search,
@Sort = @Sort,
@Page = @Page,
@PageSize = @PageSize;