An Output Parameter of a SP can be also used as input parameter
Did you know that you can pass a value to a stored procedure also using an output parameter of the SP. For example, lets create a SP with...
Read More
Tampilkan postingan dengan label T-SQL. Tampilkan semua postingan
Tampilkan postingan dengan label T-SQL. Tampilkan semua postingan
How to find maximum/minimum value across multiple columns in a single row
In some situations you need to find a maximum/minimum or average value from more than 2 columns in a table. We can use a subquery with UNI...
Read More
Conversion failed when converting the....
When using a CASE statement you should pay attention to the data types of the return values. They all should be implicitly convertible or ...
Read More
Option (Recompile) inside of a if exists statement
Suppose you have a bad query plan of a query in "If Exists" statement. One way to fix it is to tell to the SQL Server to recompil...
Read More
Declaring variable inside 'IF' statement
If you declare some variable inside IF statement and the code inside the IF statement is not going to be executed, can you use the declare...
Read More
Convert FLOAT to VARCHAR
If you try to convert a float variable to varchar by using CAST or CONVERT, you will get an error message: DECLARE @Float FLOAT SET @...
Read More
An Update query that doesn`t work
Why the following UPDATE doesn`t work, I asked my colleague. And did not get any answer. Consider the following example, you have a table...
Read More
Local Variables and Transaction (or All at Once Concept)
Let me start with the example of Order of variable assignment in SELECT statement: DECLARE @a int ,@b int SET @a=1 SET @b=0 SELECT @...
Read More
No more soup for you! - Cool SQL Error
I had situation, where we wanted to see how the SQL Server can deal with high number of items in the IN clause, For instance, we had someth...
Read More
Creating SP in Master database
So you want to create a stored procedure in the master database in order to be able to access it from any database in the server without spe...
Read More
.jpg "SELECT...INTO is faster than CREATE & INSERT")
SELECT...INTO is faster than CREATE & INSERT
Do you use SELECT..INTO in your code / SPs? Or you prefer to create a new table and then use INSERT INTO? Anyway, you should be awa...
Read More
Report progress from a batch/script/stored procedure
There are situations when you have to update/delete high volume of data in a table(s).For example, moving data to another database (archiv...
Read More
How to drop extended properties of all columns in a table
When synchronizing database schema between DBs, you may also need to synchronize the extended properties . Dropping extended propert...
Read More
Search for a string in SQL Agent Job Steps
When you change schema of tables or when you change a SP, you probably need to to find out whether the SP used in SQL Server Job(s) or...
Read More
Order of Joins, FORCE ORDER Hint
Well, as you may know SQL Server can build the execution plan with different order of joins than in your query. This is done as part of th...
Read More
Interesting index option IGNORE_DUP_KEY
In one of migration scripts, we needed to create a table to hold distinct values. In the population process, there was no option to speci...
Read More
@@Rowcount and Aggregative functions
Please note the following, When using aggregative function(s) in a SELECT that not returns any row you will always get one row in the res...
Read More
How to debug/troubleshoot a SP in production environment?
There are times when you need to debug some stored procedure(s) in Production environment or to make a SP to behave differently when...
Read More
@@Trancount in DML statement
I was debuging something related to transactions and saw something strange from first sight. You know that @@TRANCOUNT returns the n...
Read More
CHECKSUM and OPTION(RECOMPILE)
Working with CHECKSUM functions, I encountered a situation when the result of the CHECKSUM function is effected by OPTION(RECOM...
Read More
Langganan:
Postingan (Atom)