Getting PRINT into a sproc - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Getting PRINT into a sproc

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-15 : 13:34:22

Why does this execute:

CREATE FUNCTION X (@Variable int) RETURNS int AS
BEGIN [Intermediary Code] END
GO
PRINT 'Created'
GO

And this"

IF EXISTS([Condition])
BEGIN
	DROP Function dbo.X;
	PRINT 'Deleted';
END
GO

But this does not?

CREATE FUNCTION X (@Variable int) RETURNS int AS
BEGIN [Intermediary Code] END;
PRINT 'Created'
GO

Thanks,
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 13:36:03

are you using RETURN inside the BEGIN END block? the execution returns to calling code once it sees the RETURN.

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-15 : 13:40:58

quote:
Originally posted by visakh16

are you using RETURN inside the BEGIN END block?

Yes. However, I don't want the PRINT to be part of the function. I just want this "success message" to show, if the other steps in the batch compiles correctly. How would I do that?
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 13:42:21

Just put a GO statement before the PRINT.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 13:42:52

I think a CREATE FUNCTION statement needs to be alone in a statement.
The error message Incorrect syntax near the keyword 'PRINT'. says so.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 13:45:12

quote:
Originally posted by Arithmomaniac

quote:
Originally posted by visakh16

are you using RETURN inside the BEGIN END block?

Yes. However, I don't want the PRINT to be part of the function. I just want this "success message" to show, if the other steps in the batch compiles correctly. How would I do that?
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

how you are using create function as a part of batch? you may be better to put this PRINT where you invoke function just following it.

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-15 : 13:46:01

quote:
Originally posted by Peso

I think a CREATE FUNCTION statement needs to be alone in a statement.
The error message Incorrect syntax near the keyword 'PRINT'. says so.

It was. I think I'll just use an IF_EXISTS query to test if the table/function was created, and print if so.
Thanks,
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 13:51:22

quote:
Originally posted by Arithmomaniac

quote:
Originally posted by Peso

I think a CREATE FUNCTION statement needs to be alone in a statement.
The error message Incorrect syntax near the keyword 'PRINT'. says so.

It was. I think I'll just use an IF_EXISTS query to test if the table/function was created, and print if so.
Thanks,
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

No it wasn't as you didn't have the GO to indicate begin/end of batch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-15 : 14:09:03

quote:
Originally posted by tkizer
No it wasn't as you didn't have the GO to indicate begin/end of batch

I'm confused...you mean it shouldn't have said "...near PRINT"? Because it did.

---------
Ignorance may be bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 14:14:05

I don't know what you mean by "...near PRINT". But you need "GO" before the PRINT statement to indicate the end of the function so that the function is in its own batch. It's a requirement when creating functions. They must be in their own batch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources