We help IT Professionals succeed at work.

How do I Print messages in an SQL Function

SwamyN
SwamyN asked
on
Medium Priority
15,213 Views
Last Modified: 2012-05-06
i need an alert in a loop in an SQL function.

I tried PRINT and even creating a table so i can INSERT or UPDATE values
but I get an error saying these are not supported by SQL.
I even tried creating a stored procedure which takes a string as input and prints it. That didn't work either.
create function test 
returns bit
as
begin
declare @Counter as int
set @ Counter =0
while(@Counter<32)
   if exists(select ColumnA from TBL where ColumnB = '1')
         print @Counter    --This throws as error. What can i use here instead of PRINT?
   else
          print 'Does Not Exist'
 
end

Open in new window

Comment
Watch Question

Commented:
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

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

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

Author

Commented:
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 

Open in new window

Author

Commented:
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

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
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?

Author

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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)

Author

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>is there one for SQL server 2000 as well?

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

Author

Commented:
how do i create a loop to refer through a table variable row by row?
All the fetch  examples i looked through use cursors.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>All the fetch  examples i looked through use cursors.
yes, the typical problem.

here we go:

declare @tmp table ( id int )
declare @id int
 
-- insert into temp tables is possible in functions, btw
insert into @tmp (id) select keyfield from yourtable 
 
while @@rowcount > 0
begin
  select top 1 @id = id from @tmp
  if @@rowcount > 0
  begin
    'process the @id value here:
     
  end
  delete @tmp where id = @id
 
end

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.