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
227 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

895 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

11 Experts available now in Live!

Get 1:1 Help Now