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 11:29 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
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.
Post #1383333
Posted Tuesday, November 13, 2012 5:08 AM
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
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
Post #1384035
Posted Tuesday, November 13, 2012 5:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 884, Visits: 2,414
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
Post #1384049
Posted Tuesday, November 13, 2012 8:24 AM
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
This was very helpful . Thank you very much.
Post #1384124
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse