Help me on Write Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

SQL

Create PROCEDURE [dbo].[Sp_TaccountdetailsB] 
@i int,
@UserId int,
@amount decimal(18,2),
@duserid int,
@caccountnumber varchar(50)
AS
BEGIN
	
	if @i=0
	BEGIN
	select AccountNumber,AccountType,Amount from tblT_AccountDetailsB where AccountDetailsId=( select MAX(AccountDetailsId) from tblT_AccountDetailsB where UserId = @UserId)
	END
	ELSE IF @i=1
	BEGIN
	select AccountNumber,Amount,Credited,Debited from tblT_AccountDetailsB where AccountDetailsId=(select  MAX(AccountDetailsId) from tblT_AccountDetailsB where UserId=@UserId)
	END
	ELSE IF @i=2
	BEGIN
	select AccountNumber,Amount,Credited,Debited from tblT_AccountDetailsB where UserId=@UserId
	END
	ELSE IF @i=3
	BEGIN
	Declare @cAccountType varchar(50)
	Declare @dAccountType varchar(50)
	Declare @cBranchId int
	Declare @dBranchId int
	Declare @cuserid int
    Declare @daccountnumber varchar(50)
    declare @damount decimal(18,2)
    declare @camount decimal(18,2)
	set @daccountnumber=(select AccountNumber from tblT_AccountDetailsB where UserId=@duserid)
	set @cuserid=(select distinct UserId from tblT_AccountDetailsB where AccountNumber=@caccountnumber)
	set @cAccountType=(select Distinct AccountType from tblT_AccountDetailsB where UserId=@cuserid)
	set @dAccountType=(select Distinct AccountType from tblT_AccountDetailsB where UserId=@duserid)
	set @cBranchId=(select Distinct BranchId from tblT_AccountDetailsB where UserId=@cuserid)
	set @dBranchId=(select Distinct BranchId from tblT_AccountDetailsB where UserId=@UserId)
	set @damount=((select Amount from tblT_AccountDetailsB where UserId=@duserid)-@amount)
	set @camount=((select Amount from tblT_AccountDetailsB where UserId=@cuserid)+@amount)
	insert into tblT_AccountDetailsB (AccountNumber,BranchId,AccountType,Amount,UserId,Debited) values (@daccountnumber,@dBranchId,@dAccountType,@damount,@duserid,@amount)
	insert into tblT_AccountDetailsB (AccountNumber,BranchId,AccountType,Amount,UserId,Credited) values (@caccountnumber,@cBranchId,@cAccountType,@camount,@cuserid,@amount)

	end

this stored procedure is executed but not returns the UserId
please help me how to write Stored Procedure If i want to retun UserId Please...

Posted 8-Aug-12 3:52am

tejaramesh97

Updated 8-Aug-12 3:58am

Kenneth Haugland

v4

Add a Solution

Comments

[no name] 8-Aug-12 10:21am

http://www.sqlteam.com/article/stored-procedures-returning-data

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Answer 1 · 2012-08-08T05:36:00

Solution 2

Because you are not returning or selecting user id in the above procedure.

try

SQL

return @UserId

at the end

or

SQL

select @UserId.

Posted 8-Aug-12 5:36am

Santhosh Kumar Jayaraman

Aarti Meswania · Answer 2 · 2012-08-08T04:21:00

Solution 1

write down this 'return' statement when store-procedure ends

SQL

insert into tblT_AccountDetailsB (AccountNumber,BranchId,AccountType,Amount,UserId,Credited) values (@caccountnumber,@cBranchId,@cAccountType,@camount,@cuserid,@amount)

--here you can return userid
return @userid

end

Happy Coding!
:)

Posted 8-Aug-12 4:21am

Aarti Meswania

Updated 8-Aug-12 4:22am

v2

Comments

tejaramesh97 9-Aug-12 4:40am

thanx

Aarti Meswania 9-Aug-12 4:47am

welcome :)