Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

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

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
nitayalevette
Asked:
nitayalevette
  • 8
  • 7
2 Solutions
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
nitayalevetteAuthor Commented:
RQuadling, didn't mean to tag MySQL thanks for the heads up
0
 
nitayalevetteAuthor Commented:
I've requested that this question be deleted for the following reason:

Wrong Zone
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Richard QuadlingSenior Software DeveloperCommented:
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
 
nitayalevetteAuthor Commented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
nitayalevetteAuthor Commented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
nitayalevetteAuthor Commented:
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
 
nitayalevetteAuthor Commented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
Ephraim WangoyaCommented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
@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
 
nitayalevetteAuthor Commented:
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
 
nitayalevetteAuthor Commented:
Ok, I missed the obvious in the concatenation...changed it to and everything work perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now