February 18, 2004 at 9:11 pm
Hi,
There's a procedure that requires a parameter (@Param1). That procedure takes quite some time to be executed. And we want a user to be able to execute it via ASP. So we created a job to execute that job... and another to start the job. Can I pass a parameter to the job?
February 20, 2004 at 6:02 pm
I don't think that jobs (SQL Server Agent jobs, i presume) take parms. (If I'm wrong, someone slap me silly and let me know...)
Here's how a user (via your app and a web connection) could change parms and start a job (named "my_job") to run a procedure (name "my_sp")...
exec sp_start_job 'my_job'
This technique starts the (long running) job asynchronously, and returns control to your application and user while the job is running. The user could logout or whatever, but the job is still running.
There are any number of variations to this.
Hope this helps ...
TONYMARKS
February 20, 2004 at 6:54 pm
Excellent Idea, Tony. I've starting using parameterless procs for a number of reason's, the main being performance. In fact, I'm working on a generic Parameter table to deal with this. Something like:
CREATE TABLE [Proc] (
[ProcID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [sysname] NOT NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[ProcID]
  ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ProcParam] (
[ProcParamID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [int] NOT NULL ,
[ProcID] [int] NOT NULL ,
[OrdNum] [int] NOT NULL CONSTRAINT [DF__ProcParam__OrdNu__5A054B78] DEFAULT (0),
[Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[ClientID],
[ProcID],
[OrdNum]
  ON [PRIMARY] ,
FOREIGN KEY
(
[ClientID]
  REFERENCES [Client] (
[ClientID]
 ,
FOREIGN KEY
(
[ProcID]
  REFERENCES [Proc] (
[ProcID]
 
) ON [PRIMARY]
GO
I haven't had any time to work on this lately, though, except:
create function getParameter (@ParamterID int)
returns varchar(8000)
as
begin
declare @string varchar(8000)
select @string = isnull(@string + ', ', '') + isnull(Name + ' = ', '') + Value
from Parameter p (nolock)
where ParameterID = @ParamterID
order by OrdNum
select @string = isnull(@string, '')
return @string
end
This would return your parameter string as a csv.
Signature is NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply