Category Archives: SQL

Function Vs Stored Procedure

Difference between Function and Stored Procedure Functions Compiled and executed at Run time. Can not affect the Database state. i.e Can not perform insert,delete and update operation. Can return 1 value which is mandatory. Can have Input parameters only. Function … Continue reading

Posted in SQL | Tagged , , , , | Leave a comment

Microsoft Enterprise Library for Database

What is EnterPrise Library? Microsoft Enterprise Library is a collection of reusable application blocks designed to assist software developers with common enterprise development challenges. It includes: Data Access Application Block Cryptography Application Block Caching Application Block Exception Handling Application Block … Continue reading

Posted in ASP.NET, SQL | Tagged , , , , , | Leave a comment

PLSQL CASE query examples

–Case Query in Sql: UPDATE [account] SET balance =(CASE WHEN ((balance – 10.00) < 0) THEN 0 ELSE (balance – 10.00) END) WHERE id=1 SELECT productId, ‘status’ = CASE WHEN quantity > 0 THEN ‘in stock’ ELSE ‘out of stock’ … Continue reading

Posted in SQL | Tagged , , , , , | Leave a comment

Auto Increment Key Example

CREATE TABLE [dbo].[complaint] ( [complaint_ID] [nvarchar](50) NOT NULL, CONSTRAINT [PK_complaint] PRIMARY KEY CLUSTERED([complaint_ID]ASC) ) /****** Object: StoredProcedure [dbo].[autokeygeneration] Script Date: 10/12/2011 17:49:18 ******/ –select isnull(complaint_ID,null) from complaint –exec autokeygeneration ’09’,’Jan’,’03’ –select * from complaint –print substring(’09Feb020001′,6,2) CREATE procedure [dbo].[autokeygeneration] ( … Continue reading

Posted in SQL | Tagged , , , | Leave a comment

DELETE duplicate rows from table

–Delete All Duplicate Records: DELETE FROM DUP_TBL WHERE id1 IN (SELECT id1 FROM DUP_TBL GROUP BY id1 HAVING COUNT(id1) > 1) OR –DELETE Duplicate Records WITH temp AS (SELECT id1,name1, row_number() over(partition by id1 order by id1) as duplicates from … Continue reading

Posted in SQL | Tagged , , , , | Leave a comment

Get a comma separated string

DECLARE @strCommaSeperated VARCHAR(MAX) –Make comma seperated values from a column SET @strCommaSeperated = SUBSTRING((SELECT ‘,”’+ s.EntityName+”” FROM dbo.Entity s FOR XML PATH(”)),2,20000) SELECT @strCommaSeperated Selecting Comma Separated orderID from OrderDetails Table DECLARE @MyOrderList VARCHAR(MAX); SET @MyOrderList = ” SELECT @MyOrderList … Continue reading

Posted in SQL | Tagged , , , | Leave a comment

SQL DATE QUERIES

— Midnight of Current Day SELECT dateadd (dd, 0, datediff (dd, 0, Getdate ())) — Midnight of last Friday (or whichever day). /*The day of the week of date entered determines the result. Pick a Friday, any Friday, and you … Continue reading

Posted in SQL | Tagged , , , , | Leave a comment