Solved

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

Posted on 2011-03-23
2
225 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:jsvb1977
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35203330

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
 

Author Comment

by:jsvb1977
ID: 35203521
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now