?
Solved

Best way to have a stored procedure add 1 to a value and stop at a certain number

Posted on 2011-09-07
17
Medium Priority
?
166 Views
Last Modified: 2012-05-12
I have a procedure and I need to take a column name and increment by 1 until say number "10".  What is the best way to complete this task?

Syntax

select A, B, C, FROM tab where D1 IS NOT NULL

I need to create some routine that will take the column "D1" and increment by "1" until the it reaches D10

Thanks Experts
0
Comment
Question by:nitayalevette
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
17 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36496935
The SQL
UPDATE tab SET D1 = 1 + ISNULL(D1, 0) WHERE D1 < 10

Open in new window

would work in MSSQL.

You tagged SQL Server 2008 R2 and Zone MySQL Server.

VERY different servers.
0
 

Author Comment

by:nitayalevette
ID: 36496957
RQuadling, didn't mean to tag MySQL thanks for the heads up
0
 

Author Comment

by:nitayalevette
ID: 36497016
I've requested that this question be deleted for the following reason:

Wrong Zone
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36497083
Or are you talking about only getting n rows?

SELECT TOP 10 ...


Or are you wanting to number the rows?

SELECT TOP 10 ROWNUMBER(), ....

0
 

Author Comment

by:nitayalevette
ID: 36497105
Incrementing the table name by 1 until the procedure completes.  Where D1 IS NULL and the next set of statements will say where D2 IS NULL and so on...

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36497111
Sorry. Bad example.

SELECT TOP 10
	ROW_NUMBER() OVER(ORDER BY [Date In]) Row,
	[Date In],
	[Casing ID],
	Size
FROM
	vwMerlinCasings

Open in new window


will select the top 10 rows order by the Date In column, and show a row number per row.

Row	Date In	Casing ID	Size
1	2009-08-12 00:00:00.000	900942404780033	295/80R22.5
2	2009-08-12 00:00:00.000	900942404780034	295/80R22.5
3	2009-08-12 00:00:00.000	900942404780035	295/80R22.5
4	2009-08-12 00:00:00.000	900942404780037	295/80R22.5
5	2009-08-12 00:00:00.000	900942404780038	295/80R22.5
6	2009-08-12 00:00:00.000	900942404780039	295/80R22.5
7	2009-08-12 00:00:00.000	900942404780040	295/80R22.5
8	2009-08-12 00:00:00.000	900942404780041	295/80R22.5
9	2009-08-12 00:00:00.000	900942404780042	295/80R22.5
10	2009-08-12 00:00:00.000	900946699747329	295/80R22.5

Open in new window

0
 

Author Comment

by:nitayalevette
ID: 36497124
with example number 2, if I where using the fault columns, I would to increment by 1 each time in my procedure therefore the first select would say

where fault1 IS NULL

the second select would say

where fault2 IS NULL

and so on
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36497130
Ah. Column name, not value.

Interesting.

Do to that you have to use something called dynamic SQL.

In essence, you use SQL to build an SQL statement and then execute the built SQL statement.

I'm really not a big fan of these.

Can you give some more detail about this? Something that I could use to find a cleaner alternative?
0
 

Author Comment

by:nitayalevette
ID: 36497175
I'm not sure what additional clarity I can provide, I'm currently attempting to include this in a stored procedure.  My initial thought was to try and declare the column as a variable in a increment by 1; however I stuck on how to write the syntax.  
0
 

Author Comment

by:nitayalevette
ID: 36497431
I've came up with a way to complete the task...may not be the best, but now I'm tesitng it for performance...


DECLARE @columnName VARCHAR(20) = 'Column'
DECLARE @i INT = 1
DECLARE @sql VARCHAR(100) = ''

WHILE @i <= 10
BEGIN
 SET @sql = @sql + @columnName + CAST(@i as VARCHAR(20)) + ' '
 SET @i =  @i+1
END

PRINT @sql
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36497634
What you are looking for is termed "dynamic SQL".

This involves using SQL to build a string that contains the SQL statement you want to execute.

It isn't ideal.

Are you trying to find the first non null value?

How about...

SELECT COALESCE(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10) FROM tab

Open in new window


0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36497682
I'm not sure how you intend to control the increment?

Is it once per invocation of stored procedure? If so, you need to hold the last count somewhere.

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1800 total points
ID: 36499882
try
declare @colname varchar(10), @sql varchar(max), @i int

set @sql = 'select A, B, C, FROM tab where ' + char(13)

set @i = 1
while @i <= 10
begin
  if @i = 1 
    set @sql = @sql + ' WHERE COL' + CAST(@i as varchar) + ' IS NOT NULL ' + char(13)
  else
    set @sql = @sql + ' OR COL' + CAST(@i as varchar) + ' IS NOT NULL ' + char(13)  
   
 set @i = @i + 1;   
end

exec(@sql)

Open in new window

0
 
LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 200 total points
ID: 36501501
@ewangoya, drop the 'where' on line 3.


If you want to select A, B and C when all of the other columns are null ...
SELECT A, B, C FROM tab WHERE COALESCE(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10) IS NULL

Open in new window


or if you want to select A,B and C when at least one of the other columns are NOT null ...
SELECT A, B, C FROM tab WHERE COALESCE(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10) IS NOT NULL

Open in new window


As the number of columns is fixed, I'd go with the COALESCE and hard code the names.

And pretty much because I've seen some horror stories regarding dynamic SQL.

For information on COALESCE, see COALESCE (Transact-SQL).

Specifically the return value
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.
0
 

Author Comment

by:nitayalevette
ID: 36504660
ewangoya I'm testing your code; however I getting an error near the WHERE clause.  

set @sql = 'SELECT
                        *
                  FROM
                        Testtable
                  WHERE Number=''1936''
                  ' + char(13)

0
 

Author Comment

by:nitayalevette
ID: 36505228
Ok, I missed the obvious in the concatenation...changed it to and everything work perfectly!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question