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
162 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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:
ewangoya earned 450 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 50 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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