Solved

Dynamic parameters from a table

Posted on 2003-10-30
16
402 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:isrsa
  • 10
  • 6
16 Comments
 

Author Comment

by:isrsa
ID: 9651792
I have only 40 points, but I will increase it tomorrow when I get more.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9655267
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
0
 

Author Comment

by:isrsa
ID: 9657959
What should be in mycommand.bat, or is it just a sample name, and I can run myprogram.exe instead?
0
 

Author Comment

by:isrsa
ID: 9658084
Sorry, I got it.
I should echo the command to a batch file. Let me try it.
0
 

Author Comment

by:isrsa
ID: 9658168
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!
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 100 total points
ID: 9658885
Declare @cmd varchar(100), @Prog varchar(50), @outer varchar(50)
Set @Prog = 'notepad '
Set @outer = '''' +  @Parm + ''''
SET @cmd='echo '+ @prog +  '  ' +  @outer  ' >c:\mycmd.bat'
EXEC master..xp_cmdshell @cmd
EXEC master..xp_cmdshell 'c:\mycmd.bat'

Ultimately you want to output the actual command into c:\mycmd.bat file. The command line may or may not have args.

Example

MyProgram.exe Parm1, Param2 , etc

HTH



0
 

Author Comment

by:isrsa
ID: 9659124
It works. I will accept your answer later.
Thank you very very much.
0
 

Author Comment

by:isrsa
ID: 9681023
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'.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9684054
Missing a plus sign

SET @cmd='echo '+ @prog +  '  ' +  @outer +  ' >c:\mycmd.bat'

HTH

Namasi
0
 

Author Comment

by:isrsa
ID: 9687025
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!
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9687190


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
0
 

Author Comment

by:isrsa
ID: 9687319
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)?
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9691268
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.
0
 

Author Comment

by:isrsa
ID: 9694433
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.
0
 

Author Comment

by:isrsa
ID: 9694499
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.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9695307
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now