Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

stored procedure problem_urgent!!! Expand / Collapse
Author
Message
Posted Friday, November 9, 2012 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 8:18 AM
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).
Post #1383240
Posted Friday, November 9, 2012 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1383253
Posted Friday, November 9, 2012 2:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 8:18 AM
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;
}
Post #1383261
Posted Friday, November 9, 2012 3:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 3,924, Visits: 8,915
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383263
Posted Friday, November 9, 2012 3:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1383267
Posted Friday, November 9, 2012 3:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 3,924, Visits: 8,915
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383270
Posted Friday, November 9, 2012 3:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1383273
Posted Friday, November 9, 2012 3:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1383274
Posted Friday, November 9, 2012 3:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 8:18 AM
Points: 6, Visits: 11
:) 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
Post #1383276
Posted Friday, November 9, 2012 3:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 3,924, Visits: 8,915
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383278
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse