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
157 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:RQuadling
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
 
LVL 40

Expert Comment

by:RQuadling
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:RQuadling
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:RQuadling
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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:RQuadling
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:RQuadling
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:RQuadling
RQuadling 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now