Stored Procedure name stored in a table

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)

Stored Procedure name stored in a table

Author

Topic

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-18 : 03:31:15

Hi,

In a table, I have the fields Id, Name, StoredProcedureName. I have created the storedProcedure and saved it in in the db folder.. Wat I should do is when I execute a specific id, the storedProcedureName should call the procedure and execute it..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 03:55:04

http://www.sommarskog.se/dynamic_sql.html

EXEC, sp_execute_sql

E 12°55'05.25"
N 56°04'39.16"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-18 : 04:36:11

At a basic level, although a Peso has outlined you may want to use the sp_execute_sql method, which has benefits of compilatio
DECLARE @tempStr VARCHAR(200),@id INT
SET @id = 1
SET @tempstr = '(SELECT StoredProcedureName FROM myTable WHERE id ='& @id &')'
EXEC(@tmpstr)

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 04:47:22

quote:
Originally posted by jackv

At a basic level, although a Peso has outlined you may want to use the sp_execute_sql method, which has benefits of compilatio
DECLARE @tempStr VARCHAR(200),@id INT
SET @id = 1
SET @tempstr = '(SELECT StoredProcedureName FROM myTable WHERE id ='& @id &')'
EXEC(@tmpstr)

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Did you mean this?

DECLARE @tempStr VARCHAR(200),@id INT
SET @id = 1
SET @tempstr = (SELECT StoredProcedureName FROM myTable WHERE id =@id)
EXEC(@tmpstr)

Madhivanan

Failing to plan is Planning to fail

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-18 : 05:12:59

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[StatisticLineTest]

AS
BEGIN

SET NOCOUNT ON;

DECLARE @Proc nvarchar(200),
@ExecSql nvarchar(255),
@RowCnt int,
@MaxRows int

SELECT @RowCnt = 1
SELECT @Proc = StoredProcedureName FROM StatisticLine

DECLARE @Import TABLE (RowNO int IDENTITY (1, 1) Primary key NOT NULL , StatisticLineId nvarchar(50))
INSERT INTO @Import (StatisticLineId) SELECT StatisticLineId FROM StatisticLine

SELECT @MaxRows=count(*) FROM @Import

WHILE @RowCnt <= @MaxRows
BEGIN
SELECT @ExecSql = 'exec ' + @Proc + ' ''' + StatisticLineId + '''' from @Import where RowNO = @RowCnt
EXECUTE sp_executesql @ExecSql
SELECT @RowCnt = @RowCnt + 1
END

END
GO

what is wrong????

error: Msg 8114, Level 16, State 5, Procedure Stats_UserMainProfileSelectWomenUnder40, Line 0
Error converting data type varchar to int.

plz help

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-21 : 06:26:09

Plz help!!! I have a table where all the stored procedure names are kept. I must be able to execute these stored procedures.. Plz help

ns_nataly
Starting Member

13 Posts

Posted - 2008-01-21 : 07:10:27

Is error refering SP you are executing in a loop? - try to debug it first.

Natalia

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-21 : 07:32:34

nah, .. it is when u execute the above stored procedure that u get the mentioned error.

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-21 : 08:15:19

does anyone have any other soln????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 08:41:20

[code]ALTER PROCEDURE dbo.StatisticsLineTest
AS

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(1000),
@RowID INT

DECLARE @Import TABLE
(
RowID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL,
StoredProcedureName NVARCHAR(200),
StatisticsLineID NVARCHAR(50) NOT NULL
)

INSERT @Import
(
StoredProcedureName,
StatisticsLineID
)
SELECT StoredProcedureName,
StatisticLineID
FROM StatisticLine

SELECT @RowID = MAX(RowID)
FROM @Import

WHILE @RowID >= 1
BEGIN
SELECT @SQL = 'EXEC ' + StoredProcedureName + ' ' + QUOTENAME(StatisticsLineID, '''')
FROM @Import
WHERE RowID = @RowID

PRINT @SQL

SET @RowID = @RowID - 1
END[/code]

E 12°55'05.25"
N 56°04'39.16"

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-23 : 04:56:30

It does not work!!! Wat I should return are the results from the stored procedures whose names are stored in the table statisticline..Not retrievin' rows from statisticLine table.. Plz help
To be noted that in the stored procedures, I have pass parameters which may not be the same in all stored procedures.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 05:10:15

quote:
Originally posted by R@bb1t

It does not work!!! Wat I should return are the results from the stored procedures whose names are stored in the table statisticline..Not retrievin' rows from statisticLine table.. Plz help
To be noted that in the stored procedures, I have pass parameters which may not be the same in all stored procedures.

Can you provide me with table structure containing SP names? Also where does the parameter value for these SP's be coming from?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 06:51:56

quote:
Originally posted by R@bb1t

It does not work!!!

Yes it will work if you replace the PRINT @SQL statement with an EXEC (@SQL) statement!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 06:57:02

[code]ALTER PROCEDURE dbo.StatisticsLineTest
(
@ProcedureName VARCHAR(200)
)
AS

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000),
@RowID INT

DECLARE @Import TABLE
(
RowID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL,
StoredProcedureName NVARCHAR(200) NOT NULL,
StatisticsLineID NVARCHAR(50) NOT NULL
)

INSERT @Import
(
StoredProcedureName,
StatisticsLineID
)
SELECT StoredProcedureName,
StatisticLineID
FROM StatisticLine
WHERE StoredProcedureName = @ProcedureName
AND StatisticsLineID IS NOT NULL

SELECT @RowID = MAX(RowID)
FROM @Import

WHILE @RowID >= 1
BEGIN
SELECT @SQL = 'EXEC ' + StoredProcedureName + ' ' + QUOTENAME(StatisticsLineID, '''')
FROM @Import
WHERE RowID = @RowID

PRINT @SQL
EXEC (@SQL)

SET @RowID = @RowID - 1
END[/code]

E 12°55'05.25"
N 56°04'39.16"

narayananbabun
Starting Member

3 Posts

Posted - 2008-01-23 : 07:23:23

there is another way u can create a cursor for the field for which u have to call the stored procedure then it will work fine

code

create cursor for select filedname from tablename
open cursor
while @@fetch_status=0
begin
exec sp
..
..
statements

end

narayananbabun

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-23 : 23:57:13

Here is the table structure for statisticLine
StatisticLineId UniqueIdentifier
Name nvarchar(50)
Description nvarchar(255)
StoredProcedureName nvarchar(200)
CreationDate datetime
ModificationDate datetime

e.g of one storedprocedurename : Stats_UserMainProfileSelectWomenUnder40

for execution of Stats_UserMainProfileSelectWomenUnder40 and 2 parameters year and month
exec Stats_UserMainProfileSelectWomenUnder40 '2007','7'

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-24 : 00:24:22

I should save the results from the stored procedure Stats_UserMainProfileSelectWomenUnder40 in the temp table... So, wat should I do?

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-24 : 02:46:43

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[StatisticsLineProc]
(
@Year INT,
@Month INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000),
@Row INT,
@StoredProcedureName NVARCHAR(200)

WHILE @Row > 0
BEGIN
SELECT @StoredProcedureName = StoredProcedureName
FROM StatisticLine

SELECT @SQL = 'EXEC ' + @StoredProcedureName + ' ''' + CAST(@year as varchar(4)) + ''', ''' + CAST(@Month as varchar(2))+ ''' '

SET @Row = (SELECT @@ROWCOUNT FROM StatisticLine) - 1
END
INSERT INTO Results EXECUTE sp_executesql @SQL
END

The above stored procedure works... I should now count the number of rows in table StatisticLine and then insert into the table Results. How should I do this??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-24 : 02:48:47

EXEC ('INSERT #MyIntermediateTable EXEC MyStoredProcedure MyParameter')

E 12°55'05.25"
N 56°04'39.16"

R@bb1t
Starting Member

28 Posts

Posted - 2008-01-24 : 03:30:04

How do I count the number of rows in table StatisticLine.. I have been able to insert into temp table

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-24 : 03:40:21

It seems you are making mockery of all of guys helping you. You have asked same question here.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96072 and myself(obscure answer) and visakh16 has provided you to the point the answer (Posted - 01/24/2008 : 02:48:59) . What are you trying to achieve by all this?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources