Populate variable using select

Question

Post reply

Populate variable using select

Bosco2

Ten Centuries

Points: 1240
More actions
June 16, 2010 at 6:42 am

#225766

Hello I have a basic stored procedure that has 3 input fields and performs an insert and delete. I am trying to use this code to get the userid. The substring portion works but I
cannot figure out how to get the variable @userid populated with the results from the select.
SELECT SUBSTRING(@@UserName, 1, CHARINDEX(' ', @@UserName) - 1) AS [FirstName],
SUBSTRING(@@UserName, CHARINDEX(' ', @@UserName) + 1, LEN(@@UserName)) AS [LastName]
Example Procedure:
Create Procedure Test_SplitName
@GroupID varchar(50),
@USERNAME varchar (50),
@AddDeleteFlag Varchar(1)
as
Begin
select USerID AS @user-id where firstname = (SELECT SUBSTRING(@@UserName, 1, CHARINDEX(' ', @@UserName) - 1))
and lastname = (select SUBSTRING(@@UserName, CHARINDEX(' ', @@UserName) + 1, LEN(@@UserName)))
If @AddDeleteFlag = 'A'
Begin
Insert in GroupTable (GroupID, UserId) values (@groupID, @userid)
End
If @AddDeleteFlag = 'D'
Begin
Delete GroupTable where groupid = @groupID and userID = @userid
End
End
Hope someone can help...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You have to wite it as SELECT @variable = Field:

select @user-id = USerID where firstname = (SELECT SUBSTRING(@@UserName, 1, CHARINDEX(' ', @@UserName) - 1))

and lastname = (select SUBSTRING(@@UserName, CHARINDEX(' ', @@UserName) + 1, LEN(@@UserName)))

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Gian, we can write this also like this , right?

select @user-id = USerID

where

firstname = SUBSTRING(@@UserName, 1, CHARINDEX(' ', @@UserName) - 1)

and lastname = SUBSTRING(@@UserName, CHARINDEX(' ', @@UserName) + 1, LEN(@@UserName))

vijay.s SSCrazy Points: 2161 More actions · Answer 3

Kindly provide the test script for batter result

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

ColdCoffee (6/16/2010)
Gian, we can write this also like this , right?
select @user-id = USerID
where
firstname = SUBSTRING(@@UserName, 1, CHARINDEX(' ', @@UserName) - 1)
and lastname = SUBSTRING(@@UserName, CHARINDEX(' ', @@UserName) + 1, LEN(@@UserName))

You're right: I just corrected the variable assignment and didn't look at the rest of the sql.

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Gianluca Sartori (6/16/2010)
ColdCoffee (6/16/2010)
Gian, we can write this also like this , right?
select @user-id = USerID
where
firstname = SUBSTRING(@@UserName, 1, CHARINDEX(' ', @@UserName) - 1)
and lastname = SUBSTRING(@@UserName, CHARINDEX(' ', @@UserName) + 1, LEN(@@UserName))
You're right: I just corrected the variable assignment and didn't look at the rest of the sql.

😎