Using Select * or a dynamic SQL statement

[pmcdaniel]

We receive feeds where some columns have names but the rest go from field2, field3, field4....etc, field230. Not all these fields are filled and the number of filled fields is not known ahead of time.

But we use all fields that contain data!

To avoid the "Select *" I created a dynamic SQL statement and I create the Select part of the query in a loop such as:

WHILE @Counter < 230
BEGIN
  SET @Fields = @Fields + 'Field' + CAST(@Counter AS VARCHAR(3)) + ', '
  SET @Counter = @Counter + 1
END

SET @Fields = SUBSTRING(@Fields, 1, (LEN(@Fields) - 1))

Then the Select statement includes all fields and I go on to the build the rest of the query dynamically.

From what I know there are advantages and disadvantages for Select * and dynamic SQL but considering my situation I am not sure if using dynamic is correct. Since I need all the fields maybe I should just use "Select *"?

Can someone please share with me which I should use in this situation and why so I can create the correct stored procedure? This query also uses a UNION.

Much thanks!

 

[hmckillop]

A couple of points and pointers

1 - I wouldnt use dynamic sql just because you dont want to type in the field list, not good practice and potential performance issues
2 - Using Select * is an issue particularly where you are using an application to call the stored proc. If a the schema changes, your application will also need to change, where if you name you fields in resultset, additions will have no impact.

3- if you need to generate the select statements, you can right click in enterprise manager and generate a select statement, this will populate all fields in the table.
4- alternative approach is to use excel's functionality to create select statement (a bit more roundabout, but quicker than typing each field

I would need a bit more info to understand your whole objective,

"I'm living so far beyond my income that we may almost be said to be living apart

 

[pmcdaniel]

Thanks for the prompt reply, hmckillop.

The feeds are put into a table from a txt file. The table consists of named fields but then go to field2, field3, field4....and so no to field230. I do not need to populate a table, I'm selecting from a table. I think my original description was a little confusing.

The loop I built in the original example creates the Select part to include the fields. I am not typing them all in the query.

I have to Select the fields based on an EntryDate and RequestNum. I am also using some of the info in the table to join with another table to order some documents we're creating.

I hope this helps.

 

[hmckillop]

Ok, I may need more info here, or may have completely missed the point, but here goes.

Based on the EntryDate and RequestNum, you will select certain fields of information back to the application e.g. if entrydate > today and requestnum = 5 then return field1, field2 and field3 otherwise return field4 field5 and field6?
And also based on this info you need to join with a different table, e.g. in first data scenario join with TableB otherwise join with TableC

not sure if this is what you want, but if you need to do that you can do this with a case statement and a conditional join. (Note its probably best to ensure fields are of same type if used by an application and you may need convert statements)


e.g.

select
case when entrydate > getdate() and requestnum = 5 then field1
else field4 end as MyFirstReturnField,
case when entrydate > getdate() and requestnum = 5 then field2
else field5 end as MySecondReturnField,
case when entrydate > getdate() and requestnum = 5 then field3
else field6 end as MyThirdReturnField,
--Anyother common fields
FROM MyTable tb1
LEFT OUTER JOIN TableB b on b.Joinfield = tb1.field1
LEFT OUTER JOIN TableC c on c.Joinfield = tb1.field4
WHERE b.Idfield is not null and c.idfield is not null --ensures only matches are returned

I may be well off the beaten track with this but is this what you need?



"I'm living so far beyond my income that we may almost be said to be living apart

 

[pmcdaniel]

No, that's not but I appreciate your assistance and through all this I figured out what to do. Since I have a loop creating the the fields to grab I simply printed the dynamic Select statement instead of executing it. This provided me with the exact query I needed and I didn't have to use a "Select *" and I didn't have to use dynamic SQL either. All I had to do was replace some of the variables and I had my query.

Thanks again for your help.