help converting this store procedure to a query

Question

Pardon my ignorance, but i am not sure how to convert the following stored procedure to a query. I am trying to reverse engineer a management pack for scsm that creates a report and it calls for a stored procedure. It is not working so I am trying each process. The procedure is as follows:

USE [DWDataMart]
GO
/****** Object: StoredProcedure [dbo].[SCUtils_Report_SRSLAManagement_SP_GetListOfServiceRequests_Install] Script Date: 10/8/2021 11:36:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SCUtils_Report_SRSLAManagement_SP_GetListOfServiceRequests_Install]
@DateFilter nvarchar(256) = null,
@StartDate datetime,
@EndDate datetime,
@Source Technology nvarchar(max) = '-1',
@Sta nvarchar(max) = '-1',
@Priority Networks nvarchar(max) = '-1',
@Urgency nvarchar(max) = '-1',
@ImplementationResults nvarchar(max) = '-1',
@Zack nvarchar(max) = '-1',
@SupportGroup nvarchar(max) = '-1',
@AssignedTo int = null,
@ContactMethod nvarchar(max) = null,
@SLAStatus nvarchar(max) = '-1',

@Description	nvarchar(max) = null,  
@ID  nvarchar(max) = null,  
@IncludeDeleted bit = 0,  
@LanguageCode nvarchar(max)= 'ENU'  

AS
BEGIN
SET NOCOUNT ON

DECLARE @Error int
DECLARE @ExecError int

SET @EndDate = DateAdd(Day, 1, @EndDate)

DECLARE @tableID TABLE(value nvarchar(256))
INSERT @tableID (value)
Select * FROM dbo.fn_CSVToTableString(ISNULL(@ID, ''))

DECLARE @tableSource TABLE (value nvarchar(256))
INSERT @tableSource (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Source Technology )

DECLARE @tableStatus TABLE (value nvarchar(256))
INSERT @tableStatus (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Sta )

DECLARE @tablePriority TABLE (value nvarchar(256))
INSERT @tablePriority (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Priority Networks )

DECLARE @tableUrgency TABLE (value nvarchar(256))
INSERT @tableUrgency (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)

DECLARE @tableArea TABLE (value nvarchar(256))
INSERT @tableArea (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Zack )

DECLARE @tableSupportGroup TABLE (value nvarchar(256))
INSERT @tableSupportGroup (value)
SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroup)

DECLARE @tableImplementationResults TABLE (value nvarchar(256))
INSERT @tableImplementationResults (value)
SELECT * FROM dbo.fn_CSVToTableInt(@ImplementationResults)

DECLARE @tableSLAStatus TABLE (value nvarchar(256))
INSERT @tableSLAStatus (value)
SELECT * FROM dbo.fn_CSVToTableInt(@SLAStatus)

SELECT DISTINCT
SR.ServiceRequestDimKey,
SR.CreatedDate,
Description=NULL,
SR.CompletedDate,
SR.ClosedDate,
SR.Id,
SR.Title,

Source = ISNULL(SourceDS.DisplayName, SourceEnum.ServiceRequestSourceValue) ,
SourceEnum.ServiceRequestSourceId AS SourceId,

Status = ISNULL(StatusDS.DisplayName, StatusEnum.ServiceRequestStatusValue) ,
StatusEnum.ServiceRequestStatusId AS StatusId,

Priority = ISNULL(PriorityDS.DisplayName, PriorityEnum.ServiceRequestPriorityValue),
PriorityEnum.ServiceRequestPriorityId AS PriorityId,

ImplementationResults = ISNULL(ImplementationResultsDS.DisplayName, ImplementationResultsEnum.ServiceRequestImplementationResultsValue),
ImplementationResultsEnum.ServiceRequestImplementationResultsId AS ImplementationResultsId,

AssignedTo.UserDimKey AssignedToUserId,
AssignedTo.DisplayName AssignedToUserName,

SLAinfo.duration as SLAInstanceDuration,
SLAinfo.ActiveDuration as SLAInstanceActiveDuration,

SLAStatus = ISNULL(SLAStatusDS.DisplayName, SLAStatus.SLAInstanceStatusValue) ,
SLAStatus.SLAInstanceStatusId AS SLAStatusId,

SLAconfig.DisplayName as SLAName

FROM
dbo.ServiceRequestDimvw SR

INNER JOIN dbo.WorkItemDimvw WI  
ON SR.EntityDimKey = WI.EntityDimKey  
  
LEFT OUTER JOIN	  
dbo.WorkItemAssignedToUserFactvw  
ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey   
AND (@IncludeDeleted = 1 OR dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)  
  
LEFT OUTER JOIN  
dbo.UserDimvw AS AssignedTo   
ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey   
  
LEFT OUTER JOIN   
dbo.WorkItemAboutConfigItemFactvw ON   
dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey   
AND (@IncludeDeleted = 1 OR dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)  
  
LEFT OUTER JOIN   
dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON   
CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey   
AND (@IncludeDeleted = 1 OR CIFctForFilter.DeletedDate IS NULL)  

LEFT OUTER JOIN	  
dbo.WorkItemHasSLAInstanceInformationFactvw  
ON dbo.WorkItemHasSLAInstanceInformationFactvw.WorkItemDimKey = WI.WorkItemDimKey  
AND (@IncludeDeleted = 1 OR dbo.WorkItemHasSLAInstanceInformationFactvw.DeletedDate IS NULL)  

LEFT OUTER JOIN  
dbo.SLAInstanceTimeInformationDimvw AS SLAinfo   
ON dbo.WorkItemHasSLAInstanceInformationFactvw.WorkItemHasSLAInstanceInformation_SLAInstanceTimeInformationDimKey = SLAinfo.SLAInstanceTimeInformationDimKey  
  
LEFT OUTER JOIN	  
dbo.InformationRefersToSLAConfigurationFactvw  
ON dbo.InformationRefersToSLAConfigurationFactvw.SLAInstanceTimeInformationDimKey = SLAinfo.SLAInstanceTimeInformationDimKey  
AND (@IncludeDeleted = 1 OR dbo.InformationRefersToSLAConfigurationFactvw.DeletedDate IS NULL)  

LEFT OUTER JOIN  
dbo.SLAConfigurationDimvw AS SLAconfig   
ON dbo.InformationRefersToSLAConfigurationFactvw.InformationRefersToSLAConfiguration_SLAConfigurationDimKey = SLAconfig.SLAConfigurationDimKey  
  
LEFT OUTER JOIN  
SLAInstanceStatusvw AS SLAStatus   
ON SLAStatus.SLAInstanceStatusId = SLAinfo.Status_SLAInstanceStatusId  

LEFT OUTER JOIN   
dbo.DisplayStringDimvw SLAStatusDS  
ON SLAStatus.EnumTypeId=SLAStatusDS.BaseManagedEntityId  
AND SLAStatusDS.LanguageCode = @LanguageCode  

LEFT OUTER JOIN  
dbo.ServiceRequestSourcevw AS SourceEnum   
ON SourceEnum.ServiceRequestSourceId = SR.Source_ServiceRequestSourceId    
  
LEFT OUTER JOIN   
dbo.DisplayStringDimvw SourceDS  
ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId  
AND SourceDS.LanguageCode = @LanguageCode  
  
LEFT OUTER JOIN  
dbo.ServiceRequestStatusvw AS StatusEnum   
ON StatusEnum.ServiceRequestStatusId = SR.Status_ServiceRequestStatusId    
  
LEFT OUTER JOIN   
dbo.DisplayStringDimvw StatusDS  
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId  
AND StatusDS.LanguageCode = @LanguageCode  
  
LEFT OUTER JOIN  
dbo.ServiceRequestPriorityvw AS PriorityEnum   
ON PriorityEnum.ServiceRequestPriorityId = SR.Priority_ServiceRequestPriorityId  
  
LEFT OUTER JOIN   
dbo.DisplayStringDimvw PriorityDS  
ON PriorityEnum.EnumTypeId=PriorityDS.BaseManagedEntityId  
AND PriorityDS.LanguageCode = @LanguageCode  

LEFT OUTER JOIN  
dbo.ServiceRequestImplementationResultsvw AS ImplementationResultsEnum   
ON ImplementationResultsEnum.ServiceRequestImplementationResultsId = SR.ImplementationResults_ServiceRequestImplementationResultsId  
  
LEFT OUTER JOIN   
dbo.DisplayStringDimvw ImplementationResultsDS  
ON ImplementationResultsEnum.EnumTypeId=ImplementationResultsDS.BaseManagedEntityId  
AND ImplementationResultsDS.LanguageCode = @LanguageCode  

WHERE
(
(@DateFilter = 'CompletedOn' AND ((SR.CompletedDate >= @StartDate) AND (SR.CompletedDate < @EndDate))) OR
(@DateFilter = 'ClosedOn' AND ((SR.ClosedDate >= @StartDate) AND (SR.ClosedDate < @EndDate))) OR
(@DateFilter = 'CreatedOn' AND ((SR.CreatedDate >= @StartDate) AND (SR.CreatedDate < @EndDate))) OR
(@DateFilter = 'All')
) AND
(@StartDate <= @EndDate) AND
(SLAInfo.IsCancelled = 0) AND
((-1 IN (Select value from @tableSource)) OR (SR.Source_ServiceRequestSourceId IN (Select value from @tableSource))) AND
((-1 IN (Select value from @tableStatus)) OR (SR.Status_ServiceRequestStatusId IN (Select value from @tableStatus))) AND
((-1 IN (Select value from @tablePriority)) OR (SR.Priority_ServiceRequestPriorityId IN (Select value from @tablePriority))) AND
((-1 IN (Select value from @tableUrgency)) OR (SR.Urgency_ServiceRequestUrgencyId IN (Select value from @tableUrgency))) AND
((-1 IN (Select value from @tableArea)) OR (SR.Area_ServiceRequestAreaId IN (Select value from @tableArea))) AND
((-1 IN (Select value from @tableSupportGroup)) OR (SR.SupportGroup_ServiceRequestSupportGroupId IN (Select value from @tableSupportGroup))) AND
((-1 IN (Select value from @tableSLAStatus)) OR (SLAinfo.Status_SLAInstanceStatusId IN (Select value from @tableSLAStatus))) AND
(
(-1 in (Select value from @tableImplementationResults))
OR
(
SR.ImplementationResults_ServiceRequestImplementationResultsId IN (Select value from @tableImplementationResults)
OR
(
(SR.ImplementationResults_ServiceRequestImplementationResultsId IS NULL)
AND
('' IN (Select value from @tableImplementationResults))
)
)
) AND
((@AssignedTo = 0) OR AssignedTo.UserDimKey = @AssignedTo) AND
((@ID IS NULL) OR (SR.Id IN (Select value from @tableID)))

SET @Error = @@ERROR

QuitError:

RETURN @Error
END

Answer 1

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'nvarchar'.
Msg 137, Level 15, State 2, Line 36
Must declare the scalar variable "@EndDate".
Msg 137, Level 15, State 2, Line 40
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@Source Technology ".
Msg 137, Level 15, State 2, Line 48
Must declare the scalar variable "@Sta ".
Msg 137, Level 15, State 2, Line 52
Must declare the scalar variable "@Priority Networks ".
Msg 137, Level 15, State 2, Line 56
Must declare the scalar variable "@Urgency".
Msg 137, Level 15, State 2, Line 60
Must declare the scalar variable "@Zack ".
Msg 137, Level 15, State 2, Line 64
Must declare the scalar variable "@SupportGroup".
Msg 137, Level 15, State 2, Line 68
Must declare the scalar variable "@ImplementationResults".
Msg 137, Level 15, State 2, Line 72
Must declare the scalar variable "@SLAStatus".
Msg 137, Level 15, State 2, Line 117
Must declare the scalar variable "@IncludeDeleted".
Msg 102, Level 15, State 1, Line 205
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 205
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 206
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 206
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 207
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 207
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 208
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 208
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 209
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 209
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 210
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 210
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 211
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 211
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 213
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 217
Incorrect syntax near the keyword 'OR'.
Msg 102, Level 15, State 1, Line 221
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 226
Incorrect syntax near ')'.
Msg 178, Level 15, State 1, Line 232
A RETURN statement with a return value cannot be used in this context.

Answer 2

Well, after following what you for changing alter to create, the procedure worked. I changed it back to alter and that works. It looks like that one change fixed what was missing and now the alter works as it is supposed to.

Answer 3

When I copy the code in your original post, I don't get any of these errors. In fact, once I change ALTER to CREATE, the procedure creates successfully. (But it will obviously not run, since I don't have the tables.) So apparently, these error messages apply to something else that we don't see.

You said that the procedure was not working. What Tom asked for was any error message you get when you ran the procedure as posted.

Answer 4

To expand the context, when I run the query/procedure, I get errors of missing tables. The tables are there. What I am trying to find out is what is this doing differently as a stored procedure versus a regular query. I am self taught in SQL so my terminology and thought process does not fully follow standards. I was thinking since the tables are there, but I am getting errors. and when I run the query, I get syntax errors. I was thinking that maybe somehow the stored procedure needed to have a syntax adjustment to make it a viable query.

Answer 5

What do you mean "converting this store procedure to a query"? The stored proc is a query.