Solved

Nested INSERT EXEC

Posted on 2004-09-09
22
470 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
Comment Utility
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]
Comment Utility
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
Comment Utility
agree 100% with imran,   you dont need DSQL here.
0
 

Author Comment

by:jbonello
Comment Utility
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
Comment Utility
angelIII,

yea
0
 
LVL 142

Expert Comment

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

CHeers
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
please post the complete code of spGetALLUserGroupData


imran
0
 

Author Comment

by:jbonello
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Oops typo,


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

Exec spGetALLUserGroupData,  @UserGroupName ,'Factory'
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:jbonello
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your interesting contribution ShogunWade.

0
 

Author Comment

by:jbonello
Comment Utility
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]
Comment Utility
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
Comment Utility
Thanks AngelIII,

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

0
 
LVL 18

Expert Comment

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

Author Comment

by:jbonello
Comment Utility
Any further contributions imrancs?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
>>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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

10 Experts available now in Live!

Get 1:1 Help Now