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'
Microsoft DevelopmentMicrosoft SQL Server

Avatar of undefined
Last Comment
mathburg

8/22/2022 - Mon
Aneesh

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
Koen Van Wielink

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.
Koen Van Wielink

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Koen Van Wielink

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.
Koen Van Wielink

Apologies for the triple post. I still don't know why this happens.
mathburg

ASKER
Thank you, I will try it out.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Aneesh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mathburg

ASKER
Thank you, Worked like a charm.  The only problem I'm looking at now is an exception that was thrown in executing. 'OutOfMemoryException'
Aneesh

> it could be because you are displaying lot of data on your local computer on SQL SERVER MANAGEMENT Studio
Aneesh

You can go ahead an try the one without a cursor; that will be faster and effective
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
mathburg

ASKER
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
mathburg

ASKER
Thanks again