Stored Procedure -Question-

Question

Post reply

Stored Procedure -Question-

Mando_eg

SSChasing Mays

Points: 648
More actions
May 10, 2015 at 4:43 am

#398432

Hello,
I have 2 stored Procedures
the first get the UserId by UserName parameter
the second Delete the user from multiple tables by UserId
ALTER PROCEDURE [dbo].[GetUserIDbyUserName]
@UserName nvarchar(100)
AS
SELECT
[UserId]
FROM
[Users]
WHERE
[UserName] = @UserName
SET QUOTED_IDENTIFIER OFF
the Second
ALTER PROCEDURE [dbo].[DeleteUserByUserName]
@UserName nvarchar(100)
AS
declare @user-id uniqueidentifier
exec @user-id = [dbo].[GetUserIDbyUserName] @UserName
delete from Memberships where UserId = @user-id
delete from UsersInRoles where UserId = @user-id
delete from Users where UserId = @user-id
I want to send the username only to the sencond procedure to do everything, Please tell what I'm missing in the previous codes

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick suggestion, use OUTPUT parameter for the procedure

😎

/* Procedure #1 */

CREATE PROCEDURE [dbo].[GetUserIDbyUserName]

@UserName nvarchar(100)

,@UserID uniqueidentifier = NULL OUTPUT

AS

SELECT

@user-id = [UserId]

FROM

[Users]

WHERE

[UserName] = @UserName

GO

/* Procedure #2 */

CREATE PROCEDURE [dbo].[DeleteUserByUserName]

@UserName nvarchar(100)

AS

declare @user-id uniqueidentifier

exec [dbo].[GetUserIDbyUserName] @UserName ,@UserID OUTPUT

delete from Memberships where UserId = @user-id

delete from UsersInRoles where UserId = @user-id

delete from Users where UserId = @user-id

GO

Mando_eg SSChasing Mays Points: 648 More actions · Answer 2

Thanks for the quick and easy reply 🙂

Appreciated

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Hmmmm.... I recognize those tables. I would strongly recommend NEVER deleting from those tables because 1) you could orphan a whole lot of information that many applications rely on for "who dunnit" and 2) any auditors that may pay you a visit will flunk you immediately for tampering with historical information. There are columns in some of the tables (like the Users table and others) that will disable logins/users but you should never delete from those tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)