Call stored procedure from a stored procedure

EXEC yourprocedurename 'param1', 32 , @parameter

Is this the right way of doing it

    Var_SQL :='call dbo.update ('''||resource_name||''','''||document||''')' ;
   EXECUTE IMMEDIATE Var_SQL;

that is ORACLE syntax, which is NOT valid in MS SQL Server.
please specify

what are the resource_name and document "values" ?

Does this mean that 2 parameters will be passed

   EXECUTE dbo.update 'resource_name', 'document'

first with the value resource_name and second with the value document

yes.
note howere that I would not name a procedure "update"


   EXECUTE dbo.[update] 'resource_name', 'document'

I am trying to execute the stored procedure, by calling it from another procedure (which is called from JDBC) but I get this error

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Pro
cedure or function update_customTables has too many arguments specified.
        at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source
)
        at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
        at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
 Source)
        at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown

My proc looks something like this

CREATE PROCEDURE dbo.update_customTables
@targetTable Varchar(40),
@sourceTable Varchar(40)
 AS
CREATE TABLE #tempInsert
(ID INT Identity(1,1),
 Name Varchar(100)
)

   EXEC ( ' DELETE from @targetTable')
   TRUNCATE TABLE #tempInsert
   EXEC ( ' INSERT INTO #tempInsert(Name) SELECT DISTINCT @targetTable FROM @sourceTable WHERE @targetTable IS NOT NULL AND @targetTable !="" ')  
   EXEC ( 'INSERT INTO ' + @targetTable + '(' + @targetTable + '_id, ' + @targetTable + '_name )  SELECT ID, [Name] FROM #tempInsert ')
GO

how do you actaully call that procedure? the error message indicates to me that not the procedure is the problem, but how you call it..

anyhow, your pcedure needs also some corrections, and could actually be reduced to 2 statements:

CREATE PROCEDURE dbo.update_customTables
@targetTable Varchar(40),
@sourceTable Varchar(40)
 AS
   if @targettable is null  return
   if @targettable = '' return

   if @sourcetable is null  return
   if @sroucetable = '' return

   EXEC ( 'DELETE FROM ' + @targetTable )
   EXEC ( 'INSERT INTO ' + @targetTable + '(' + @targetTable + '_id, ' + @targetTable + '_name )  SELECT SELECT DISTINCT ' + @targetTable + ' FROM ' + @sourceTable + ' ')  
GO

This is how I am calling it

IF @arg1='res_name' or @arg2='res_name' or @arg3='res_name' or @arg4='res_name' or @arg5='res_name' or @arg6='res_name' or @arg7='res_name' or @arg8='res_name' or @arg9='res_name' or @arg10='res_name' or @arg11='res_name'
BEGIN

   EXECUTE dbo.update_customTables 'res_name', 'document'

END

IF @arg1='application' or @arg2='application' or @arg3='application' or @arg4='application' or @arg5='application' or @arg6='application' or @arg7='application' or @arg8='application' or @arg9='application' or @arg10='application' or @arg11='application'
BEGIN
   EXECUTE dbo.update_customTables 'application', 'document_r'
END

and so on...

did you meanwhile try my last suggestion for the procedure?

>>   if @targettable is null  return
>>   if @targettable = '' return

>>   if @sourcetable is null  return
>>   if @sroucetable = '' return

actually I am trying to do something else here. The code you have posted checks whether the parameters passed are null or not but in

   EXEC ( ' INSERT INTO #tempInsert(Name) SELECT DISTINCT @targetTable FROM @sourceTable WHERE @targetTable IS NOT NULL AND @targetTable !="" ')  

@targetTable is the column name as I am doing
SELECT DISTINCT @targetTable
I want the value which is selected by the select statement should not be null or blank since the table to which it is inserted does not support null values.
So the value of @targetTable is the name of the table to which values need to be inserted, its also the name of the column of the source table from which values needs to be picked.
But I think you have correctly pointed out the mistake, since doing
WHERE @targetTable IS NOT NULL AND @targetTable !="" '
will check for the value of the parameter and not the value fetched from column. Please tell me how to correct this.

This is how I am calling it

This is how I am calling it

IF @arg1='res_name' or @arg2='res_name' or @arg3='res_name' or @arg4='res_name' or @arg5='res_name' or @arg6='res_name' or @arg7='res_name' or @arg8='res_name' or @arg9='res_name' or @arg10='res_name' or @arg11='res_name'
BEGIN

   EXECUTE dbo.update_customTables 'res_name', 'document'

END

IF @arg1='application' or @arg2='application' or @arg3='application' or @arg4='application' or @arg5='application' or @arg6='application' or @arg7='application' or @arg8='application' or @arg9='application' or @arg10='application' or @arg11='application'
BEGIN
   EXECUTE dbo.update_customTables 'application', 'document_r'
END

and so on...

Is it incorrect?

well, it looks correct...