Welcome to ChaudharyTechBlog

C ursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row. Life Cycle of Cursor Declare Cursor A cursor is declared by defining the SQL statement that returns a result set. Open A Cursor is opened and populated by executing the SQL statement defined by the cursor. Fetch When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation. Close After data manipulation, we should close the cursor explicitly. Deallocate Finally, we need to delete the cursor definition and released all the system resources associated with the cursor. Syntax to Declare Cursor Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The b...

EXEC sp_configure 'show advanced options' , 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries' , 1 RECONFIGURE GO

Whether you're bulk loading data or connecting to an OLE DB data source, OPENROWSET is a handy tool for retrieving data. Find out how to use the OPENROWSET function for SQL Server and Microsoft Access. There may be times when you'll want to run an ad hoc query that retrieves data from a remote OLE DB data source... or bulk loads data into a SQL Server table. In such cases, you can use the OPENROWSET function in Transact-SQL to pass a connection string and query to the data source in order to retrieve the necessary data. You can use the OPENROWSET function to retrieve data from any data sources that support a registered OLD DB provider, such as a remote instance of SQL Server or Microsoft Access. If you're using OPENROWSET to retrieve data from a SQL Server instance, that instance must be configured to permit ad hoc distributed queries. To configure the remote instance of SQL Server to support ad hoc queries, use the  sp_configure system stored procedure to ...

One of the blog followers asked this question “The code written by my colleague has WHERE 1=1. What does it mean?” Well. You have seen people using it when they dynamically construct WHERE condition based on the input values. Let us create this dataset CREATE TABLE #t (emp_name VARCHAR(100), experience INT, specialist VARCHAR(100)) INSERT INTO #t SELECT 'Pinal',12,'SQL' UNION ALL SELECT 'John',10,'JAVA' UNION ALL SELECT 'Sudhan',3,'SQL' Suppose you want to use two parameters and pass values for them in such a way that if the value is null, you need to skip the comparison Check the following code DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100) SET @sql=' where 1=1' SET @sql = @sql+CASE WHEN @experience IS NULL THEN '' ELSE ' and experience='''+CAST(@experience AS VARCHAR(100))+'''' END SET @sql = @sql+CASE WHEN @specialist IS NULL THEN '' ELSE ...

“Interact in a way that makes you feel competent and effective, not helpless and overly dependent. Stand up for yourself, your beliefs and opinions; follow your own wise mind.” 1. Accept responsibility. 2. Show consideration, courtesy, & politeness. 3. Think win/win. 4. Choose what you say rather than say what you choose 5. Don’t criticize & complain. 6. Smile & be kind. 7. Put positive interpretation on other people’s behavior. 8. Be a good listener. 9. Live while you are alive. 10. Give honest & sincere appreciation. 11. When we make a mistake, we should accept it immediately & willingly. 12. Discuss but don’t argue 13. Don’t gossip. 14. Turn your promises into commitments. 15. Be grateful but do not expect gratitude 16. Be dependable & practice loyalty. 17. Avoid bearing grudge. 18. Practice honesty, integrity & sincerity. 19. Practice humility. 20. Be understanding & caring. .21. Practice courtesy on a daily...