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
Solved

Why is my SQL Procedure crashing?

Posted on 2008-10-31
9
231 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migrate a SQL 2008 to 2016, 2 33
Text file into sql server 5 32
Job - date manual 1 35
tempdb log keep growing 7 33
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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