Solved

Nested INSERT EXEC

Posted on 2004-09-09
22
473 Views
Last Modified: 2008-01-16
Hi,

I have run into an insert exec nesting problem and I would like to find out how to go around this. The scenario of occurrence is as follows: -

Stored procedure A has the following line which executes : spGetALLUserGroupData

INSERT INTO #ALLFacts1 EXECUTE ('spGetALLUserGroupData ''' +  @UserGroupName + ''' ,''Factory''')

The temp table  #ALLFacts1 and the corresponding variables are all set up correctly.

Within the spGetALLUserGroupData stored procedure, the following statement is generating the nesting error: -

INSERT INTO #ALLFacts
EXECUTE        ('SELECT DISTINCT factory_cd, factory_desc
         FROM LINEN_FACTORY, CUSTOMER
       WHERE customer.serv_site = linen_factory.factory_cd
       AND   customer.ngrp_cd IN (SELECT ngrp_cd FROM user_group_group
                  WHERE user_group_id =  ' + @UGrpId + ')')

Any solutions please?
0
Comment
Question by:jbonello
  • 8
  • 7
  • 4
  • +1
22 Comments
 
LVL 10

Accepted Solution

by:
imrancs earned 300 total points
ID: 12014751
first thing, you don't need to have dynamic sql in spGetALLUserGroupData as

INSERT INTO #ALLFacts
EXECUTE       ('SELECT DISTINCT factory_cd, factory_desc
        FROM LINEN_FACTORY, CUSTOMER
      WHERE customer.serv_site = linen_factory.factory_cd
      AND   customer.ngrp_cd IN (SELECT ngrp_cd FROM user_group_group
               WHERE user_group_id =  ' + @UGrpId + ')')

you simply could do this
as

INSERT INTO #ALLFacts
SELECT DISTINCT factory_cd, factory_desc
        FROM LINEN_FACTORY, CUSTOMER
      WHERE customer.serv_site = linen_factory.factory_cd
      AND   customer.ngrp_cd IN (SELECT ngrp_cd FROM user_group_group
               WHERE user_group_id =  ' + @UGrpId + ')


Please post the compete code of spGetALLUserGroupData.


Imran
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015097
imrancs: you surely meant it this way:

INSERT INTO #ALLFacts
SELECT DISTINCT factory_cd, factory_desc
        FROM LINEN_FACTORY, CUSTOMER
      WHERE customer.serv_site = linen_factory.factory_cd
      AND   customer.ngrp_cd IN (SELECT ngrp_cd FROM user_group_group
               WHERE user_group_id =  @UGrpId

CHeers
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12015107
agree 100% with imran,   you dont need DSQL here.
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.

 

Author Comment

by:jbonello
ID: 12015879
OK thanks. But what if I can't do without DSQL?

I am facing this problem in the same stored procedure where I'm dynamically building a WHERE clause.

Is there a generic solution?

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12015916
angelIII,

yea
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015992
Does this change anything:
INSERT INTO #ALLFacts1
EXEC spGetALLUserGroupData @UserGroupName , 'Factory'

CHeers
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12016011
please post the complete code of spGetALLUserGroupData


imran
0
 

Author Comment

by:jbonello
ID: 12016131
The proc is quite longish so I'm copying the section which is generating the error: -
(let me know if you need more info)

IF (@Type = 'Group')
BEGIN
      SET @Where =''

      IF @countFac > 0
      BEGIN
            SET @Where = @Where + ' AND serv_site IN (SELECT factory_cd from user_group_factory  WHERE user_group_id = ' + CAST(@UGrpId as varchar) + ')'
      END

      IF @countGroup > 0
      BEGIN
            SET @Where = @Where + ' AND c.ngrp_cd IN (SELECT ngrp_cd from user_group_group  WHERE user_group_id = ' + CAST(@UGrpId as varchar) + ')'
      END

      IF @countAcc > 0
      BEGIN
            SET @Where = @Where + ' AND c.cust_no IN (SELECT cust_no from user_group_account  WHERE user_group_id = ' + CAST(@UGrpId as varchar) + ')'
      END


      IF @countFac > 0 OR @countGroup > 0 OR @countAcc > 0
      BEGIN
            INSERT INTO #ALLGrps
            EXECUTE       ('      SELECT DISTINCT c.ngrp_cd, n.ngrp_name
                              FROM national_group n, customer c WHERE c.ngrp_cd = n.ngrp_cd ' + @Where )

            INSERT INTO #ALLGrps
            EXECUTE       ('      SELECT DISTINCT c.ngrp_cd, n.ngrp_name
                              FROM ww_customer c, ww_national_group n WHERE c.ngrp_cd = n.ngrp_cd ' + @Where )
      END

      IF (SELECT count(*) FROM #ALLGrps) > 0
      BEGIN
            SELECT DISTINCT ngrp_cd, ISNULL(ngrp_name,'')
            FROM      #ALLGrps
            ORDER BY ngrp_cd
      END
      
      ELSE
      BEGIN
            SELECT '', description
            FROM      error_list
            WHERE      errorid = 1
      END

END
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12016274
just execute this in Query Analyzer and see what it produce

declare @UserGroupName Varchar(100)
Set @UserGroupName  = '<UserGroup>' --<<change here

Exec spGetALLUserGroupData,  @UserGroupName + ,''Factory'


Imran

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12016312
and also change DSQL (as i told above)

   INSERT INTO #ALLGrps
          EXECUTE      ('     SELECT DISTINCT c.ngrp_cd, n.ngrp_name
                         FROM national_group n, customer c WHERE c.ngrp_cd = n.ngrp_cd ' + @Where )

          INSERT INTO #ALLGrps
          EXECUTE      ('     SELECT DISTINCT c.ngrp_cd, n.ngrp_name
                         FROM ww_customer c, ww_national_group n WHERE c.ngrp_cd = n.ngrp_cd ' + @Where )


to

   INSERT INTO #ALLGrps
   SELECT DISTINCT c.ngrp_cd, n.ngrp_name
   FROM national_group n, customer c WHERE c.ngrp_cd = n.ngrp_cd  + @Where

   INSERT INTO #ALLGrps
   SELECT DISTINCT c.ngrp_cd, n.ngrp_name
   FROM ww_customer c, ww_national_group n WHERE c.ngrp_cd = n.ngrp_cd  + @Where
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12016330
Oops typo,


declare @UserGroupName Varchar(100)
Set @UserGroupName  = '<UserGroup>' --<<change here

Exec spGetALLUserGroupData,  @UserGroupName ,'Factory'
0
 

Author Comment

by:jbonello
ID: 12016833
Thanks imrancs.

I have removed all the EXEC statements and it worked.

However, I must ask when is it appropriate to use EXEC statements since it is clearly better to use them ONLY when absolutely necessary in order to avoid future complications? Is it to be used just for executing other sp's?

Cheers
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12016919
EXEC must be used to execute to SPs, and it should be avoided to execute simple queries that can be run simply as above.
You can use Exec to execute dynamic sql when there is option to use simple sql.

Imran
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12016970
Pls dont allocate points to me for this comment as imran has done all the work but....

Dynamic SQL is best avoided.   You should only use it when absolutely necesary  in 99% of cases where if seen people use it its been inappropriate.   "generally" ( and the are exceptions) the only appropriate use of dsql is when you need to make parts of sqls syntax variable but it doesnt support it:   eg:

SELECT * FROM @table

this would need to be :

EXEC ('SELECT * FROM ' + @Table)   to work correctly

but even though in this case only using dynamic sql works,  often this case indicates a poor database design.

A pretty important thing is security on DSQL  lets say you have a stored proc that fred can execute, by fred doesnt have read permissions on the underlying tables that the stored proc uses (lets say table1)  

CREATE PROCEDURE p AS
SELECT * FROM Table1

would execute ok for fred

but

CREATE PROCEDURE p AS
EXEC ('SELECT * FROM Table1')

would not work because fred doesnt have permissions on table1 and dynamic sql runs within its own context.


0
 

Author Comment

by:jbonello
ID: 12024160
Thanks for your interesting contribution ShogunWade.

0
 

Author Comment

by:jbonello
ID: 12024788
One other question.....

I have just found out that the following statement is not returning any results: -

INSERT INTO #ALLGrps
SELECT DISTINCT c.ngrp_cd, n.ngrp_name
FROM national_group n, customer c WHERE c.ngrp_cd = n.ngrp_cd  + @Where

When I hard code the contents of the @Where variable: -
' AND c.ngrp_cd IN (SELECT ngrp_cd from user_group_group  WHERE user_group_id = ' + CAST(@UGrpId as varchar) + ')'

I have 2 rows returned which is correct.
I have came across this same problem in another scenario so I'm pretty sure that the @Where variable in the WHERE clause is causing this problem.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12024817
This line:
FROM national_group n, customer c WHERE c.ngrp_cd = n.ngrp_cd  + @Where
is actually evaluated the same as this:
FROM national_group n, customer c WHERE c.ngrp_cd = (n.ngrp_cd  + @Where)

As you can now see, the VALUE of the variable @WHERE is appended to n.ngrp_cd

This should work:
DECLARE @SQL VARCHAR(8000)
SET @SQL =  'SELECT DISTINCT c.ngrp_cd, n.ngrp_name
FROM national_group n, customer c WHERE c.ngrp_cd = n.ngrp_cd ' + @Where
INSERT INTO #ALLGrps
EXECUTE ( @SQL )

CHeers
0
 

Author Comment

by:jbonello
ID: 12024856
Thanks AngelIII,

But doesn't that take me back to my original nested exec problem?

0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12024910
"Thanks for your interesting contribution ShogunWade." your welcome.  hope it made it a bit clearer.
0
 

Author Comment

by:jbonello
ID: 12024954
Any further contributions imrancs?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12024966
Yes, indeed. I have similar problems also, and start "hating" the limitations of SQL, life could be so easy without them... I even start thinking about writing my own database, but ok, that would be a life-time job, so i continue to live with it.

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12025438
>>Yes, indeed. I have similar problems also, and start "hating" the limitations of SQL, life could be so easy without them...
Its only possible after the end of life... ;o)

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

806 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