|
J walia wrote: Does PRINT work in sql server function???
No.
You cannot use PRINT inside a function in SQL Server.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
thanks sir.
One person's data is another person's program.
--J.Walia
|
|
|
|
|
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.
|
|
|
|
|
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'
INSERT INTO @printz(Msg)
SELECT 'Something went terribly wrong here, eracing all evidence'
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.',
18,
1,
N'number',
5);
RAISERROR (N'This is message %s %d.',
18,
1,
N'number',
6);
END
Good luck
I are Troll
|
|
|
|
|
I have Employees and Reporting hirarchy.For Ex:
1
2 3 4
5 6 7 8
Employees 2,3,4 report to 1 and 5,6 report to 2 and 7,8 report t0 4.
If I pass 1 to query it results
2,3,4,5,6,7,8
2 then 5,6
3 then 7,8
How can i get this in sqlserver 2005
|
|
|
|
|
What did you so far for your query?
Use CASE switch to archive your query.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I have thousands of records how can I use CASE ?
|
|
|
|
|
|
I have only two columns like below.
id id_project id_parent
-------------------------
1 root root
2 abc123 root
3 xyz098 root
4 cmd003 xyz098
5 asd874 abc123
6 f8jk12 cmd003
So, I will need the output to look similar to this when pass id as '1'
- abc123
-- asd874
- xyz098
-- cmd003
--- f8jk12
So, I will need the output to look similar to this when pass id as '3'
- cmd003
-- f8jk12
So, I will need the output to look similar to this when pass id as '2'
- asd874
How can I do this in sqlserver2005
|
|
|
|
|
Did you read the article i linked you to?
|
|
|
|
|
To get result, you need to write recursive function
A function to get Parent
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
and then store procedure to extract your data by providing id value
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
ELSE
BEGIN
SELECT @level = @level - 1
END
END
SELECT id, id_parent, level FROM #results
|
|
|
|
|
I would use a cursor to step through each level of the hierarchy and insert the results into a temp table.
Then just select from the temp table at the end.
|
|
|
|
|
Can you post query how to step through each level
|
|
|
|
|
Actually, a recursive CTE is probably your best bet.
|
|
|
|
|
Here's an example that probably does exactly what you want:
Recursive CTE[^]
|
|
|
|
|
I tried below stored procedure. I am getting the result what I want but its print statement. How can I convert this into table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
Insert it into a temp table and then select * from that table at the end.
|
|
|
|
|
I did that ... As everytime sp excutes output displays like multiple tables but not in one table
|
|
|
|
|
Have you tried using a CTE or a cursor?
I reckon those are your best bet and probably what your teacher is looking for if this is a homework question.
|
|
|
|
|
This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
Have a look to this code and see whether it solves your problem,
CREATE TABLE TempTree (Id int IDENTITY, Id_Project VARCHAR(100), Id_Parent VARCHAR(100))
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Root','Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 1', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 2', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 3', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 1', 'Level - 1 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 2', 'Level - 1 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 3', 'Level - 1 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 4', 'Level - 1 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 5', 'Level - 1 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 6', 'Level - 1 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 1', 'Level - 2 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 2', 'Level - 2 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 3', 'Level - 2 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 4', 'Level - 2 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 5', 'Level - 2 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 6', 'Level - 2 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 7', 'Level - 2 4')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 8', 'Level - 2 4')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 9', 'Level - 2 5')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 10', 'Level - 2 5')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 11', 'Level - 2 6')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 12', 'Level - 2 6')
CREATE PROC Dbo.Proc_TheTree (@parent VARCHAR(100))
AS
CREATE TABLE #TheList (RootId int, RootName VARCHAR(100), ChildName VARCHAR(100))
CREATE TABLE #TheSearch (SLNO INT IDENTITY, ParentName VARCHAR(100), IsSearchCompleted BIT)
IF NOT EXISTS (SELECT * FROM TempTree WHERE id_parent = @parent)
BEGIN
SELECT * FROM TempTree WHERE id_project = @parent
END
ELSE
BEGIN
INSERT INTO #TheSearch (ParentName, IsSearchCompleted)
SELECT id_project, 0 FROM TempTree WHERE id_parent = @parent
INSERT INTO #TheList (RootId, RootName, ChildName)
SELECT (SELECT Id FROM TempTree WHERE Id_Project= @parent), Id_Parent, Id_Project
FROM TempTree
WHERE id_parent = @parent
DECLARE @MINSLNO INT
DECLARE @PARENTNAME VARCHAR(100)
SELECT @MINSLNO = MIN(SLNO) FROM #TheSearch
WHILE ISNULL(@MINSLNO,0) > 0
BEGIN
SELECT @PARENTNAME = ParentName FROM #TheSearch
WHERE SLNO = @MINSLNO AND ParentName <> @parent
IF EXISTS (SELECT * FROM TempTree WHERE id_parent = @PARENTNAME)
BEGIN
INSERT INTO #TheList (RootId, RootName, ChildName)
SELECT (SELECT Id FROM TempTree WHERE Id_Project= @PARENTNAME), Id_Parent, Id_Project
FROM TempTree
WHERE id_parent = @PARENTNAME
INSERT INTO #TheSearch (ParentName, IsSearchCompleted)
SELECT id_project, 0 FROM TempTree WHERE id_parent = @PARENTNAME
END
SELECT @MINSLNO = MIN(SLNO) FROM #TheSearch WHERE SLNO > @MINSLNO
END
SELECT * FROM #TheList
END
EXEC Proc_TheTree 'Level - 1 1'
|
|
|
|
|