How to execute stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to execute stored procedure

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2010-03-19 : 11:28:24

Hi,

I have a Table where i stored the list of my stored procedure.

Id Code Description Procedure_Name
-- ---- ----------- ----------------------
1 SOLD Get Qty Sold [dbo].[pr_get_quantity]
2 RECEIVED Get Qty Received [dbo].[pr_get_quantity]
3 RETURNED Get Qty Returned [dbo].[pr_get_quantity]

I succeed to loop through these records and execute these procedures by doing some validating in order to send the exact value for "Stage" parameter.
Here's the following code that works fine.

Declare c_exec_procedure
Select code
, procedure_name
From mytable

Open c_exec_procedure
Fetch Next From c_exec_procedure Into @v_code, @v_procedure_name

While @@fetch_status = 0
Begin

Declare @v_stage nvarchar(2)

If @c_code = 'SOLD'
Begin
Set @v_stage = '1'
End

Else If @c_code = 'RECEIVED'
Begin
Set @v_stage = '2'
End

Else If @c_code = 'RETURNED'
Begin
Set @v_stage = '3'
End

Exec @v_procedure_name @v_stage

Fetch Next From c_sps Into @v_code, @v_procedure_name
End

Close c_exec_procedure
Deallocate c_exec_procedure

Question:
---------
So instead doing 'If Statement' and send manually the value of 'stage', I tried to store the 'Stage value' in the database (view below).
When i executed my script it generates the following error:
[dbo].[pr_get_quantity] '1' is not a valid identifier.

How can I fix this problem ?
Thanks for your help

Paul

Id Code Description Procedure_Name
-- ---- ----------- ----------------------
1 SOLD Get Qty Sold [dbo].[pr_get_quantity] '1'
2 RECEIVED Get Qty Received [dbo].[pr_get_quantity] '2'
3 RETURNED Get Qty Returned [dbo].[pr_get_quantity] '3'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:34:24

why do you need to store procedure name in table? isnt it just a matter of making it a udf and then calling it based on table record value (1,2,3,..) using apply operator?

see 4th example here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smmcroberts
Starting Member

3 Posts

Posted - 2010-03-19 : 12:06:03

Paul,

Place @v_procedure_name in parens:

exec (@v_procedure_name)

That should do it.

--Steve

--Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:07:52

quote:
Originally posted by smmcroberts

Paul,

Place @v_procedure_name in parens:

exec (@v_procedure_name)

That should do it.

--Steve

--Steve

whats the relevance of that? it works even without ()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paulnamroud
Starting Member

26 Posts

Posted - 2010-03-19 : 12:15:46

Thank you Steve
It works fine

smmcroberts
Starting Member

3 Posts

Posted - 2010-03-19 : 12:27:25

quote:
Originally posted by visakh16

whats the relevance of that? it works even without ()

Visakh,

It doesn't work when the parameter is combined into the first variable along with the proc name (unless you use the parens).

@v_procedure_name = '[dbo].[pr_get_quantity] ''1'''

Then exec @v_procedure_name returns an error.
But exec (@v_procedure_name) works.

--Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources