Solved

Call stored procedure from a stored procedure

Posted on 2007-04-04
14
252 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
  • 8
  • 6
14 Comments
 
LVL 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18848947
that is ORACLE syntax, which is NOT valid in MS SQL Server.
please specify
0
 
LVL 142

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

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 142

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 142

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 142

Expert Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

21 Experts available now in Live!

Get 1:1 Help Now