#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Posts
    2
    Rep Power
    0

    Must declare the scalar variable "@Score1"


    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? Thanks in advance.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Posts
    2
    Rep Power
    0
    I found out the solution by using CAST operator. Here it is:

    WHEN SchoolCode = 1 THEN ' + CAST(@Score1 AS VARCHAR) + '
    WHEN SchoolCode = 2 THEN ' + CAST(@Score2 AS VARCHAR) + '
    WHEN SchoolCode = 3 THEN ' + CAST(@Score3 AS VARCHAR) + '

    Thanks anyway.

IMN logo majestic logo threadwatch logo seochat tools logo