Solved

Why is my SQL Procedure crashing?

Posted on 2008-10-31
9
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22852490
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
ID: 22852726
@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
ID: 22852743
Can you give me the error message it is givng you?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:trumpman
ID: 22852785
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22852803
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
ID: 22852865
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
ID: 22853026
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
ID: 22853211
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
ID: 22853253
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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