Solved

Call stored procedure from a stored procedure

Posted on 2007-04-04
14
251 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 88
Need Counts 11 42
MS SQL 2014 get SPIDs of users 6 26
separate column 24 20
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

757 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

19 Experts available now in Live!

Get 1:1 Help Now