Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
243 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:
Ephraim Wangoya earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased riskā€¦

782 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