Error in Query?

The below line gives a red squirly under the last + when copied to SSMS

set @Query= 'SELECT'+ ROW_NUMBER()OVER +'

I assume you're trying to create a dynamic query. Further I assume that you want  ROW_NUMBER() OVER to be evaluated when the stored procedure is executed, not when you create the stored procedure. The same applies to the end of your query with BETWEEN.

From that perspective, the beginning of your query should look like

set @Query= 'SELECT ROW_NUMBER() OVER
(
ORDER BY [ID]desc
) AS RowNumber
,[ID]

Further the way you use it is not correct. You need an additional select. I'll demo further below.

Also, I don't trust the end of your query, but that is just a feel; this looks ugly for some reason; probably the highlighted part.

,[ModifiedOn] INTO #Results FROM [Master_ProductInfo] '+ @SubCategoryName + ' '+ 'SELECT @RecordCount = COUNT(*) FROM #Results

I suggest that you start with a normal (non-dynamic) stored procedure and try to get that working first. Next you can make it dynamic by placing single ticks at the beginning and the end of the string and assigning it to @Query.


A little demo;

Non-dynamic stored procedure:
CREATE PROCEDURE [dbo].[usp_rownumber]
  -- Add the parameters for the stored procedure here
  @pFirst int,
  @pLast int
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

select * from
(select
ROW_NUMBER() over
(
order by Name
) as RowNumber
,*
from tblScores
) as tbldata

where tbldata.RowNumber between @pFirst AND @pLast

Note the additional select. If it works (it did in my case ;)), it can be made dynamic.

Building dynamic stored procedure:
We'll start by declaring a avariable to hold the query. I've highlighted in yellow the changes compared to the original
ALTER PROCEDURE [dbo].[usp_rownumber]
  -- Add the parameters for the stored procedure here
  @pFirst int,
  @pLast int
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

declare @Query nvarchar(MAX)
set @Query =
'
select * from
(select
ROW_NUMBER() over
(
order by Name
) as RowNumber
,*
from tblScores
) as tbldata

where tbldata.RowNumber between @pFirst AND @pLast
'

Next we need to prepare a 'list' of the parameters that we want to pass. I've placed it after the above. I've also added some debug print statements so when we execute the store procedure we can see what happens (in SSMS)

declare @ParamDefinition nvarchar(500)
set @ParamDefinition = '@pFirst int, @pLast int'

print @Query
print @ParamDefinition
print @pFirst
print @pLast

Lastly we'll tell the stored procedure to execute our SQL statement

execute sp_executesql @Query, @ParamDefinition, @pFirst, @pLast
END

The complete stored procedure looks like

ALTER PROCEDURE [dbo].[usp_rownumber]
  -- Add the parameters for the stored procedure here
  @pFirst int,
  @pLast int
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

declare @Query nvarchar(MAX)
set @Query =
'
select * from
(select
ROW_NUMBER() over
(
order by Name
) as RowNumber
,*
from tblScores
) as tbldata

where tbldata.RowNumber between @pFirst AND @pLast
'

declare @ParamDefinition nvarchar(500)
set @ParamDefinition = '@pFirst int, @pLast int'

print @Query
print @ParamDefinition
print @pFirst
print @pLast

execute sp_executesql @Query, @ParamDefinition, @pFirst, @pLast
END

Save the stored procedure and execute it. You can look in the messages tab of the output window to see what is created.

I hope this gets you on the right track.