March 12, 2009 at 10:04 am
Hi Guys
Every now and then I come up with some stupid or crazy idea that may help me. Today I thought if its possible to write a single stored procedure that could create stored procedures within SQL? So, say I have a list of 10 clients, each client must be loaded into their own procedure (the idea is to get the procs to run concurrently). I declare a while loop and loop through all the client with the create procedure in there(which I cant get to work, by the way.) Each procedure would be named differently (I was thinking of a guid) then when the procedure completes I have an if exists in there that will drop it. (That part work just fine.)
Is it possible to create a stored procedure within a stored procedure?
How do I call the stored proc I created, but let the while loop continue while its running? (If that makes any sense..)
Thanx.
March 12, 2009 at 11:37 am
Hi Mark
Today I thought if its possible to write a single stored procedure that could create stored procedures within SQL?
Sure. Use sp_executesql
So, say I have a list of 10 clients, each client must be loaded into their own procedure
Why this? Use only one procedure and pass the client-id (or something else) to it.
(the idea is to get the procs to run concurrently).
This is no problem one procedure can run as often as you want at same time.
Each procedure would be named differently (I was thinking of a guid) then when the procedure completes I have an if exists in there that will drop it. (That part work just fine.)
Don't need to. You can use a temporary procedure (like #usp_myproc).
How do I call the stored proc I created, but let the while loop continue while its running?
For dynamic call:
DECLARE @sql NVARCHAR(100)
SET @sql = "EXECUTE usp_myproc ''Hello World'''
EXECUTE sp_executesql @sql
Greets
Flo
March 12, 2009 at 11:50 am
Hi Flo,
Thanx for the reply.
Let me give a lttle background.
We have a bulk dialing solution housed on a remote server, we have groups of account segemented in to what we refer to as clients. I load the accounts to the remote server using a while loop, the drawback is that essentially the clients are loaded into a list for the loop then wait until its their turn to be processed. But if i were to dyanmically create a stored procedure for each client and let them run at the same time, no one waits in line for their turn to come up. The actual "CREATE PROCEDURE" statement within another procedure iswhere im having trouble.
Thanx so far.
March 12, 2009 at 12:08 pm
Hi Mark
I still do not completely understand why the dynamic procedures.. :hehe:
But here an example for your requirement (try on a AdventureWorks)
[font="Courier New"]
-- The "procedure creator" procedure
IF (OBJECT_ID('usp_proc_creator') IS NOT NULL)
DROP PROCEDURE usp_proc_creator
GO
CREATE PROCEDURE usp_proc_creator
@proc_name SYSNAME
AS
DECLARE @sql NVARCHAR(MAX)
-- Create a procedure to select all persons like a specified last name
SET @sql = 'CREATE PROCEDURE ' + @proc_name + '
@last_name NVARCHAR(128)
AS
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @last_name'
EXECUTE sp_executesql @sql
GO
DECLARE @proc_name SYSNAME
-- Define the name of the new procedure
SET @proc_name = 'usp_get_persons_by_last_name'
IF (OBJECT_ID(@proc_name) IS NOT NULL)
EXECUTE ('DROP PROCEDURE ' + @proc_name)
-- Create the new procedure
EXECUTE usp_proc_creator @proc_name
-- Build a dynamic execution for the new procedure
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'EXECUTE ' + @proc_name + ' @last_name_IN'
-- Execute the procedure dynamically with parameter
EXECUTE sp_executesql @sql, N'@last_name_IN NVARCHAR(128)', @last_name_IN = N'Ab%'
[/font]
Greets
March 13, 2009 at 8:11 am
Aaaaaahhh... I see. Sorry for the confusion, it was mostly me.
Thanx for the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply