Database

David Skelly wrote:

in sql server function

J walia wrote:

then what is solution for this?

J walia wrote:

I have one more question. can we use


Execute sp_executesql in functions

J walia wrote:

Does PRINT work in sql server function???

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

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

I are Troll

CREATE FUNCTION dbo.FindRoot(@id int)
RETURNS int
AS  
BEGIN 
  DECLARE @Id_parent int
  SELECT @Id_parent= Id_parent
  FROM TableName
  WHERE id = @id
  WHILE @Id_parent <> NULL
    BEGIN
      SELECT @id = @Id_parent
      SELECT @Id_parent = Id_parent
      FROM PrimeInfo
      WHERE id = @id
    END
  RETURN @id
END

CREATE PROCEDURE BuildTree(@id int)
AS 
SET NOCOUNT ON
CREATE TABLE #results(level int, id int, id_parent int)
DECLARE @id_parent int
DECLARE @level int
SELECT @level = 1
DECLARE @root int
SELECT @root = dbo.FindRoots(@id)
CREATE TABLE #stack (id int, level smallint)
INSERT INTO #stack VALUES (@root, @level)
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
  BEGIN
    SELECT @id = s.id, @id_parent= IsNull(t.id_parent, 0)
    FROM #stack s INNER JOIN TableName t
    ON t.id = s.id
    WHERE level = @level
    INSERT INTO #results VALUES (@level, @id, @id_parent)
    DELETE FROM #stack
    WHERE level = @level
    AND id = @id
    INSERT #stack
    SELECT id, @level + 1
    FROM TableName
    WHERE id_parent = @id
    IF @@ROWCOUNT > 0 
      BEGIN
        SELECT @level = @level + 1
    END
 END--IF EXISTS
 ELSE
 BEGIN
   SELECT @level = @level - 1
 END
END -- WHILE
SELECT id, id_parent, level FROM #results