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
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. :-)
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
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
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5007 Visits: 3232
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
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
This was very helpful . Thank you very much.
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