Solved

Why is my SQL Procedure crashing?

Posted on 2008-10-31
9
228 Views
Last Modified: 2010-03-20
Hello,

I am attempting to loop through a statement to EXECUTE a SQL statement the number of times equal to the amount of rows in a table.  However, it is crashing upon execution.


Where is my problem?
Create Procedure [dbo].[sp_***](
 

@subdomain_name nvarchar(15),

@city nvarchar (25),

@state nvarchar (2),

@email nvarchar (50)

)

AS
 

DECLARE @count int 

DECLARE @totalCount int
 

SELECT @totalCount = count(*) from navSystemA

SET @count = 0
 

WHILE @count <= @totalCount

	BEGIN

		SET @cmd = N'INSERT INTO navSystemAMap (storeID, navID, active) 

				   VALUES (select count(*) from navSystemA)+ 1, @count, 1)'

		SET @parameters ='@subdomain_name nvarchar(15)'

		EXEC sp_executesql @cmd, @parameters, @subdomain_name

		SELECT @count = @count - 1

	END
 

GO

Open in new window

0
Comment
Question by:trumpman
  • 5
  • 4
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Create Procedure [dbo].[sp_***](
 
@subdomain_name nvarchar(15),
@city nvarchar (25),
@state nvarchar (2),
@email nvarchar (50)
)
AS
 
DECLARE @count int
DECLARE @totalCount int
 
SELECT @totalCount = count(*) from navSystemA
SET @count = 0
 
WHILE @count <= @totalCount
        BEGIN
                SET @cmd = N'INSERT INTO navSystemAMap (storeID, navID, active)
                                   VALUES (select count(*)+ 1, @count, 1 from navSystemA))'
                SET @parameters ='@subdomain_name nvarchar(15)'
                EXEC sp_executesql @cmd, @parameters, @subdomain_name
                SELECT @count = @count - 1
        END
 
GO
0
 

Author Comment

by:trumpman
Comment Utility
@chapmandew:

I  made your change and corrected some incorrect loop logic.  However, the crash is still occuring.  Any further ideas?


DECLARE @count int 

DECLARE @totalCount int
 

SELECT @totalCount = count(*) from navSystemA

SET @count = -1
 

WHILE @count < @totalCount

	BEGIN

		SET @cmd = N'INSERT INTO navSystemAMap (storeID, navID, active) 

				   VALUES (select count(*)+ 1, @count, 1 from store_profile))'

				   

		SET @parameters ='@subdomain_name nvarchar(15)'

		EXEC sp_executesql @cmd, @parameters, @subdomain_name, @count

		SELECT @count = @count + 1

	END

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Can you give me the error message it is givng you?
0
 

Author Comment

by:trumpman
Comment Utility
Sorry @chapmandew.  I meant to include it but forgot.  The following is repeated 12 times in the Browser.

Incorrect syntax near the keyword 'select'.
Must declare the scalar variable "@count".
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
try this:

DECLARE @count int
DECLARE @totalCount int
 
SELECT @totalCount = count(*) from navSystemA
SET @count = -1
 
WHILE @count < @totalCount
        BEGIN
                SET @cmd = N'INSERT INTO navSystemAMap (storeID, navID, active)
                                   VALUES (select count(*)+ 1, ' + cast(@count as varchar(50)) + ', 1 from store_profile))'
                                   
                SET @parameters ='@subdomain_name nvarchar(15)'
                EXEC sp_executesql @cmd, @parameters, @subdomain_name
                SELECT @count = @count + 1
        END
0
 

Author Comment

by:trumpman
Comment Utility
It is now listing the following 8 times:

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
having one of those days...sorry.  try this

DECLARE @count int
DECLARE @totalCount int
 
SELECT @totalCount = count(*) from navSystemA
SET @count = -1
 
WHILE @count < @totalCount
        BEGIN
                SET @cmd = N'INSERT INTO navSystemAMap (storeID, navID, active)
                                   select count(*)+ 1, ' + cast(@count as varchar(50)) + ', 1 from store_profile'
                                   
                SET @parameters ='@subdomain_name nvarchar(15)'
                EXEC sp_executesql @cmd, @parameters, @subdomain_name
                SELECT @count = @count + 1
        END
0
 

Author Comment

by:trumpman
Comment Utility
No problem.  I appreciate your help! This last solution was the winner.  Just for my understanding... Why cast(@count as varchar(50))  necessary..and not simply @count?

Again, thanks so much.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
you didn't declare @count inside your dynamic sql...and really didn't need to so I just added it the other way.
0

Featured Post

What Is Threat Intelligence?

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

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

8 Experts available now in Live!

Get 1:1 Help Now