How do I Print messages in an SQL Function

Hi,

You have to convert @counter to a string first

create function test
returns bit
as
begin
declare @Counter as int
declare @text varchar(10)
set @ Counter =0
while(@Counter<32)
   if exists(select ColumnA from TBL where ColumnB = '1') begin
         set @text=convert(varchar(10),@Counter)
         print @text
   end else
          print 'Does Not Exist'
 
end

/peter

your problem is you are missing the BEGIN .. END for the WHILE
also, you "loop" does never end (as the code is right now).

finally, you cannot PRINT in a function, you can PRINT in a procedure.

Yes angelIII is right, disregard my post. I wasn't thinking. 8-(

rite..thanks for replying both of u....my mistake....forgot the begin end
so if i cannot print in a function, what is the standard procedure for viewing intermediate values of variables (debugging basically)?  I am using SQL Server 2000
....also, I tried using a Stored Procedure....but i use a cursor to go through records in a table. This seems to create a problem when the Stored Procedure calls itself.
I have attached the Function below, in case it's required.

alter function InsertFaultyReporting (@CheckEmpcode varchar(12),@CurrEmpcode varchar(12)) 
returns bit
as
begin
 
declare @ReportingEmpcode varchar(12)
 
declare CurrEmpCursor cursor FAST_FORWARD  
FOR select ReportingToempcode from TestReporting where empcode <> ReportingToEmpcode and empcode = @CurrEmpcode
 
OPEN CurrEmpCursor
	FETCH NEXT from CurrEmpCursor into @ReportingEmpcode
	while @@Fetch_Status = 0
	begin
		if (@CheckEmpcode = @ReportingEmpcode)
		begin
			--print cast(@CheckEmpcode as varchar) +  ' first ' + cast(@ReportingEmpcode as varchar)
			--exec AshwinPrint @StringToPrint
			--insert into #TEMPValidateReportingTo values(@CheckEmpcode,@ReportingEmpcode)
			return 1
		end
		else
		begin
			if (dbo.InsertFaultyReporting(@CheckEmpcode,@ReportingEmpcode) = 1)
				begin
					--print @CheckEmpcode + ' second ' + @ReportingEmpcode
					--exec AshwinPrint @StringToPrint
					CLOSE CurrEmpCursor
					DEALLOCATE CurrEmpCursor
					return 1
				end
			else
				FETCH NEXT from CurrEmpCursor into @ReportingEmpcode
		end
	end
 
CLOSE CurrEmpCursor
DEALLOCATE CurrEmpCursor
 
return 0
end 

Select allOpen in new window

The above function is called in a stored procedure, which calls this function for every single employee in a table.

ALTER     proc dbo.ValidateReportingTo
as
begin
 
declare @iReturn int
declare @Empcode varchar(12)
declare @EmpName varchar(100)
declare @CurrEmp varchar(12)
 
CREATE TABLE #TEMPValidateReportingTo(Empcode varchar(12)) 
 
declare EmpCursor cursor FAST_FORWARD  
FOR select empcode from TestReporting --select * from TestReporting1
 
OPEN EmpCursor
	FETCH NEXT from EmpCursor into @Empcode
	while @@Fetch_Status = 0
		begin
			if (dbo.InsertFaultyReporting(@Empcode,@Empcode) = 1)
			begin
				insert into #TEMPValidateReportingTo values (@Empcode)
			end
			FETCH NEXT from EmpCursor into @Empcode
		end
 
CLOSE EmpCursor
DEALLOCATE EmpCursor
 
select distinct empcode from #TEMPValidateReportingTo
 
DROP TABLE #TEMPValidateReportingTo
end

Select allOpen in new window

in a function, you cannot "insert/update/delete" neither !!!

yes, thanks......those lines are commented...they were my futile attempts at debugging
My question is how do i watch values of variables, as they change through the process?

the first code snippet was just an example
the third code snippet is a stored procedure which calls a recursive function (the second code snippet)
Hope I've explained my requirement clearly enough.
I want to know how i can watch variables (since they can't be PRINTed or inserted into tables) or if it's even possible at all.

you need to use the debugging features (if installed), and watch the variables.
otherwise, you need to develop in a procedure instead, and change it later to a function.

now, "CURSOR", if possible, should be avoided. using a table variable and "fetching/deleting" row by row from there will be more efficient (because resource-saving)

i know there's a debugger in SQL 2005, is there one for SQL server 2000 as well? If there is how can I access it.
thanks for the tip about not using the cursor...i'll try using the stored procedure again without a cursor this time.

>is there one for SQL server 2000 as well?

yes, in the query analyser:
http://articles.techrepublic.com.com/5100-10878_11-5768655.html

how do i create a loop to refer through a table variable row by row?
All the fetch  examples i looked through use cursors.