November 9, 2012 at 2:24 pm
Create stored procedure for users registration. Procedure accepts two parameters: username and password and introduces these two values into table entitled Users. Table users has three columns: ID, username and password. When activating procedure it is first to check if the user with that username already exists in the table. If exists, the entry does not execute and the procedure returns an error message (number -1). If the user does not exist the entry introduces to database and the procedure returns success message (zero 0).
November 9, 2012 at 2:45 pm
marthasmithuk (11/9/2012)
Create stored procedure for users registration. Procedure accepts two parameters: username and password and introduces these two values into table entitled Users. Table users has three columns: ID, username and password. When activating procedure it is first to check if the user with that username already exists in the table. If exists, the entry does not execute and the procedure returns an error message (number -1). If the user does not exist the entry introduces to database and the procedure returns success message (zero 0).
This sounds a LOT like homework. Typically most people around here don't do homework for people. We can help when you get stuck. How about if you post your table and what you have tried so far? Then maybe post some thoughts about what steps you need to do next.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2012 at 2:55 pm
USE [Users]
GO
CREATE TABLE [dbo].[Users](
[Username] [nvarchar](50) NULL,
[Password] [varbinary](50) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE Users;
GO
CREATE PROCEDURE UserRegistration.usp
@Username nvarchar(50),
@Password nvarchar(50)
AS
SET NOCOUNT ON;
SELECT Username
SELECT Password
FROM Users
WHERE Username = @Username
AND EndDate IS NULL;
GO
USE Users;
GO
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = '-1')
DROP TRIGGER -1
ON DATABASE;
GO
CREATE TRIGGER -1
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR ('Data previously stored'-1)
ROLLBACK
GO
DROP TRIGGER -1
ON DATABASE;
GO
function InsertUser(string sUsername, string sPassword)
{
SELECT ID FROM Users WHERE username=sUsername; // if you get data, there is
if there is return -1
INSERT INTO Users (username,password) VALUES (sUsername,sPassword);
return 0;
}
November 9, 2012 at 3:01 pm
I'm a little more considerate than Sean and will tell you where to start. All these articles are at your disposal just by pressing F1 in SSMS.
Stored Procedures: http://msdn.microsoft.com/en-us/library/ms190782(v=sql.105).aspx
"New" way of doing what is expected:
MERGE: http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx
EDIT: You don't need a trigger, but in your script you have a way to check if the row "exists"
November 9, 2012 at 3:18 pm
Luis Cazares (11/9/2012)
I'm a little more considerate than Sean and will tell you where to start. All these articles are at your disposal just by pressing F1 in SSMS.Stored Procedures: http://msdn.microsoft.com/en-us/library/ms190782(v=sql.105).aspx
"New" way of doing what is expected:
MERGE: http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx
EDIT: You don't need a trigger, but in your script you have a way to check if the row "exists"
If I came across as inconsiderate I apologize. Certainly not my intention.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2012 at 3:25 pm
Sean Lange (11/9/2012)
Luis Cazares (11/9/2012)
I'm a little more considerate than Sean and will tell you where to start. All these articles are at your disposal just by pressing F1 in SSMS.Stored Procedures: http://msdn.microsoft.com/en-us/library/ms190782(v=sql.105).aspx
"New" way of doing what is expected:
MERGE: http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx
EDIT: You don't need a trigger, but in your script you have a way to check if the row "exists"
If I came across as inconsiderate I apologize. Certainly not my intention.
Never meant to say you were inconsiderate, but it takes a certain amount of time, and I just happen to have spare time (at least for the moment)
November 9, 2012 at 3:27 pm
I think that what you posted at the bottom of your post looks like a C# function? I don't think that is what your professor is looking for. They wants the entire logic inside your proc. I agree 100% with Luis that MERGE would be the best approach here.
Mostly you seem to be on the right track although I don't understand the trigger part at all. Also be careful, you have referenced EndDate in your query but that column is not in your Users table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2012 at 3:28 pm
Luis Cazares (11/9/2012)
Sean Lange (11/9/2012)
Luis Cazares (11/9/2012)
I'm a little more considerate than Sean and will tell you where to start. All these articles are at your disposal just by pressing F1 in SSMS.Stored Procedures: http://msdn.microsoft.com/en-us/library/ms190782(v=sql.105).aspx
"New" way of doing what is expected:
MERGE: http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx
EDIT: You don't need a trigger, but in your script you have a way to check if the row "exists"
If I came across as inconsiderate I apologize. Certainly not my intention.
Never meant to say you were inconsiderate, but it takes a certain amount of time, and I just happen to have spare time (at least for the moment)
+1 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2012 at 3:30 pm
🙂 thanks a lot. my problem is that I can't test the code so I am just trying to find the solution asking people who know
November 9, 2012 at 3:36 pm
You should be able to test your code, no one is perfect to say it's not necessary.
Why don't you install SQL Server Express with tools to test on your computer?
There are several errors in your code that make it look as if you're not sure of what you're doing.
Try to explain us what's every instruction for and we can tell you how to correct them.
November 9, 2012 at 11:29 pm
I can't because my PC does not have free space at the hard disk. I tried and PC could not perform anything. Unfortunatelly I have the teacher who does not want to help in any way, just making thing more and more difficult for me. Anyway your suggestion to use MERGE was very helpful, I am still reading these texts and soon I will try again, to write new code. Then I will need your help again: to see and suggest further corrections. You are very kind and thanks a lot. 🙂
November 13, 2012 at 5:08 am
Hi,
hope this is better! Please could you help me by suggesting corrections?
CREATE PROCEDURE [dbo].[usp_UsersRegistration]
@ID VARCHAR(10),
@Username VARCHAR(50),
@Password VARCHAR(50)
AS
MERGE [dbo].[Users] AS [Target]
USING (SELECT @Username, @Password)
AS [Source] ( [Username], [Password] )
ON [Target].[Username] = [Source].[Username]
ON [Target].[Password] = [Source].[Password]
WHEN MATCHED THEN
UPDATE SET [ID] = [Source][ID],
[Username] = [Source].[Username],
[Password] = [Source].[Password]
RETURN -1
WHEN NOT MATCHED THEN
INSERT ( [ID], [Username], [Password] )
VALUES ( [Source].[ID], [Source].[Username],
[Source].[Password];
RETURN 0
GO
November 13, 2012 at 5:55 am
There are a couple of errors in your merge.
I would ask you ID is a varchar(10) when you have the ID as an IDENTITY in your original User table creation script.
It seems that you are passing in the ID of the user so that forms the Unique lookup reference as a
CREATE PROCEDURE [dbo].[usp_UsersRegistration]
@ID VARCHAR(10),
@Username VARCHAR(50),
@Password VARCHAR(50)
AS
DECLARE @Action TABLE ([Action] varchar(100))
DECLARE @Ret tinyint
MERGE [dbo].[Users] AS [Target]
USING (SELECT @ID Id, @Username [Username], @Password [Password])
ON [Target].[Id] = [Source].[Id]
WHEN MATCHED THEN
UPDATE SET
[UserName] = [Source].[Username]
,[Password] = [Source].[Password]
WHEN NOT MATCHED THEN
INSERT ( [Username]
, [Password] )
VALUES ( [Source].[Username]
, [Source].[Password]);
OUTPUT
$Action
INTO @Action
Select @Ret=(Select CASE [Action] WHEN 'Update' Then 1 Else 0 end From @Action)
Return @Ret
GO
This should then Return -1 when an Update is executed and a 0 when an insert is executed.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 13, 2012 at 8:24 am
This was very helpful . Thank you very much.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply