String Id Comma Separate Find Query logic

String Id Comma separate Query logic

For example : ProductCategoryId has "1,5,7,9,11,16" multiple id
                       Find/Search 5 in  ProductCategoryId ?

Query :

And charIndex(',' + rtrim(cast(IsNull(Items.ProductCategoryId,0) as nvarchar(max))) + ',',',' + isnull(@ProductCategoryId,IsNull(Items.ProductCategoryId,0)) +',') >0

Sql Query : Cross Apply Over the Inner Join

//Inner Join
WITH t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT   
  t1.*, t2o.*
FROM    t1
INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn <= 3 //Record fillter after join
SELECT t1.* FROM sys.objects t1
INNER JOIN myFunction(O.name) F ON F.schema_id= O.schema_id  //NOT USE FUNCTION : gettin error in inner join

//Cross Apply
SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY // First fillter record then join is done --Result Incres speed
        (
         SELECT  TOP 3 * FROM    t2
  WHERE   t2.t1_id = t1.id
         ORDER BY
                t2.rank DESC
        ) t2o
SELECT F.* FROM sys.objects O
CROSS APPLY //Not Error
 (
  SELECT * FROM myFunction(O.name) WHERE F.schema_id= O.schema_id

Select statement with NULL and Find No. of the character from string

Some of the rows in the Customers table have a NULL in the region column. For those, SQL Server returns by default a NULL in the location result column:

custid      country         region city            location
----------- --------------- ------ --------------- -------------------
1           Germany         NULL   Berlin          NULL
2           Mexico          NULL   México D.F.     NULL
3           Mexico          NULL   México D.F.     NULL
4           UK              NULL   London          NULL
5           Sweden          NULL   Luleå           NULL
6           Germany         NULL   Mannheim        NULL
7           France          NULL   Strasbourg      NULL
8           Spain           NULL   Madrid          NULL
9           France          NULL   Marseille       NULL
10          Canada          BC     Tsawassen       Canada,BC,Tsawassen
11          UK              NULL   London          NULL
12          Argentina       NULL   Buenos Aires    NULL
13          Mexico          NULL   México D.F.     NULL
14          Switzerland     NULL   Bern            NULL
15          Brazil          SP     Sao Paulo       Brazil,SP,Sao Paulo
16          UK              NULL   London          NULL
17          Germany         NULL   Aachen          NULL
18          France          NULL   Nantes          NULL
19          UK              NULL   London          NULL
20          Austria         NULL   Graz            NULL
...

(91 row(s) affected)

You can change the way SQL Server treats concatenation by setting a session option called CONCAT_NULL_YIELDS_NULL to OFF. SQL Server treats a NULL set to OFF as an empty string for concatenation purposes. To demonstrate this behavior, run the following code to set the option to OFF, and then rerun the query in above result

SET CONCAT_NULL_YIELDS_NULL OFF;

----------------------------------------------------------------------------------

Find No. of the character  from string

SELECT empid, lastname,
  LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur
FROM HR.Employees;

Sql Server

A FULL OUTER JOIN is neither "left" nor "right"— it's both!

SELECT
* FROM
@tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NULL AND tblTarget.ID IS NOT NULL

check null to fillter rows that in both table.

RAISERROR with Log

DECLARE @ERROR_NUMBER INT

DECLARE @ERROR_SEVERITY INT

DECLARE @ERROR_STATE INT

DECLARE @ERROR_PROCEDURE NVARCHAR(252)

DECLARE @ERROR_LINE INT

DECLARE @ERROR_MESSAGE NVARCHAR(4000)

SELECT @ERROR_NUMBER = ERROR_NUMBER() , @ERROR_NUMBER = ERROR_NUMBER() , @ERROR_SEVERITY = ERROR_SEVERITY() , @ERROR_STATE = ERROR_STATE() , @ERROR_PROCEDURE = ERROR_PROCEDURE() , @ERROR_LINE = ERROR_LINE() , @ERROR_MESSAGE = ERROR_MESSAGE()

EXEC CPV.uspInsertSQLErrorLog @ERROR_NUMBER , @ERROR_SEVERITY , @ERROR_STATE , @ERROR_PROCEDURE , @ERROR_LINE , @ERROR_MESSAGE

RAISERROR(@ERROR_MESSAGE,@ERROR_SEVERITY,@ERROR_STATE) /******Raise Error backto client *****/