asked on

SQL Coding - SQL Server 2000

Hi. I wrote some VB code in an Access module that I am trying to convert to SQL so I can run it directly on the server. I am stuck on one thing and hope someone can help.

Here's my code in VB that works:
*************************
Do While i < 9
i = i + 1
If rs1("D" & cstr(i)) = True Then
thestring = thesample & "-" & CStr(i)
*************************
Here's what I have so far in SQL but it's not correct.

while @Dcount < 9 BEGIN
set @Dcount = @Dcount + 1
IF (@D + convert(varchar(5), @Dcount) = 1) BEGIN
Set @theString = thesample + ‘-’ + convert(varchar(5), @Dcount)
*************************
How do I write "D" & cstr(i) in SQL?
Right now I am getting an error: Must declare the variable '@D'.

I did declare the dynamic variables:
declare @D1 as bit, @D2 as bit, @D3 as bit, @D4 as bit, @D5 as bit, @D6 as bit, @D7 as bit, @D8 as bit, @D9 as bit

Does anyone have any ideas?

Thanks,
Alexis

Member_2_861731

What exactly is it that you want to do with this code? I think that the approach you're taking is not the most efficient one, since you're trying to convert a client side code to a server side code with the same logic.
Let us know exactly what you want to achieve and we might help you with a better solution.

Regards,

alexisbr

ASKER

Thanks for your reply. I am trying to convert data from one table into another so I can do reports based on the data in the second table. I'm not concerned right now about the most efficient solution as I can probably tweak the code once I get it working. And it's not looking through more than a few thousand records. I just need to structure the data in a different way for a report.

I have used this type of logic in VB, PHP and ASP and it works fine. I just don't know how to write the equivalent code in SQL as I haven't done that much straight coding in SQL. If you wouldn't mind telling me if this can be done, that would help. I have already spent over an hour trying to find this logic on the internet by looking up dynamic sql, increments, etc but have not been successful.

I can always post a separate question if the code I write is not efficient. This is going to be a process run from the server only and will not have a user interface.

Thanks,
Alexis

Member_2_861731

The problem is that in your vb code you have a recordset (I assume) rs1. You don't have that in you sql code, so I'd have to know exactly what you're trying to do with this recordset in order to provide you with an example of how it can be done.

From the looks of it you're evaluating different fields in the recordset for TRUE/FALSE, this could be done with a where clause in SQL, that's why we need to know more or less what you're trying to achieve with this query.

alexisbr

ASKER

I have records with data values and 9 bit values, D1 through D9. If the bit value is true, I want to insert data into another table and set a string value based on which bit values was used in the insert statement. I will give a similar, less complicated example about what I want to accomplish. The data itself will not make sense but it will show you how I need to the data to look in the second table.

Source table: MyData fields: name varchar(20), D1 bit, D2 bit
record 1: name = 'joe'
record 2: name = 'mary'

Destination table: ReportData field: newfield varchar(25)

If D1 is true in MyData, I want to create a new record in ReportData that has newfield = 'joe-1'
If D2 is true in MyData, I want to create a new record in ReportData that has newfield = 'joe-2'

Therefore, if joe's record in MyData has D1 = true and D2 = false, only one record would be inserted into ReportData that has newfield = "joe-1". If Mary's record has D1 and D2 both = true, then 2 records would be inserted into ReportData with "mary-1" and "mary-2". In this example, I would end up with 3 records inserted into ReportData.

Does this make sense?

Thanks,
Alexis

ASKER CERTIFIED SOLUTION

Member_2_861731

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexisbr

ASKER

Thanks, LionKing. I understand what you have done and am applying it to my tables/fields. I will get back to you.

alexisbr

ASKER

LionKing,
Thanks. Your logic worked but I did not use the UNION ALL lines. They were causing an error and I realized that they weren't necessary. The INSERT lines worked fine without the UNION ALL in both the Query Analyzer and the Stored Procedure that will be called from SQL Agent.

Thanks so much for your help. This is a different way of thinking for me and I will use your code for future reference.

Alexis

Member_2_861731

You're welcome!