how to use declare variable in select query in stored procedure using sql server

Question

Hello I want to concate two things one is string and other is int variable. Now, these thing I want to store in one variable and use that variable in select query as a into type to create a temptable in stored procedure using sql server.

Here is my query

USE [FlightExamSoftware]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- For Storing Question in Temp table  
-- EXEC [GetQuestionListPerSubjectRatioWise] 1,11
ALTER PROCEDURE [dbo].[GetQuestionListPerSubjectRatioWise]
@SubjectID INT,
@NumberOfQue INT,
@UserID int
AS
BEGIN
DECLARE @strQuery VARCHAR(MAX);
DECLARE @PerChapQue INT;

DECLARE @tempTable VARCHAR(MAX) = 'tempTestUser' + @UserID;

SELECT @PerChapQue = COUNT(appQueID)/@NumberOfQue FROM tblQuestion WHERE appQueSubID=@SubjectID

SELECT COUNT(appQueID)/@PerChapQue ChapwiseQue
    ,CASE WHEN COUNT(appQueID)>=@PerChapQue THEN COUNT(appQueID)/@PerChapQue ELSE 1 END ChapWiseQuePlusOne
    ,appQueChapID into @tempTable
    FROM tblQuestion 
    WHERE appQueSubID=@SubjectID   
    GROUP BY appQueChapID
END

Now, I am talking about these line

DECLARE @tempTable VARCHAR(MAX) = 'tempTestUser' + @UserID;

In these line two things are concate one is string and other is int. And store in varchar variable.

And use in following select query i.e.

SELECT COUNT(appQueID)/@PerChapQue ChapwiseQue
    ,CASE WHEN COUNT(appQueID)>=@PerChapQue THEN COUNT(appQueID)/@PerChapQue ELSE 1 END ChapWiseQuePlusOne
    ,appQueChapID into @tempTable
    FROM tblQuestion 
    WHERE appQueSubID=@SubjectID   
    GROUP BY appQueChapID
END

Now, in these query I want to create a temptable named @tempTable.

But, in these line it showing error i.e. Incorrect syntax near '@tempTable'.

Confuse that where is the syntax is wrong.

Thank You.

Answer 1

Hope this Dynamic Query helps you:

Try like this:

USE [FlightExamSoftware]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- For Storing Question in Temp table  
-- EXEC [GetQuestionListPerSubjectRatioWise] 1,11
ALTER PROCEDURE [dbo].[GetQuestionListPerSubjectRatioWise]
@SubjectID INT,
@NumberOfQue INT,
@UserID int
AS
BEGIN
DECLARE @strQuery VARCHAR(MAX);
DECLARE @PerChapQue INT;

DECLARE @tempTable VARCHAR(MAX) = 'tempTestUser' + CAST(@UserID AS VARCHAR);

SELECT @PerChapQue = COUNT(appQueID)/@NumberOfQue FROM tblQuestion WHERE appQueSubID=@SubjectID

SET @strQuery='
SELECT COUNT(appQueID)/'+CAST(@PerChapQue AS VARCHAR)+' ChapwiseQue
    ,CASE WHEN COUNT(appQueID)>='+CAST(@PerChapQue AS VARCHAR)+' THEN COUNT(appQueID)/'+CAST(@PerChapQue AS VARCHAR)+' ELSE 1 END ChapWiseQuePlusOne
    ,appQueChapID 
INTO '+@tempTable+'
FROM tblQuestion 
WHERE appQueSubID='+CAST(@SubjectID AS VARCHAR)+'
GROUP BY appQueChapID

/*.................................
And you have to use the temp table inside the String only
.................................*/

'

EXEC (@strQuery)
END

Answer 2

There are a number of things wrong with your code.

1. When concatenating an int to a string, you must first cast the int to varchar. Otherwise, SQL Server will try to implicitly convert the string to int, that will result with an error.
   So this: DECLARE @tempTable VARCHAR(MAX) = 'tempTestUser' + @UserID; should become this:
   DECLARE @tempTable VARCHAR(MAX) = 'tempTestUser' + CAST(@UserID AS VARCHAR(11)); (you need 11 chars to be able to fit the minimum value of int: -2,147,483,648)

2. You can't use select...into with a table variable.
   You can only use it for actual tables (temporary or regular).

3. your @tempTable isn't even a table variable (not that it will help with a select...into).

4. Even if you would use select...into the correct way, unless you are going to use a global temporary table (and that doesn't come without it's risks), Unless your stored procedure uses this temporary table later on, it will be useless, since temporary tables are bound to scope.

Taking all of that into consideration I'm not sure what output you are actually looking for. If you could edit your question to include the desired output of your stored procedure as well as some sample data as DDL+DML, it would be easier to help you write better code.