Link to home
Start Free TrialLog in
Avatar of mathburg
mathburg

asked on

basic stored procedure question microsoft sql

I am very new with stored procedures.  I have created this procedure GetLOAN2 and I can call the info by the execute statement below by manually putting in the ACCT.  What I want to do is hand it the account number dynamically or from a query.  How can I do this?

Thank you in advance

Create  PROCEDURE GetLOAN2(

@ACCT VARCHAR(50)                   --Input parameter ,  Studentid of the student

)
AS
BEGIN
SELECT  COL_CD, ACCT, SUFFIX, BAL
 FROM tbl_LOAN WHERE ACCT=@ACCT
END

Execute GetLOAN2 WHERE ACCT LIKE 'xxxxxxx'
Avatar of Aneesh
Aneesh
Flag of Canada image

If you want ot loop thru the dataset and call the sp for each entry, you need to use a cursor or a similar mechanism
Aneeshattingal is right. You will have to select first the account records you wish to execute the procedure for, then put those results into a cursor and execute the procedure one at a time. That would look something like this:

Set nocount on

Declare	@Acct	Varchar(50)

Declare	T_Acct Cursor For

	Select	Acct
	from	YourAccountTable
	Order by Acct

	Open T_Acct

	Fetch	next from T_acct
	Into
		@Acct
	
	While (@@Fetch_status <> -1)
	
	Begin
	
		Exec	GetLOAN2 @Acct
	
	Fetch	next from T_acct
	Into
		@Acct
	
	End
	
	Close	T_acct
	Deallocate T_Acct
	
set nocount off

Open in new window

     
Where youraccounttable is the table which contains your main account list.
Aneeshattingal is right. You will have to select first the account records you wish to execute the procedure for, then put those results into a cursor and execute the procedure one at a time. That would look something like this:

Set nocount on

Declare	@Acct	Varchar(50)

Declare	T_Acct Cursor For

	Select	Acct
	from	YourAccountTable
	Order by Acct

	Open T_Acct

	Fetch	next from T_acct
	Into
		@Acct
	
	While (@@Fetch_status <> -1)
	
	Begin
	
		Exec	GetLOAN2 @Acct
	
	Fetch	next from T_acct
	Into
		@Acct
	
	End
	
	Close	T_acct
	Deallocate T_Acct
	
set nocount off

Open in new window

     
Where youraccounttable is the table which contains your main account list.
Aneeshattingal is right. You will have to select first the account records you wish to execute the procedure for, then put those results into a cursor and execute the procedure one at a time. That would look something like this:

Set nocount on

Declare	@Acct	Varchar(50)

Declare	T_Acct Cursor For

	Select	Acct
	from	YourAccountTable
	Order by Acct

	Open T_Acct

	Fetch	next from T_acct
	Into
		@Acct
	
	While (@@Fetch_status <> -1)
	
	Begin
	
		Exec	GetLOAN2 @Acct
	
	Fetch	next from T_acct
	Into
		@Acct
	
	End
	
	Close	T_acct
	Deallocate T_Acct
	
set nocount off

Open in new window

     
Where youraccounttable is the table which contains your main account list.
Apologies for the triple post. I still don't know why this happens.
Avatar of mathburg
mathburg

ASKER

Thank you, I will try it out.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, Worked like a charm.  The only problem I'm looking at now is an exception that was thrown in executing. 'OutOfMemoryException'
> it could be because you are displaying lot of data on your local computer on SQL SERVER MANAGEMENT Studio
You can go ahead an try the one without a cursor; that will be faster and effective
Actually it looks great now, Ran succesfully.  Thank you.  I think the exception was due to the cursor suggestion.  I deleted the other one and ran this one with my tables and it worked great.

 Create  PROCEDURE GetLOAN3
AS
BEGIN
SELECT  COL_CD, ACCT, SUFFIX, BAL
 FROM tbl_LOAN WHERE ACCT in (SELECT acct FROM ... where acct like 'xxxxxx%' )
END

Thank you
Thanks again