Print statement in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Print statement in stored procedure

Author

Topic

kbearhome
Starting Member

36 Posts

Posted - 2003-05-30 : 17:38:59

This is part of a stored procedure that returns warnings to users creating a record. If they don't fill in a particular field, I want it to warn them it's blank and then give them a list of record numbers to choose from. This is what I have so far but the warning only lists the first record number (field = iIncidentID). How do I get it to loop through the temp table (I was thinking the @@rowcount would do it) for the number of iIncidentIDs in my temp table? thanks

Begin

create table #IncidentInfo
(iOwnerID int not null,
iIncidentID int not null,
vchDesc1 varchar(255) null)
insert into #IncidentInfo
(iOwnerID ,
iIncidentID ,
vchDesc1 )
select iOwnerID ,
iIncidentID ,
vchDesc1 from incident
where iownerid=@iownerid
and isiteid=@isiteid and tirecordstatus=1 and iIncidentCategory=3
rows=@@rowcount
declare @message varchar(255)
select @message='Please enter an opportunity ID for this product'+': '+cast( iIncidentID as varchar)+', '+vchDesc1 from #IncidentInfo

Print @message

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-30 : 17:48:52

Why bother with the PRINT statement?

select 'Please enter an opportunity ID for this product'+': '+cast( iIncidentID as varchar)+', '+vchDesc1 from #IncidentInfo

Just use the above result set.

Tara

JCamburn
Starting Member

31 Posts

Posted - 2003-05-30 : 18:51:27

Use a cursor. Or use a WHILE loop if your temp table has a primary key that you can sort on and increment.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-30 : 18:58:45

Don't use a cursor!!!

Why bother with the PRINT statement when your TEMP table already has the data? Just use the record set.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-02 : 10:49:07

quote:

Use a cursor.

AAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHHHHHH

Brett

8-)

kbearhome
Starting Member

36 Posts

Posted - 2003-06-02 : 11:07:01

Thanks for the responses. I understand the concepts but don't know the syntax. I don't know what to use besides the print statement to put it in a warning. I figured it was a while loop but couldn't quite figure out how to write it. Can anyone give me snippet of code? I'm better at editing than creating from scratch so any example would help.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-02 : 12:37:30

You do not need to use a WHILE loop, a cursor, or a PRINT statement. All of the data is already available in the TEMP table. Your app just needs to get the record set and then warn the user for each record. To get the record set:

SELECT 'Please enter an opportunity ID for this product' + ': ' + cast( iIncidentID as varchar)+ ', '+ vchDesc1
from #IncidentInfo

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-02 : 12:40:09

I'd do it at the interface level, BEFORE performming a database operation. Also if, the interface misses it does try the INSERT or UPDATE, make the column NOT NULL with a constraint of <> ' '.

Brett

8-)

kbearhome
Starting Member

36 Posts

Posted - 2003-06-02 : 16:20:33

I apologize - maybe I needed to give more detail:

This is a third party application. I can edit many stored procedures to customize the product. Unless I misunderstand Tara, I cannot make the app get the record set. The print statement worked fine if I just said 'you must enter an opportunity id for this record'. The users want a list of opportunities (iIncidentID) for the company so they don't have to close the product record (can't minimize), go to another tab and find the iIncidentID, then go back to the product. Am I still off-base with a print statement?

Thanks again for your patience and your knowledge

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-02 : 16:30:14

quote:

I apologize - maybe I needed to give more detail:

This is a third party application. I can edit many stored procedures to customize the product. Unless I misunderstand Tara, I cannot make the app get the record set. The print statement worked fine if I just said 'you must enter an opportunity id for this record'. The users want a list of opportunities (iIncidentID) for the company so they don't have to close the product record (can't minimize), go to another tab and find the iIncidentID, then go back to the product. Am I still off-base with a print statement?

Thanks again for your patience and your knowledge

Ok I understand more now.

Here it is:

create table #IncidentInfo
(IncidentIdentity int identity(1,1) not null
iOwnerID int not null,
iIncidentID int not null,
vchDesc1 varchar(255) null)
insert into #IncidentInfo
(iOwnerID ,
iIncidentID ,
vchDesc1 )
select iOwnerID ,
iIncidentID ,
vchDesc1 from incident
where iownerid=@iownerid
and isiteid=@isiteid and tirecordstatus=1 and iIncidentCategory=3

declare @count int
select @count = count(*) from #IncidentInfo
declare @increment int
set @increment = 1

declare @message varchar(255)

while @increment < @count + 1
begin
select @message='Please enter an opportunity ID for this product'+': '+cast( iIncidentID as varchar)+', '+vchDesc1 from #IncidentInfo
WHERE IncidentIdentity = @increment

Print @message

set @increment = @increment + 1
end

I wrote this pretty quickly so it might have some bugs, but mostly it should work. Let me now if you get errors.

Tara

Edited by - tduggan on 06/02/2003 16:31:59

kbearhome
Starting Member

36 Posts

Posted - 2003-06-02 : 18:17:55

Thank you very much. I will give it a try.

Darlene

kbearhome
Starting Member

36 Posts

Posted - 2003-06-03 : 15:14:18

This returns the incidents but puts each one in a separate pop-up. Anyway to get them to list in one box?

Darlene

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-03 : 16:12:25

quote:

This returns the incidents but puts each one in a separate pop-up. Anyway to get them to list in one box?

Darlene

You'll need to put all of the iIncidentIDs into a string and then store this string in a variable. Then you just PRINT the variable. To put them all into a string, [url]http://www.sqlteam.com/item.asp?ItemID=11021[/url].

Tara

kbearhome
Starting Member

36 Posts

Posted - 2003-06-03 : 19:23:32

Tara,

Thanks so much. After being rather confused by the code in the link, I found it was actually easy to edit the code you gave me (see below). It works really well. I appreciate your time on this and I learned quite a bit.

Darlene

while @increment < @count + 1
begin
select @message=@message+cast( iIncidentID as varchar)+', '+vchDesc1+'; '+char(10)+char(13) from #IncidentInfo
WHERE IncidentIdentity = @increment

set @increment = @increment + 1
end

begin
Print 'Please enter an opportunity ID for this product: '+char(10)+char(13) +@message
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources