sql server programming: Hi,
Is there a way to pass parameters to a SQL job?

Thanks
Saps

There may be other ways, but I use stored procedures to manage
parameters. They can call other SPs and return parameters as well.

if your using SQL Server Management Studio by right clicking on a
database/programmability/Stored Procedures you'll see "New Stored
Procedure."

when you click it you'll get something like the following template.
Note that parts between <> you'll need to replace with a procedure
name and parameters.


CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END


Hope this helps.

rb



[quoted text, click to view]

Saps,

The sp_start_job stored procedure has no job parameters. There are a few
SQL Agent Tokens (in the BOL under "Using Tokens in Job Steps") but these
are probably not what you are after.

I would create a table for the job in which the needed parameters could be
stored. The job could then read those rows and use the data as parameters.
E.g.

CREATE TABLE JobParameters
(JobName nvarchar(128),
ParmName nvarchar(128),
ParmValue nvarchar(128))

Of course, ParmValue is character in this example, so you would need to
populate it by converting integers, datetimes, etc. into nvarchar. (Or you
could use a sql_variant.)

RLF
[quoted text, click to view]

[quoted text, click to view]

Ah, yes, I completely missed the intent of the original question.
Thanks for this.
rb

Hey Russell,

This is exactly what i was thinking....
before implementing i thought let me see if some one has done it....
Thanks a lot ...


Thanks
saps