DECLARE @SourceShipToID VARCHAR(20) IF (ISNULL(@SourceShipToID,''))='' SET @SourceShipToID ='NULLSourceShipToID' PRINT @SourceShipToID
Archive for the ‘SQL server’ Category
This article reproduce from http://searchsqlserver.techtarget.com/tip/Avoid-cursors-in-SQL-Server-with-these-methods-to-loop-over-records. The reason I reproduced this here because the original site requires you to register first before you can view the full article.
Many articles have beaten up on SQL Server cursors — database objects that manipulate data in a set on a row-by-row basis — and I want to add my name to the list of people who wish cursors had never been introduced. But, unfortunately, cursors are a fact of life. Problems with cursors include extending locks, their inability to cache execution plans and CPU/RAM overhead. Many T-SQL programmers and DBAs do not know how to successfully loop over records without the need for cursors. In this tip, I’ll share some alternatives to cursors that provide looping functionality.
Method 1: Temp table with identity column
In the first approach, we will use a temp table with an identity column added to allow for row-by-row selection. If you’re performing an INSERT/UPDATE/DELETE, be sure to use the explicit transactions. This vastly reduces the load on your log file by committing per loop, and it prevents huge rollbacks in the case of failure.
set nocount on declare @i int --iterator declare @iRwCnt int --rowcount declare @sValue varchar(100) set @i = 1 --initialize create table #tbl(ID int identity(1,1), Value varchar(100)) insert into #tbl(Value) select name from master..sysdatabases (nolock) set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work create clustered index idx_tmp on #tbl(ID) WITH FILLFACTOR = 100 /* Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times. */ while @i <= @iRwCnt begin select @sValue = Value from #tbl where ID = @i --begin tran print 'My Value is ' + @sValue --replace with your operations on this value --commit tran set @i = @i + 1 end drop table #tbl
Method 2: Temp table without ID
In the second approach, we use a temp table without an identity column and simply grab the top row to process, then loop until we find no more rows to process. If you’re performing an INSERT/UPDATE/DELETE, again, be sure to use the explicit transactions to vastly reduce the load on your log file by committing per loop, which prevents huge rollbacks in the case of failure.set nocount on declare @i int --iterator declare @iRwCnt int --rowcount declare @sValue varchar(100) set @i = 1 --initialize create table #tbl(Value varchar(100)) insert into #tbl(Value) select name from master..sysdatabases (nolock) set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work create clustered index idx_tmp on #tbl(Value) WITH FILLFACTOR = 100 /* Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times. */ while @iRwCnt > 0 begin select top 1 @sValue = Value from #tbl set @iRwCnt = @@ROWCOUNT --ensure that we still have data if @iRwCnt > 0 begin --begin tran print 'My Value is ' + @sValue --replace with your operations on this value --commit tran delete from #tbl where value = @sValue --remove processed record end end drop table #tbl
Method 3: Selecting a comma-delimited list of items
When most developers/DBAs are asked to come up with a list of comma-delimited values from a table, they typically use a cursor or temp table (as above) to loop through the records. However, if you do not need to use a GROUP BY or an ORDER BY, then you can use the method below that operates in batch to handle the task. This cannot be used with GROUP BY DISTINCT, or ORDER BY, because of how SQL Server handles those operations.
Basically, this takes a given variable, and for every row in the table it adds the current value to the variable along with a comma.declare @vrs varchar(4000) declare @sTbl sysname set @sTbl = 'TableName' set @vrs = '' select @vrs = @vrs + ', ' + name from syscolumns where id = (select st.id from sysobjects as st where name = @sTbl) order by colorder set @vrs = right(@vrs, len(@vrs)-2) print @vrs
This article gives you some good reasons why cursors in SQL Server should be avoided as well as some alternatives that give you looping functionality. Keep in mind that SQL Server is designed around batch processing, so the less you loop, the faster your system will run.
ABOUT THE AUTHOR
Matthew Schroeder is a senior software engineer who works on SQL Server database systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. He specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. Matthew is a Microsoft certified MCITP, Database Developer, has a master’s degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at firstname.lastname@example.org.
When you working with database one of most often problem you face is date time data. In this post I’m going to post code snippets that do different things with datetime data and links to the original posts!
Converting datetime value to a given format using built in SQL Sever functions
select convert(varchar, getdate(), 10) -- returns 12-30-06 select convert(varchar, getdate(), 1) -- returns 12/30/06 --Convert and replacing the divider select replace(convert(varchar, getdate(),101),'/','') -- returns 12302006
The source for this snippet http://www.mssqltips.com/tip.asp?tip=1145
During recent debugging of T-SQL stored procedures, I found adding following
code in your catch block will help to point the actual cause of error and make your programming life much easy!
Begin Try Print 'Do Something' End Try BEGIN CATCH ROLLBACK TRANSACTION print 'Error Msg: ' + ERROR_MESSAGE() print 'Error Procedure: ' + ERROR_PROCEDURE() print 'Error Number: ' + convert(varchar(50),ERROR_NUMBER()) print 'Error Line: ' + convert(varchar(50),ERROR_Line() END CATCH
I was looking a query that tell me a list of view using certain view from a table, and found the answer here.
See the code below. Hope this could help someone somewhere.
select view_name from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where column_name='ColumnNameHere' and table_name='TableNameHere'
I got this code snippet from my collegue , hope this can be helpful for others too
declare @dbname nvarchar(255) set @dbname = 'databasename' backup log @dbname with truncate_only DBCC SHRINKDATABASE (@dbname, 0)
Simple statement to delete record a table with condition from other related table.
delete prod from excp_amended_daily_sales_deposits prod inner join excp_amended_daily_sales ds on ds.id=prod.id where ds.status <> 'Active' and ds.reason_code='51'
delete hist_tbl_ASNHeaders from hist_ShipmentHeader,hist_tbl_ASNHeaders where hist_ShipmentHeader.ShipmentHeaderID=hist_tbl_ASNHeaders.fkShipmentHeaderID