Nested INSERT EXEC

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?
jbonelloAsked:
Who is Participating?
 
imrancsConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ShogunWadeCommented:
agree 100% with imran,   you dont need DSQL here.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jbonelloAuthor Commented:
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
 
imrancsCommented:
angelIII,

yea
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Does this change anything:
INSERT INTO #ALLFacts1
EXEC spGetALLUserGroupData @UserGroupName , 'Factory'

CHeers
0
 
imrancsCommented:
please post the complete code of spGetALLUserGroupData


imran
0
 
jbonelloAuthor Commented:
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
 
imrancsCommented:
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
 
imrancsCommented:
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
 
imrancsCommented:
Oops typo,


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

Exec spGetALLUserGroupData,  @UserGroupName ,'Factory'
0
 
jbonelloAuthor Commented:
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
 
imrancsCommented:
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
 
ShogunWadeCommented:
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
 
jbonelloAuthor Commented:
Thanks for your interesting contribution ShogunWade.

0
 
jbonelloAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jbonelloAuthor Commented:
Thanks AngelIII,

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

0
 
ShogunWadeCommented:
"Thanks for your interesting contribution ShogunWade." your welcome.  hope it made it a bit clearer.
0
 
jbonelloAuthor Commented:
Any further contributions imrancs?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
imrancsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.