Must declaret the scalar variable exec

Question

USE [Kn_online]
GO /** Object: StoredProcedure [dbo].[LOAD_KNIGHTS_SIEGE_WARFARE_USERS] Script Date: 04/12/2014 13:51:24 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[LOAD_KNIGHTS_SIEGE_WARFARE_USERS] @strUserId char(25) AS /* Author : AKUMA Update : 25.10.2009 - 14:23 */ DECLARE @Knights smallint DECLARE @MasterKnightsID smallint DECLARE @CSWFreeLoyaltyUpdateTime datetime DECLARE @AddedLoyalty int DECLARE @IsOnline tinyint SET @AddedLoyalty = 25 SELECT @Knights = Knights FROM USERDATA WHERE strUserId = @strUserId SELECT @MasterKnightsID = sMasterKnights FROM KNIGHTS_SIEGE_WARFARE WHERE sCastleIndex = 1 IF (@Knights <> 0) AND (@Knights = @MasterKnightsID) BEGIN SELECT @CSWFreeLoyaltyUpdateTime = CSWFreeLoyaltyUpdateTime FROM USERDATA WHERE strUserId = @strUserId IF DATEDIFF(day,@CSWFreeLoyaltyUpdateTime,GETDATE()) >= 1 UPDATE USERDATA SET Loyalty = Loyalty + @AddedLoyalty,CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId ELSE IF @CSWFreeLoyaltyUpdateTime IS NULL UPDATE USERDATA SET Loyalty = Loyalty + @AddedLoyalty,CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId DECLARE KSWU_CURSOR CURSOR FOR SELECT strUserId FROM USERDATA WHERE Knights = @MasterKnightsID OPEN KSWU_CURSOR FETCH NEXT FROM KSWU_CURSOR INTO @strUserId WHILE @@FETCH_STATUS = 0 BEGIN SET @CSWFreeLoyaltyUpdateTime = 0 SET @IsOnline = 0 SELECT @IsOnline = COUNT(strCharID) FROM CURRENTUSER WHERE strCharID = @strUserId IF @IsOnline = 0 BEGIN SELECT @CSWFreeLoyaltyUpdateTime = CSWFreeLoyaltyUpdateTime FROM USERDATA WHERE strUserId = @strUserId IF DATEDIFF(day,@CSWFreeLoyaltyUpdateTime,GETDATE()) >= 1 UPDATE USERDATA SET Loyalty = Loyalty + @AddedLoyalty,CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId ELSE IF @CSWFreeLoyaltyUpdateTime IS NULL UPDATE USERDATA SET Loyalty = Loyalty + @AddedLoyalty,CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId END FETCH NEXT FROM KSWU_CURSOR INTO @strUserId END CLOSE KSWU_CURSOR DEALLOCATE KSWU_CURSOR END

EXEC LOAD_KNIGHTS_SIEGE_WARFARE_USERS @strUserid

when i use it in other procedure error: must declare the scalar variable

score 0 · Answer 1 · answered Apr 12 '14 at 11:05

What a messy query, by the way you missed an END in your query, test it as below and let me know the result

GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[LOAD_KNIGHTS_SIEGE_WARFARE_USERS] @strUserId char(25) AS DECLARE @Knights smallint DECLARE @MasterKnightsID smallint DECLARE @CSWFreeLoyaltyUpdateTime datetime DECLARE @AddedLoyalty int DECLARE @IsOnline tinyint
SET @AddedLoyalty = 25
SELECT @Knights = Knights
FROM USERDATA
WHERE strUserId = @strUserId
  SELECT @MasterKnightsID = sMasterKnights
  FROM KNIGHTS_SIEGE_WARFARE WHERE sCastleIndex = 1 
  IF (@Knights <> 0) AND (@Knights = @MasterKnightsID) 
  BEGIN
      SELECT @CSWFreeLoyaltyUpdateTime = CSWFreeLoyaltyUpdateTime
      FROM USERDATA WHERE strUserId = @strUserId IF DATEDIFF(DAY,@CSWFreeLoyaltyUpdateTime,GETDATE()) >= 1
      UPDATE USERDATA
      SET Loyalty = Loyalty + @AddedLoyalty,
          CSWFreeLoyaltyUpdateTime = GETDATE() 
      WHERE strUserId = @strUserId 
--This END is missing in your code
  END
  ELSE IF @CSWFreeLoyaltyUpdateTime IS NULL
      UPDATE USERDATA
      SET Loyalty = Loyalty + @AddedLoyalty,
          CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId 
------------------------------------------------------
  DECLARE KSWU_CURSOR
  CURSOR
  FOR
  SELECT strUserId
      FROM USERDATA WHERE Knights = @MasterKnightsID 
  OPEN KSWU_CURSOR 
  FETCH NEXT FROM KSWU_CURSOR INTO @strUserId 
  WHILE @@FETCH_STATUS = 0 
  BEGIN
      SET @CSWFreeLoyaltyUpdateTime = 0
      SET @IsOnline = 0
      SELECT @IsOnline = COUNT(strCharID)
      FROM CURRENTUSER WHERE strCharID = @strUserId 
      IF @IsOnline = 0 
      BEGIN
          SELECT @CSWFreeLoyaltyUpdateTime = CSWFreeLoyaltyUpdateTime
          FROM USERDATA WHERE strUserId = @strUserId 
          IF DATEDIFF(DAY,@CSWFreeLoyaltyUpdateTime,GETDATE()) >= 1
              UPDATE USERDATA
              SET Loyalty = Loyalty + @AddedLoyalty,
                  CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId 
          ELSE IF @CSWFreeLoyaltyUpdateTime IS NULL
              UPDATE USERDATA
              SET Loyalty = Loyalty + @AddedLoyalty,
                  CSWFreeLoyaltyUpdateTime = GETDATE() WHERE strUserId = @strUserId 
      END 
      FETCH NEXT FROM KSWU_CURSOR INTO @strUserId 
  END 
  CLOSE KSWU_CURSOR 
  DEALLOCATE KSWU_CURSOR 
END

Must declaret the scalar variable exec

1 Answers1