Solved

Have SQL Loop a column then pass those values with commas to a variable for use as a clause.

Posted on 2008-06-25
12
194 Views
Last Modified: 2010-03-20
What I need:

I need SQL to loop through a column in my database (sortfield)
I need SQL to pass the values that are returned to a variable and add commas behind each value
I need SQL to place the values into a where clause

Example:

Invoice Table:
IDNum      Sortfield
11             1
12             2
14             3

@sortfield will become '1', '2', '3'
my where clause will look like: where sortfield in ('1', '2', '3')

declare @sortfield

set @sortfield ='All'
 

If (@sortfield)='All'

begin

set @sortfield='Number1'+, +'Number2'+, +'Number3'+, +'etc...

end
 

Select * from invoice where sortfield in (@sortfield)

Open in new window

0
Comment
Question by:stephi01
  • 6
  • 5
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
why not use this:

select * from tablename where sortfield in(select sortfield from someothertable)
0
 

Author Comment

by:stephi01
Comment Utility
i need the query to loop through the invoice table and pull data based off of the sortfield numbers in that same table. The sortfields aren't refrenced in the same way in another table.

0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
I am not sure I understand.  The invoice table has a field named sortfield that you want to filter on.  You want to filter these values based on the sortfield values in another table, right?  What is the name of the table and the name of the field?
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
So where do the sortfields come from? In your example they appear to be hard-coded, which doesn't make much sense to me.

Maybe this helps: http://www.sommarskog.se/arrays-in-sql-2005.html
0
 

Author Comment

by:stephi01
Comment Utility
I am only using one table. The table is called invoice. Everytime a new invoice is created for a file it is assigned an id number and a sortfield. (the sortfield notates in which order the sees the invoice in the GUI of our software). I have a document that loops through the invoice table for that file and prints a page for each invoice. I am trying to allow the user to isolate a specific invoice to print and have gotten that far. I now need to include logic that allows the user to print all invoices if nessicary. I figure that if i can have the user input the word all i can force the procedure perform the above and pull a page for each row.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
Ok, I understand a bit better now.  Try this:

declare @sortfield
set @sortfield ='All'
 
select * from invoices
where sortfield = case when @sortfield = 'ALL' THEN sortfield else @sortfield END
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:stephi01
Comment Utility
The sortfield column is set as int.
I get this message when I run the above.


Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'A' to data type int.
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
what values are in the variable @sortfield?
0
 

Author Comment

by:stephi01
Comment Utility
it can vary... it will be either a number or the word "All".
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
one single valued number or a string of numbers?
0
 

Author Comment

by:stephi01
Comment Utility
I got this to work by using

declare  @uidnum varchar (3), @Sort int


set @uidnum='3'

if @uidnum='All'
begin
set @sort=0
end
else
if @uidnum <>'All'
begin
set @sort=@uidnum
end

select * from invoice
where sortfield= case when @sort=0 then sortfield else @uidnum end
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
Good deal.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

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

10 Experts available now in Live!

Get 1:1 Help Now