Solved

Call stored procedure from a stored procedure

Posted on 2007-04-04
14
256 Views
Last Modified: 2012-08-13
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
Comment
Question by:thomas908
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18848931

EXEC yourprocedurename 'param1', 32 , @parameter
0
 
LVL 8

Author Comment

by:thomas908
ID: 18848935
Is this the right way of doing it

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18848947
that is ORACLE syntax, which is NOT valid in MS SQL Server.
please specify
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18848954
what are the resource_name and document "values" ?
0
 
LVL 8

Author Comment

by:thomas908
ID: 18848955
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18848984
yes.
note howere that I would not name a procedure "update"


   EXECUTE dbo.[update] 'resource_name', 'document'
0
 
LVL 8

Author Comment

by:thomas908
ID: 18849099
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18849118
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
 
LVL 8

Author Comment

by:thomas908
ID: 18849154
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18849172
did you meanwhile try my last suggestion for the procedure?
0
 
LVL 8

Author Comment

by:thomas908
ID: 18849176
>>   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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 18849182
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
 
LVL 8

Author Comment

by:thomas908
ID: 18849300
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18849304
well, it looks correct...
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

737 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