asked on

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

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsvb1977

ASKER

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