Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Must declare the scalar variable.

    CREATE PROCEDURE [dbo].[Testing]
    @FilteredID VARCHAR (MAX),
    @SchoolCode VARCHAR (MAX),
    @Score1 INT,
    @Score2 INT,
    @Score3 INT

    AS
    BEGIN
    DECLARE @SQLStr AS VARCHAR (MAX);
    SET @SqlStr = 'UPDATE dbo.School
    SET Score = CASE
    WHEN SchoolCode = 1 THEN @Score1
    WHEN SchoolCode = 2 THEN @Score2
    WHEN SchoolCode = 3 THEN @Score3
    END';
    IF @FilteredID > '' OR @SchoolCode > ''
    BEGIN
    SET @SqlStr = @SqlStr + ' WHERE id IN (' + @FilteredID + ') AND code IN (' + @SchoolCode + ')';
    END
    EXECUTE (@SqlStr );
    END

    I tried to execute above sproc in SQL Server Management Studio , I received the error: Must declare the scalar variable "@Score1". Any idea what wrong with my stored procedure codes? I really appreciate your time to help me out on this error. Thanks in advance.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The problem is your dynamic SQL statement. Assuming values of "1" and "10" for @FilteredID and @SchoolCode respectively, what you are generating is the following:
    Code:
    UPDATE dbo.School
    WHEN SchoolCode = 1 THEN @Score1
    WHEN SchoolCode = 2 THEN @Score2
    WHEN SchoolCode = 3 THEN @Score3
    END' WHERE id IN (1) AND code IN (10)
    You need to concatenate the values of @Score1, @Score2 and @Score3 into the string.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    I am not sure what you said that I need to concatenate the values of @Score1, @Score2 and @Score3 into the string? In my school table, score column has integer datatype. Actually, I passed the parameter values from front end as following:

    sqlCmd.Parameters.AddWithValue("@Score1", Convert.ToInt32(scoreOne));
    sqlCmd.Parameters.AddWithValue("@Score2", Convert.ToInt32(scoreTwo));
    sqlCmd.Parameters.AddWithValue("@Score3", Convert.ToInt32(scoreThree));

    sqlCmd.ExecuteNonQuery();

    if I execute the following query without error:

    UPDATE dbo.School
    SET score = CASE
    WHEN SchoolCode = 1 THEN 75
    WHEN SchoolCode = 2 THEN 100
    WHEN SchoolCode = 3 THEN 50
    END

    WHERE id IN ('1','2') AND code IN ('10', '11')


    Please give me further helping. Thanks.
    Last edited by avt2k6; 06-17-13 at 13:02.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The point is that you are using the names of the variables in your dynamic SQL statement, not their values. You need to use the values in the dynamic SQL statement in order for it to work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    I tried this format ' + @Score1 + ' but not working. How do I pass values to names of variables in dynamic SQL statement?
    Last edited by avt2k6; 06-17-13 at 14:00.

  6. #6
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Here are my answers on what your comments. The reason that I used T-SQL dynamic because I am passing a string of user selections on selected checkbox marks from front end in order to filter the resulted set; your skeleton, I can search on Google a lot. Sometimes, the thing we hate but we have to work on what existed data? You can not change it if you do not have a right to do so or you are messing up with previous developement. The id column is just an example that I posted on question; it can be SSN, identified number, etc ... and it is not id column that generate from database. What I need helps on how to fix the error that I got? Thanks anyway. Here is a solution to fix my above error, hopefully if anybody got same issue they can refer to it. Just simple if we know the CAST operation.

    CREATE PROCEDURE [dbo].[Testing]
    @FilteredID VARCHAR (MAX),
    @SchoolCode VARCHAR (MAX),
    @Score1 INT,
    @Score2 INT,
    @Score3 INT

    AS
    BEGIN
    DECLARE @SQLStr AS VARCHAR (MAX);
    SET @SqlStr = 'UPDATE dbo.School
    SET Score = CASE
    WHEN SchoolCode = 1 THEN ' + CAST(@Score1 AS VARCHAR) + '
    WHEN SchoolCode = 2 THEN ' + CAST(@Score2 AS VARCHAR) + '
    WHEN SchoolCode = 3 THEN ' + CAST(@Score3 AS VARCHAR) + '
    END';
    IF @FilteredID > '' OR @SchoolCode > ''
    BEGIN
    SET @SqlStr = @SqlStr + ' WHERE id IN (' + @FilteredID + ') AND code IN (' + @SchoolCode + ')';
    END
    EXECUTE (@SqlStr );
    END
    Last edited by avt2k6; 06-18-13 at 12:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •