SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure problem_urgent!!!


stored procedure problem_urgent!!!

Author
Message
marthasmithuk
marthasmithuk
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
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).
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26163 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
marthasmithuk
marthasmithuk
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
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;
}
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16613 Visits: 19098
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"


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26163 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16613 Visits: 19098
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)


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26163 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26163 Visits: 17539
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
marthasmithuk
marthasmithuk
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
Smile 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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16613 Visits: 19098
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search