asked on

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

chapmandew

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

trumpman

ASKER

@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

chapmandew

Can you give me the error message it is givng you?

trumpman

ASKER

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".

chapmandew

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

trumpman

ASKER

It is now listing the following 8 times:

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trumpman

ASKER

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.

chapmandew

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