[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
?
168 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 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