troubleshooting Question

Can't insert breakpoints when debugging udf in sql server 2005

Avatar of krysova
krysova asked on
Microsoft SQL Server
2 Comments1 Solution637 ViewsLast Modified:
I am running SQL 2005, Management Studio 9.00, Visual Studio 2005 Version 8.0.50727.762 Premier Partner Edition - ENU Svc Pack 1.

I created a user-defined function in a database using Mgt Studio.  I opened Visual Studio 2005 and opened a Data Connection to the database.  Using Server Explorer, I navigated to Stored Procedures and created a new procedure to test the function.  I then opened the function and tried to insert a breakpoint, but it is greyed out.  I can run the procedure but not debug it.  What am I missing?  I used this site as my guide:  http://msdn.microsoft.com/en-us/library/ms165047(VS.80).aspx

=======
The udf:
=======
ALTER FUNCTION dbo.ufstrRouteRR 
(	-- Add the parameters for the function here
	@sRouting as nvarchar,
    @iSeq AS Int)
RETURNS nvarchar(4)
AS
BEGIN  --1
	-- Declare the return variable here
	DECLARE @ResultVar nvarchar(8)
	
	-- Declare other variables here
	DECLARE @i int
	DECLARE @intDirect int
	DECLARE @intDash1 int
	DECLARE @intDashStart int
	DECLARE @intDashEnd int
	DECLARE @NullString nchar(1)
 
	-- Add the T-SQL statements to compute the return value here
	SELECT @intDirect = CHARINDEX('Direct',@sRouting)
	SELECT @intDash1 = CHARINDEX('-',@sRouting)
	SELECT @NullString=''
 
	IF @intDirect>0 
.... (etc, etc)
	-- Return the result of the function
	RETURN @ResultVar
 
END --1
 
============
The procedure:
============
ALTER PROCEDURE dbo.Test_udf 
AS
    SELECT dbo.ufstrRouteRR('ACWR-ABRDN-CSXT-HAGTN-NS',1) as Test1   inventory
    SELECT dbo.ufstrRouteRR('ACWR-ABRDN-CSXT-HAGTN-NS',2) as Test2 
    SELECT dbo.ufstrRouteRR('ACWR-ABRDN-CSXT-HAGTN-NS',3) as Test3  
	RETURN
 
=============================
The result of running the procedure:
=============================
 
Running [dbo].[Test_udf].
 
Test1  
------ 
A      
No rows affected.
(1 row(s) returned)
Test2  
------ 
       
No rows affected.
(1 row(s) returned)
Test3  
------ 
       
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Test_udf].
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros