How to declare and set a value to a variable in a stored procedure based on a nested sql statement

I am sure this is basic stuff but what I am trying to do is populate a variable using a nested sql select statement INSIDE of a stored procedure.

Based on the value of that variable I want to then Execute other stored procedures or not.
See the code below.

USE [MyDB]
GO

/****** Object:  StoredProcedure [dbo].[spLoginCount]    Script Date: 03/23/2011 18:25:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jason Smith
-- Create date: 01/22/2011
-- Description:	Updates LoginCount Table and Runs first time login procudures if it is the users first login
-- =============================================
ALTER PROCEDURE [dbo].[spLoginCount] 
	-- Add the parameters for the stored procedure here
	@UserID int
AS
BEGIN

	SET NOCOUNT ON;

-- @@@ First, get logincount # from LoginStats Table and store in a variable
DECLARE @LoginCount int
@LoginCount = SELECT LoginCount FROM LoginStats WHERE UserID = @UserID
@LoginCount = @LoginCount + 1

-- @@@ Next, add 1 to the variable and update the login stats table
UPDATE [LoginStats]
   SET [AccountStatusID] = '1'
      ,[DateModified] = GETDATE()
 WHERE UserID = @UserID

-- @@@ Last, if the original varaible is a 0, then run the "first login routines"
If @LoginCount = 0 Then
	EXECUTE spCreateRequest, @UserID
	EXECUTE spCreateTask, @UserID
END IF

END
GO

Open in new window


And here are the errors when I execute the sp during an ALTER

Msg 102, Level 15, State 1, Procedure spLoginCount, Line 18
Incorrect syntax near '@LoginCount'.
Msg 102, Level 15, State 1, Procedure spLoginCount, Line 19
Incorrect syntax near '@LoginCount'.
Msg 137, Level 15, State 2, Procedure spLoginCount, Line 28
Must declare the scalar variable "@LoginCount".

Open in new window


Any assistance is helpful.
Thank you,
Jason
jsvb1977Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:

try
ALTER PROCEDURE [dbo].[spLoginCount] 
(
	-- Add the parameters for the stored procedure here
	@UserID int
)
AS
BEGIN

	SET NOCOUNT ON;

-- @@@ First, get logincount # from LoginStats Table and store in a variable
DECLARE @LoginCount int
SELECT @LoginCount = LoginCount FROM LoginStats WHERE UserID = @UserID
set @LoginCount = @LoginCount + 1  --you are adding 1 so the test if @LoginCount = 0 is not valid

-- @@@ Next, add 1 to the variable and update the login stats table
UPDATE [LoginStats]
   SET [AccountStatusID] = '1'
      ,[DateModified] = GETDATE()
 WHERE UserID = @UserID

-- @@@ Last, if the original varaible is a 0, then run the "first login routines"
If @LoginCount = 0 
begin
	EXECUTE spCreateRequest @UserID
	EXECUTE spCreateTask @UserID
END 

END

Open in new window

0
 
jsvb1977Author Commented:
So, if I do something like this, then I should be able to use the test?

ALTER PROCEDURE [dbo].[spLoginCount] 
(
        -- Add the parameters for the stored procedure here
        @UserID int
)
AS
BEGIN

        SET NOCOUNT ON;

-- @@@ First, get logincount # from LoginStats Table and store in a variable
DECLARE @LoginCount int
DECLARE @LoginCount2 int

SELECT @LoginCount = LoginCount FROM LoginStats WHERE UserID = @UserID
SET @LoginCount2 = @LoginCount + 1  --you are adding 1 so the test if @LoginCount = 0 is not valid

-- @@@ Next, add 1 to the variable and update the login stats table
UPDATE [LoginStats]
   SET [LoginCount] = @LoginCount2
      ,[DateModified] = GETDATE()
 WHERE UserID = @UserID

-- @@@ Last, if the original varaible is a 0, then run the "first login routines"
If @LoginCount = 0 
BEGIN
        EXECUTE spCreateRequest @UserID
        EXECUTE spCreateTask @UserID
END 

END

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.