• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8017
  • Last Modified:

How do I Print messages in an SQL Function

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

0
SwamyN
Asked:
SwamyN
  • 6
  • 5
  • 2
1 Solution
 
pivarCommented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.

0
 
pivarCommented:
Yes angelIII is right, disregard my post. I wasn't thinking. 8-(
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
SwamyNAuthor 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

0
 
SwamyNAuthor 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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in a function, you cannot "insert/update/delete" neither !!!
0
 
SwamyNAuthor 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?
0
 
SwamyNAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)
0
 
SwamyNAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>is there one for SQL server 2000 as well?

yes, in the query analyser:
http://articles.techrepublic.com.com/5100-10878_11-5768655.html
0
 
SwamyNAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now