• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Call stored procedure from a stored procedure

How can I call a stored procedure from another stored procedure (SQL Server 2000). I also need to pass parameters while calling the stored procedure.
0
thomas908
Asked:
thomas908
  • 8
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

EXEC yourprocedurename 'param1', 32 , @parameter
0
 
thomas908Author Commented:
Is this the right way of doing it

    Var_SQL :='call dbo.update ('''||resource_name||''','''||document||''')' ;
   EXECUTE IMMEDIATE Var_SQL;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that is ORACLE syntax, which is NOT valid in MS SQL Server.
please specify
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what are the resource_name and document "values" ?
0
 
thomas908Author Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes.
note howere that I would not name a procedure "update"


   EXECUTE dbo.[update] 'resource_name', 'document'
0
 
thomas908Author Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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








0
 
thomas908Author Commented:
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...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you meanwhile try my last suggestion for the procedure?
0
 
thomas908Author Commented:
>>   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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see. check out this:

CREATE PROCEDURE dbo.update_customTables
@targetTable Varchar(40),
@sourceTable Varchar(40)
 AS

   EXEC ( 'DELETE FROM ' + @targetTable )
   EXEC ( 'INSERT INTO ' + @targetTable + '(' + @targetTable + '_id, ' + @targetTable + '_name )  SELECT SELECT DISTINCT ' + @targetTable + ' FROM ' + @sourceTable + ' WHERE ' + @targetTable + ' IS NOT NULL AND ' + @targetTable + ' <> ''''  ')  
GO
0
 
thomas908Author Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, it looks correct...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now