Handling special characters with FOR XML PATH(”)

In sql server using stuff  the string replaced when its  have tag “<” or “>” then  &gt;  when its  “&” then &amp;

Example

select
stuff(
(select ‘, <‘ + name + ‘>’
from sys.databases
where database_id > 4
order by name
for xml path(”)
)
, 1, 2, ”) as namelist;

For avoid replace  &gt; or &amp;

 

select
stuff(
(select ‘, <‘ + name + ‘>’
from sys.databases
where database_id > 4
order by name
for xml path(”), root(‘MyString’), type
     ).value(‘/MyString[1]’,’varchar(max)’)
, 1, 2, ”) as namelist;

 

 

Advertisements

Hirarchy (Parent -child- child)

I am trying this query every time. but I can’t get my proper output. then I continuous try this query I get this result. This query very useful me.

2016-12-29_211943.png

WITH CTE AS
(
SELECT CP.Position,CP.Description,CP.sdfParentID,ROW_NUMBER() OVER (ORDER BY POSITION) RN,CAST(‘ ‘ AS VARCHAR(8000)) AS COL
FROM sdfComPriv CP WHERE sdfParentID IS NULL
UNION ALL
SELECT CP.Position,CP.Description,CP.sdfParentID,RN,COL+CAST(RN AS VARCHAR(2))+CAST(ROW_NUMBER() OVER (ORDER BY C.Position) AS VARCHAR(10))–+CAST(LEVEL AS VARCHAR(10))
FROM sdfComPriv CP
INNER JOIN CTE C ON C.Position=CP.sdfParentID
)

SELECT * FROM CTE
ORDER BY RN,col

 

outer sp throw proper error. inner sp throw only 6401 error(Cannot roll back OP2. No transaction or savepoint of that name was found.)

USE TEST

DROP TABLE NestedTbl
CREATE TABLE NestedTbl(slno tinyint not null primary key identity, Val VARCHAR(20) UNIQUE )

 

ALTER PROC NestedOp1
AS
BEGIN
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
BEGIN TRAN OP1
INSERT INTO NestedTbl(Val) VALUES(1)
–INSERT INTO NestedTbl(Val) VALUES(1)
EXEC NestedOp2
COMMIT TRAN OP1
END TRY
BEGIN CATCH

IF @@TRANCOUNT<>0 ROLLBACK TRAN OP1
PRINT ‘SP 1’

PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_NUMBER()
PRINT ERROR_LINE()
END CATCH
END
ALTER PROC NestedOp2
AS
BEGIN

BEGIN TRY
BEGIN TRAN OP2
INSERT INTO NestedTbl(Val) VALUES(1)
EXEC NestedOp3
COMMIT TRAN OP2
END TRY
BEGIN CATCH
IF @@TRANCOUNT<>0 ROLLBACK TRAN OP2
PRINT ‘SP 2’
PRINT ERROR_MESSAGE()
END CATCH
END
CREATE PROC NestedOp3
AS
BEGIN
BEGIN TRY
BEGIN TRAN OP3
INSERT INTO NestedTbl(Val) VALUES(1)
COMMIT TRAN OP3
END TRY
BEGIN CATCH
IF @@TRANCOUNT<>0 ROLLBACK TRAN OP3
PRINT ERROR_MESSAGE()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_NUMBER()
PRINT ERROR_LINE()
END CATCH
END

 

SELECT * FROM NestedTbl
EXEC NestedOp1

SQL Server Scripts: Get All Nested Stored Procedures List (Procedures with Dependent Procedures)

SELECT * FROM (SELECT NAME AS ProcedureName, SUBSTRING(( SELECT ‘, ‘ + OBJDEP.NAME
FROM sysdepends
INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
WHERE obj.type = ‘P’
AND Objdep.type = ‘P’
AND sysdepends.id = procs.object_id
ORDER BY OBJ.name

FOR
XML PATH(”)
), 2, 8000) AS NestedProcedures
FROM sys.procedures procs )InnerTab
WHERE NestedProcedures IS NOT NULL

User Defined Functions

User Defined Function

 

In this Article we will learn about User-Defined Functions (UDFs) in Sql Server. All the examples in this article uses the pubs database.

There are three Types of UDFS in Sql Server:
1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued

Let us go through each of these with examples:

1.  Scalar User-Defined Function

A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages.

Example 1: Here we are creating a Scalar UDF AddTwoNumbers which accepts two input parameters @a and @b and returns output as the sum of the two input parameters.

CREATEFUNCTIONAddTwoNumbers
(
@a int,
@b int
)
RETURNSint
AS
BEGIN
RETURN@a + @b
END

Once the above function is created we can use this function as below:

PRINT dbo.AddTwoNumbers(10,20)
--OR
SELECTdbo.AddTwoNumbers(30,20)

Note: For Scalar UDFS we need to use Two Part Naming Convention i.e. in the above two statements we are using dbo.AddTwoNumbers.

Whether Below statement is correct? No, because it is not using two-part naming convention. Try executing the below statement it will error out…

PRINT AddTwoNumbers(10,20)

2.  Inline Table-Valued User-Defined Function

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.

Example: In this example we are creating a Inline table-valued function GetAuthorsByState which accepts state as the input parameter and returns firstname and lastname  of all the authors belong

USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS table
AS
RETURN (
SELECT au_fname, au_lname
FROM Authors
WHERE state=@state
)
GO

We can use the below statement to get all the authors in the state CA.

SELECT* FROMGetAuthorsByState('CA')

3. Multi-statement Table-Valued User-Defined Function

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-Sql statement. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Example: In this example we are creating a Multi-Statement Table-Valued function GetAuthorsByState which accepts state as the input parameter and returns author id and firstname of all the authors belonging to the input state. If for the input state there are no authors then this UDF will return a record with no au_id column value and firstname as ‘No Authors Found’.

 

USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT  au_id,
au_fname
FROM Authors
WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState
VALUES ('','No Authors Found')
END
RETURN
END
GO

We can use the below statements to get all the authors in the given input state:

SELECT * FROM GetAuthorsByState('CA')
SELECT * FROM GetAuthorsByState('XY')

How to get all the Tables with or without Primary Key Constraint in Sql Server?

SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
      AND type = 'U'

We can write a query like below to get all the Tables with Primary key constraint:

SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
      AND type = 'U'