Why Stored Procedure Run My Quary More Than One

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

SQL

SELECT CI.ImageId , CI.CombinationId , C.Id 
FROM
Production.Product P
INNER JOIN
Production.Combination C
ON
P.Id = C.Product_Id
AND 
P.Id = 11
INNER JOIN
Production.Combination_Image CI 
ON
C.Id = CI.CombinationId
INNER JOIN
Production.[Image] I
ON 
CI.ImageId = I.Id
AND
I.Id = 18

and here my Result is right

RESULT :
18	12	12

Its fine and that's my real Result BUT !!! :|

NOW I make that query as a stored procedure
like under here

SQL

CREATE PROC GetCombinationAndCombinationImageByProductIdAndImageId
@ProductId INT,
@ImageId INT
AS
BEGIN
SELECT CI.ImageId , CI.CombinationId , C.Id 
FROM
Production.Product P
INNER JOIN
Production.Combination C
ON
P.Id = C.Product_Id
AND 
P.Id = @ProductId
INNER JOIN
Production.Combination_Image CI 
ON
C.Id = CI.CombinationId
INNER JOIN
Production.[Image] I
ON 
CI.ImageId = I.Id
AND
I.Id = @ImageId
END


-- and here EXECUTE my proc
EXEC GetCombinationAndCombinationImageByProductIdAndImageId @ProductId = 11 , @ImageId=18

AND ... RESULT :|

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 217, Level 16, State 1, Procedure GetCombinationAndCombinationImageByProductIdAndImageId, Line 55
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Please Help Me :)

Regards

Posted 9-Dec-15 2:41am

Ali Javani

Updated 9-Dec-15 2:50am

PIEBALDconsult

v2

Add a Solution

Comments

ZurdoDev 9-Dec-15 8:50am

You have nesting going on. You'll have to figure out where.

Ali Javani 9-Dec-15 8:54am

http://www.sql-server-helper.com/error-messages/msg-217.aspx[^]

and i understand recursion in SQL SERVER
i don't know how do i fix this

Regards

PIEBALDconsult 9-Dec-15 8:53am

Does the procedure include the EXEC command shown at the bottom? If so, remove it.

Ali Javani 9-Dec-15 8:57am

i wrote EXEC for testing query .
if i remove it, how do i see result of execution ?

PIEBALDconsult 9-Dec-15 8:59am

You execute it separately; it should not be part of the procedure.

Ali Javani 9-Dec-15 9:02am

no it's Enclosed that with
batch (GO)
like here:
GO
EXEC GetCombinationAndCombinationImageByProductIdAndImageId @ProductId = 11 , @ImageId=18
GO

PIEBALDconsult 9-Dec-15 9:04am

But if it has become part of the procedure it will cause the problem that you have.
Have SSMS create an ALTER script for the procedure and see what it actually contains.

Ali Javani 9-Dec-15 8:59am

if i remove it ..
does it works right in my business layer ?

PIEBALDconsult 9-Dec-15 9:01am

Better than it does now.

Ali Javani 9-Dec-15 9:03am

OH Gosh That's Fixed :D

Your My HERO DEAR PIEBALDconsult

PIEBALDconsult 9-Dec-15 9:04am

Glad to be of service.

ZurdoDev 9-Dec-15 9:09am

I saw that too but saw the BEGIN and END and forgot about the GO.

Please post as solution.

Ali Javani 13-Apr-16 17:32pm

Thanx

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ali Javani · Accepted Answer · 2015-12-09T03:15:00

DEAR "PIEBALDconsult" said :
i need enclose my execution
and i just observance the encapsulation
with batch
"GO"

Exactly i forgot GO
:)

thanks
"PIEBALDconsult"

SQL

ALTER PROC GetCombinationAndCombinationImageByProductIdAndImageId
@ProductId INT,
@ImageId INT
AS
BEGIN
SELECT CI.ImageId , CI.CombinationId
FROM
Production.Product P
INNER JOIN
Production.Combination C
ON
P.Id = C.Product_Id
AND 
P.Id = @ProductId
INNER JOIN
Production.Combination_Image CI 
ON
C.Id = CI.CombinationId
INNER JOIN
Production.[Image] I
ON 
CI.ImageId = I.Id
AND
I.Id = @ImageId
END
GO
EXEC GetCombinationAndCombinationImageByProductIdAndImageId @ProductId = 11 , @ImageId=17
GO