Does PRINT work in sql server function

Jagz W

Hi all Does PRINT work in sql server function??? One person's data is another person's program. --J.Walia

Mycroft Holmes

Yes

Never underestimate the power of human stupidity RAH

Jagz W

but i am getting the below error: Invalid use of side-effecting or time dependent operator in 'PRINT' within function One person's data is another person's program. --J.Walia

Mycroft Holmes

Then you are trying to print something and it produces an error, find out what the produces the error and fix it. Master of logic - thats me! What are you trying to print

Never underestimate the power of human stupidity RAH

David Skelly

J walia wrote:

Does PRINT work in sql server function???

No. You cannot use PRINT inside a function in SQL Server.

Mycroft Holmes

David Skelly wrote:

in sql server function

My mistake I didn't even see that - thanks for fixing that.

Never underestimate the power of human stupidity RAH

Jagz W

thanks for reply. then what is solution for this? I have one more question. can we use Execute sp_executesql in functions One person's data is another person's program. --J.Walia

Mycroft Holmes

You do realise that it is probably quicker to actually create a function and try and execute a stored proc. Then take the error message to google/BOL and read up on the problem. It will give you a greater depth of knowledge than a forum post. AND you will not risk some one giving you the wrong answer. You also can't use dynamic SQL in a function, just to save your next question!

Never underestimate the power of human stupidity RAH

David Skelly

J walia wrote:

then what is solution for this?

The solution to what? Your question was "Can I use print in a function?" The answer is "No, you can't".

J walia wrote:

I have one more question. can we use Execute sp_executesql in functions

No.

Jagz W

thanks sir. One person's data is another person's program. --J.Walia

Lost User

J walia wrote:

Does PRINT work in sql server function???

You can convert your prints to match something like below;

DECLARE @printz AS TABLE(
Stamp DATETIME DEFAULT GETDATE()
,Msg NVARCHAR(MAX)
)

INSERT INTO @printz(Msg)
SELECT 'We are at the start of the proc'

-- Do bunch o' SQL here

INSERT INTO @printz(Msg)
SELECT 'Something went terribly wrong here, eracing all evidence'

-- Print some more

INSERT INTO @printz(Msg)
SELECT 'We are at the end of the proc'

SELECT Stamp, Msg FROM @printz

Another option that I sometimes resort to, is the RAISERROR[^] statement. To test that it'll print both statements from a sproc;

try
{
using (System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection(
"Server=.;Database=[YOURDBNAME];Trusted_Connection=True;"))
using(var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = "testerror";
cmd.ExecuteNonQuery();
}
}
catch(System.Data.SqlClient.SqlException ex)
{
System.Diagnostics.Debug.Print(ex.ToString());
}

CREATE PROCEDURE TESTERROR AS
BEGIN
RAISERROR (N'This is message %s %d.', -- Message text.
18, -- Severity,
1, -- State,
N'number', -- First argument.
5); -- Second argument.

RAISERROR (N'This is message %s %d.', -- Message text.
       18, 
       1, 
       N'number', 
       6);

END

Good luck :)

I are Troll :suss: