dynamic query inside the cursor

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 2000 Forums

Transact-SQL (2000)

dynamic query inside the cursor

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-01 : 23:28:13

CREATE TABLE [dbo].[tbl_process_info] (
[process_id] [int] IDENTITY (1, 1) NOT NULL ,
[executed_sp_name] [varchar] (100) NOT NULL ,
[sp_parameter] [varchar] (1000) NULL ,

) ON [PRIMARY]
GO

INSERT INTO tbl_process_info
(executed_sp_name,sp_parameter)
SELECT usp_emp_info_i,@emp_number='xxx',@emp_type=2 UNION ALL
SELECT usp_emp_info_i,@emp_number='xxx',@emp_type=3 UNION ALL
SELECT usp_emp_info_u,@emp_number='xxx',@emp_type=2 UNION ALL
SELECT usp_comment_i,@emp_number='xxx'

CREATE TABLE [dbo].[tbl_active_emp_numbers] (
[emp_number] [varchar] (13) NOT NULL ,

) ON [PRIMARY]

INSERT INTO tbl_active_emp_numbers
(emp_number)
SELECT E67890 UNION ALL
SELECT E97898 UNION ALL
SELECT E68892 UNION ALL
SELECT E67896 UNION ALL
SELECT E69990 UNION ALL
SELECT E27790

I want to execute these processes dynamically....

I will have a cursor which selects all the emp_number and passes to each stored procedure in the tbl_process_info
which will be another cursor and then one transaction for the emp_number is completed.

parameter can be passed as 'xxx' should be replaced with the emp_number

I don't know how to write this process...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-01 : 23:39:26

It will be some what like

CURSOR 1
BEGIN TRAN
BEGIN
CURSOR2
END
COMMIT TRAN -- IF sucess ELSE ROLLBACK the current emp_number and move to the next emp_number

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 01:38:35

Please help me out

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-02 : 11:20:59

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

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-02 : 12:46:41

I don't know what your trying to accomplish...but nested cursors?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 12:54:24

Iam trying to execute the all the stored procedures stored in the tbl_process_info table by passing the emp_numbers stored in tbl_active_emp_numbers.Since the emp_number always vary on the fly I replace the xxx to emp_number in my query.So I need to create a dynamic query which does this inside a Cursor where I need to pass emp_number one by one

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 14:04:12

Please help me out

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 19:00:06

I can't figure this out..Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-02 : 19:34:38

You haven't provided enough information. You've posted the DDL for your tables, some sample data, but you haven't told us what the output should look like. You keep saying you need to use a dynamic query inside a cursor, but that's not enough information for us to help. We can't read your mind. Please show us exactly what you want to do with the data.

Tara Kizer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 19:40:25

The cursor should be able to execute each sp specified in the table and the result will be like this

usp_emp_info_i,@emp_number='E67890',@emp_type=2
usp_emp_info_i,@emp_number='E67890',@emp_type=3
usp_emp_info_u,@emp_number='E67890',@emp_type=2
usp_comment_i,@emp_number='E67890'

usp_emp_info_i,@emp_number='E97898',@emp_type=2
usp_emp_info_i,@emp_number='E97898',@emp_type=3
usp_emp_info_u,@emp_number='E97898',@emp_type=2
usp_comment_i,@emp_number='E97898'

usp_emp_info_i,@emp_number='E68892',@emp_type=2
usp_emp_info_i,@emp_number='E68892',@emp_type=3
usp_emp_info_u,@emp_number='E68892',@emp_type=2
usp_comment_i,@emp_number='E68892'

usp_emp_info_i,@emp_number='E67896',@emp_type=2
usp_emp_info_i,@emp_number='E67896',@emp_type=3
usp_emp_info_u,@emp_number='E67896',@emp_type=2
usp_comment_i,@emp_number='E67896'

usp_emp_info_i,@emp_number='E69990',@emp_type=2
usp_emp_info_i,@emp_number='E69990',@emp_type=3
usp_emp_info_u,@emp_number='E69990',@emp_type=2
usp_comment_i,@emp_number='E69990'

usp_emp_info_i,@emp_number='E27790',@emp_type=2
usp_emp_info_i,@emp_number='E27790',@emp_type=3
usp_emp_info_u,@emp_number='E27790',@emp_type=2
usp_comment_i,@emp_number='E27790'

Each emp_number is passed to the complete stored procedures stored in the table and then moves to the next emp_number.

Please let me know if you need more information.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-02 : 19:54:19

Mr Learner (or can I call you sql?),
Do your stored procedures (ie those referenced in the tbl_process_info) return a recordset, or are they just needed to update your db? Assuming you don't have anything returned from the stored procs:

First, you may need to mod your procs table:

INSERT INTO tbl_process_info
(executed_sp_name,sp_parameter)
SELECT 'EXEC usp_emp_info_i @emp_number, 2','@emp_number VARCHAR(13)' UNION ALL
SELECT 'EXEC usp_emp_info_i @emp_number, 2','@emp_number VARCHAR(13) ,@emp_type INT' UNION ALL
SELECT 'EXEC usp_emp_info_u @emp_number, 2','@emp_number VARCHAR(13) ,@emp_type INT' UNION ALL
SELECT 'EXEC usp_comment_i @emp_number','@emp_number VARCHAR(13)'

This will allow the entries to work in the following code:

DECLARE @processID	INT,
	@spName		NVARCHAR(100),
	@param		NVARCHAR(1000),
	@sql		NVARCHAR(100),
	@empNum		VARCHAR(13)

SET @processID = 0

WHILE EXISTS (SELECT NULL FROM tbl_process_info WHERE process_id > @processID) BEGIN
	SELECT TOP 1 @processID = process_id, @spName = executed_sp_name, @param = sp_parameter 
	FROM tbl_process_info WHERE process_id > @processID
	ORDER BY process_id ASC

	SET @empNum = ''
	WHILE EXISTS (SELECT NULL FROM tbl_active_emp_numbers WHERE emp_number > @empNum) BEGIN
		SELECT TOP 1 @empNum = emp_number
		FROM tbl_active_emp_numbers 
		WHERE emp_number > @empNum
		ORDER BY emp_number ASC

		EXEC sp_ExecuteSql @spName, @param, @empNum
	END
END

HTH,

Tim

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 20:17:06

For 2 stored procedures I have an OUTPUT parameter also...
usp_tax_i @emp_number,@tax_id OUTPUT
usp_insurance_i @emp_number,@insurance_id OUTPUT

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-02 : 20:27:03

What do you want to do with that output parameter?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 20:44:28

Thanks a lot for helping me out.......

I need to pass it to the next stored procedure which is

usp_tax_details_i @tax_id INT
usp_insurance_details_i @insurance_id INT

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-02 : 20:53:53

Can't these be called from the existing stored proc? Or you could always create a new stored proc that calls the first stored proc, gets the return values and then calls the secondary stored procs.
Either way, my approach above could be modified to suit. If you want to call the secondary procs from within the loop, you will probably need to add another child table to tbl_process_info to store details of the secondary ones.

Can you explain a bit about the actual process (in layman's terms) you're trying to accomplish? You might find that someone has a completely different approach which is easier.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-02 : 23:05:03

I have to migrate data from one database to another database and I have to execute around 25-30 stored procedures
for this migration and this will be a daily task.So for each emp_numbers in tbl_emp_numbers I need to pass each emp_number
to the 25-30 stored procedures and after these are executed one transaction is completed.Now I need to pass
the next emp_numbers to these stored procedure...likewise it should process all the emp_numbers

Now the challenge is the team needs to do it dynamically.

- A table needs to be created with all the stored procedures executing and with the input and out parameters.
- If we have something like this we can change the sequence in which it needs to execute based on a column called executed_order
- If we create any new stored procedure then it should be inserted in this table.
- The main stored procedure will be executed dynamically based on the executed_order values.

Hope this helps in adding more information.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-02 : 23:44:53

I would personally ditch the idea of storing stored proc names/params and create one big stored proc that gets run for every emp_number.
This can call any stored proc you like, and you have extra control over what happens to the outputs. And if you need to change the order of the stored procs to be called, just update the 'big' stored proc. Is there any reason you might want to alter the running order of the stored procs manually??
Stick to the KISS principle and you can't go too far wrong.

Just my 2c anyway.

Tim

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-03 : 00:27:48

I could have created the main Sp as you said but the problem is the project requirement is to implement as I mentioned though its hard to code and use all the parameters as mentioned in the previous sections.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-08-03 : 01:10:48

Project Managers have a lot to answer for sometimes....

Good luck with it anyway.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources