Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » stored procedure problem_urgent!!!
Add to briefcase
14 posts, Page 1 of 2
12»»

stored procedure problem_urgent!!! Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
marthasmithuk
Posted Friday, November 09, 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
Sean Lange
Posted Friday, November 09, 2012 2:45 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
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
Post #1383253
marthasmithuk
Posted Friday, November 09, 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
Luis Cazares
Posted Friday, November 09, 2012 3:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 1,091, Visits: 2,205
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383263
Sean Lange
Posted Friday, November 09, 2012 3:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
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
Post #1383267
Luis Cazares
Posted Friday, November 09, 2012 3:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 1,091, Visits: 2,205
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383270
Sean Lange
Posted Friday, November 09, 2012 3:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
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
Post #1383273
Sean Lange
Posted Friday, November 09, 2012 3:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
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
Post #1383274
marthasmithuk
Posted Friday, November 09, 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
Luis Cazares
Posted Friday, November 09, 2012 3:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 1,091, Visits: 2,205
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383278
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.