How to incement row id with identity to off

Question

I am getting this error while i do select.

DECLARE @TopID INT
SELECT @TopID = MAX([QuestionId]) from [SurveyQuestions];

 SELECT
       @TopID = @TopID + 1   
       ,[questiontext]
      ,[DeptID]
      ,[SurveyID]   
  FROM [SOSS].[dbo].[SurveyQuestions] where surveyid =80

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I know there is a posibility to fix this error with identity set to ON.but i dont want that as my db is hosted in legecy application, changing this will make other things to break.

Answer 1

It is the way you are selecting, you cant use operators in select with other columns being fetched. See this link for some explanation on what you are doing wrong.

I think what you really require is this :

DECLARE @TopID INT
SELECT @TopID = MAX([QuestionId]) from [SurveyQuestions];

 SELECT
       @TopID + 1   
       ,[questiontext]
      ,[DeptID]
      ,[SurveyID]   
  FROM [SOSS].[dbo].[SurveyQuestions] where surveyid =80

EDIT : Or a ROW_NUMBER based solution like :

    DECLARE @TopID INT
    SELECT @TopID = MAX([QuestionId]) from [SurveyQuestions];

     SELECT
           @TopID + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as TopID 
           ,[questiontext]
          ,[DeptID]
          ,[SurveyID]   
      FROM [SOSS].[dbo].[SurveyQuestions] where surveyid =80

Improving a bit on the Previous Answer (combined into one query):

 SELECT (
        SELECT TOP 1 QuestionId
        FROM SurveyQuestions
        ORDER BY QuestionId DESC
        ) 
+ ROW_NUMBER() OVER (   ORDER BY (SELECT 1)) AS TopID
, [questiontext], [DeptID], [SurveyID]
FROM [SOSS].[dbo].[SurveyQuestions]
WHERE surveyid = 80

SQL Fiddler

Another Alternative, combing your 2 queries :

SELECT
    (SELECT MAX(ID) FROM ForgeRock) as MAx_Id
    , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as Row_ID, 
    (SELECT TOP 1 ID FROM ForgeRock ORDER BY Id DESC) +
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as New_Id,
    productName,
    description
FROM
    ForgeRock

SQL Fiddler