isrsa
asked on
Dynamic parameters from a table
try to dynamically get variable NAMES from a table (not the values of the variables, but rather their names), and execute a program with the variables e.g. exec my_sp with a parameter from a table (e.g. @JobID). The actual value of the variable is in the program, and only the name of the variable is taken from a table, but instead of passing the variable's value, the sp passes the variable's name. Is it possible somehow?
Thank you!
Here is the ocde:
Values in table ProgParams:
ProgID (int) ,Parameter (varchar(50))
-----------------------
1 @JobID
2 @FileName
3 @FilePath
Code
...
SET @JobID=1
SET @Program = 'myprogram.exe'
DECLARE my_cursor CUROSR FOR
SELECT Parameter FROM ProgParams WHERE ProgID=@progID
...
FETCH NEXT FROM my_cursor INTO @Param
SET @FullCommand = '@Program'+' '+RTRIM(@Param)
EXEC master..xp_cmdshell @FullCommand
Thank you!
Here is the ocde:
Values in table ProgParams:
ProgID (int) ,Parameter (varchar(50))
-----------------------
1 @JobID
2 @FileName
3 @FilePath
Code
...
SET @JobID=1
SET @Program = 'myprogram.exe'
DECLARE my_cursor CUROSR FOR
SELECT Parameter FROM ProgParams WHERE ProgID=@progID
...
FETCH NEXT FROM my_cursor INTO @Param
SET @FullCommand = '@Program'+' '+RTRIM(@Param)
EXEC master..xp_cmdshell @FullCommand
Yes. This should work.
If command below did not work
EXEC master..xp_cmdshell @FullCommand
Then Try
SET @FullCommand = '@Program'+' '+RTRIM(@Param)
SET @cmd = 'echo ' + @FullCommand + ' > c:\mycommand.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycommand.bat'
HTH
Namasi Navaretnam
If command below did not work
EXEC master..xp_cmdshell @FullCommand
Then Try
SET @FullCommand = '@Program'+' '+RTRIM(@Param)
SET @cmd = 'echo ' + @FullCommand + ' > c:\mycommand.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycommand.bat'
HTH
Namasi Navaretnam
ASKER
What should be in mycommand.bat, or is it just a sample name, and I can run myprogram.exe instead?
ASKER
Sorry, I got it.
I should echo the command to a batch file. Let me try it.
I should echo the command to a batch file. Let me try it.
ASKER
Well, the problem still occurs.
My problem is not to run a program but to pass the variable value, and not its name.
E.g.
Declare @cmd varchar(100), @Prog varchar(50), @outer varchar(50)
Set @Prog = 'notepad '
Set @outer = '@Prog'
SET @cmd='echo '+@outer+' >c:\mycmd.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycmd.bat'
I want it to execute notepad, and I have the variable name (@Prog) only as a varchar(50) (because I retrieve it from a table.).
Thank you so much!
My problem is not to run a program but to pass the variable value, and not its name.
E.g.
Declare @cmd varchar(100), @Prog varchar(50), @outer varchar(50)
Set @Prog = 'notepad '
Set @outer = '@Prog'
SET @cmd='echo '+@outer+' >c:\mycmd.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycmd.bat'
I want it to execute notepad, and I have the variable name (@Prog) only as a varchar(50) (because I retrieve it from a table.).
Thank you so much!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works. I will accept your answer later.
Thank you very very much.
Thank you very very much.
ASKER
I had a mistake. When running the following code:
Declare @cmd varchar(100), @Prog varchar(50), @outer varchar(50), @Param varchar(50), @Id varchar(20)
set @Id='A3499'
set @Param = '@id'
Set @Prog = 'notepad '
Set @outer = '''' + @Param + ''''
SET @cmd='echo '+ @prog + ' ' + @outer ' >c:\mycmd.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycmd.bat'
I still get the error:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ' >c:\mycmd.bat'.
Declare @cmd varchar(100), @Prog varchar(50), @outer varchar(50), @Param varchar(50), @Id varchar(20)
set @Id='A3499'
set @Param = '@id'
Set @Prog = 'notepad '
Set @outer = '''' + @Param + ''''
SET @cmd='echo '+ @prog + ' ' + @outer ' >c:\mycmd.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycmd.bat'
I still get the error:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ' >c:\mycmd.bat'.
Missing a plus sign
SET @cmd='echo '+ @prog + ' ' + @outer + ' >c:\mycmd.bat'
HTH
Namasi
SET @cmd='echo '+ @prog + ' ' + @outer + ' >c:\mycmd.bat'
HTH
Namasi
ASKER
I'm back to the first problem. It sends to the batch file the command 'notepad '@id'', instead of 'notepad A3499' as I want.
Thank you so much!
Thank you so much!
SET @cmd='echo '+ @prog + ' ' + @outer + ' >c:\mycmd.bat'
In the command above make sure @prog is set as notepad and @outer is set as 'A3499'
HTH
ASKER
That's the whole point. I get the variable NAME from a table, not its value. From my point of view @outer doesn't and cannot know what value @id holds because @id comes as a varchar from a table (as: '@id').
Is there any way that the code I posted on:11/04/2003 11:13AM PST will send the batch file the VALUE that @id holds (having only '@id' as a varchar)?
Is there any way that the code I posted on:11/04/2003 11:13AM PST will send the batch file the VALUE that @id holds (having only '@id' as a varchar)?
Is is possible to get a value if @id using dynamic sql?
create table #temp (col1 varchar(100))
execute sp_executesql
N'insert #temp select max(emp_id) from pubs.dbo.employee'
select col1 from #temp
drop table #temp
There are few examples in the help file.
create table #temp (col1 varchar(100))
execute sp_executesql
N'insert #temp select max(emp_id) from pubs.dbo.employee'
select col1 from #temp
drop table #temp
There are few examples in the help file.
ASKER
First, thank you so much for your replies. I really appreciate it.
Second, let me explain. The reason I am trying to achieve this is because the program I am writing is a scheduler that will run various programs that might need parameters.
The scheduler has all the possible parameters that those programs might need (well, almost everything), but it does not know which program needs what parameters.
Because the programs that the scheduler runs are replaceable (new versions, etc.), I would like to have dynamic parameters, which means that if I decide to replace a program that takes emp_id, job_id, and file_name with one that takes only emp_id, and file_name, I should not have to change the code, but rather only the parameters names in a table (as: prog_id-1 parameter-emp_id, task_id-1 parameter-job_id, task_id-2 parameter-file_name, etc.).
Of course, I can "hard code" the parameters directly in my code (“if @prog_id=1 set @parameters=emp_id+' '+job_id”), but I am trying to achieve a more dynamic program.
I hope that I was clear enough.
Thank you very much.
Second, let me explain. The reason I am trying to achieve this is because the program I am writing is a scheduler that will run various programs that might need parameters.
The scheduler has all the possible parameters that those programs might need (well, almost everything), but it does not know which program needs what parameters.
Because the programs that the scheduler runs are replaceable (new versions, etc.), I would like to have dynamic parameters, which means that if I decide to replace a program that takes emp_id, job_id, and file_name with one that takes only emp_id, and file_name, I should not have to change the code, but rather only the parameters names in a table (as: prog_id-1 parameter-emp_id, task_id-1 parameter-job_id, task_id-2 parameter-file_name, etc.).
Of course, I can "hard code" the parameters directly in my code (“if @prog_id=1 set @parameters=emp_id+' '+job_id”), but I am trying to achieve a more dynamic program.
I hope that I was clear enough.
Thank you very much.
ASKER
I was not clear about the table. I mean something like this:
prog_id (int), parameter (varchar(50))
--------------------------
1, '@Job_ID'
1, '@Emp_ID'
2, '@File_Name'
2, '@In_Date'
2, '@Out_Date'
3, 'c:\workdir'
3, '@Rec_No'
3, 'd:\workdir\web_in'
Now in the program I want to select:
'SELECT parameter FROM progs WHERE prog_id=@prog_id'
into a cursor or a temporary table, then loop through the results, and run each program with its parameters.
prog_id (int), parameter (varchar(50))
--------------------------
1, '@Job_ID'
1, '@Emp_ID'
2, '@File_Name'
2, '@In_Date'
2, '@Out_Date'
3, 'c:\workdir'
3, '@Rec_No'
3, 'd:\workdir\web_in'
Now in the program I want to select:
'SELECT parameter FROM progs WHERE prog_id=@prog_id'
into a cursor or a temporary table, then loop through the results, and run each program with its parameters.
DECLARE @v_paralist varchar(8000),
@v_paravaluelist varchar(8000),
@v_prog_id varchar(30),
@v_parameter varchar(100),
@v_paravalue varchar(100)
DECLARE table_cursor CURSOR FOR
SELECT parameter FROM progs WHERE prog_id=@v_prog_id
select @v_paralist = ' ', @v_paravaluelist = ' '
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @v_paramter
WHILE @@FETCH_STATUS = 0
BEGIN
select @v_paralist = @v_paralist + @v_parameter + ', '
/*
get the value of parameter using sp_execute_sql
See example in the help file.
*/
select @v_paravaluelist = @v_paravaluelist + @v_paravalue + ', '
FETCH NEXT FROM table_cursor
INTO @v_parameter
END
select @v_paralist = Left(@v_paralist, len(@v_paralist) -1)
select @v_paravaluelist = Left(@v_paravaluelist, len(@v_paravaluelist) -1)
CLOSE table_cursor
DEALLOCATE table_cursor
HTH
@v_paravaluelist varchar(8000),
@v_prog_id varchar(30),
@v_parameter varchar(100),
@v_paravalue varchar(100)
DECLARE table_cursor CURSOR FOR
SELECT parameter FROM progs WHERE prog_id=@v_prog_id
select @v_paralist = ' ', @v_paravaluelist = ' '
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @v_paramter
WHILE @@FETCH_STATUS = 0
BEGIN
select @v_paralist = @v_paralist + @v_parameter + ', '
/*
get the value of parameter using sp_execute_sql
See example in the help file.
*/
select @v_paravaluelist = @v_paravaluelist + @v_paravalue + ', '
FETCH NEXT FROM table_cursor
INTO @v_parameter
END
select @v_paralist = Left(@v_paralist, len(@v_paralist) -1)
select @v_paravaluelist = Left(@v_paravaluelist, len(@v_paravaluelist) -1)
CLOSE table_cursor
DEALLOCATE table_cursor
HTH
ASKER