Why is my SQL Procedure crashing?

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

trumpmanAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
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
 
chapmandewCommented:
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
 
trumpmanAuthor Commented:
@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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
Can you give me the error message it is givng you?
0
 
trumpmanAuthor Commented:
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
 
chapmandewCommented:
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
 
trumpmanAuthor Commented:
It is now listing the following 8 times:

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.
0
 
trumpmanAuthor Commented:
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
 
chapmandewCommented:
you didn't declare @count inside your dynamic sql...and really didn't need to so I just added it the other way.
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.