variable field names in a stored proc

Posts: 101 · Posted: Thu Oct 28, 2004 5:03 pm

I'm trying to develop a stored proc that can accest a variable as a field name. the table i am using has about 50 bit fields that i need to select from.
see attached code:

thanks for your help
kes

Code:

CREATE PROCEDURE [usr].[sp_getUniverse]
@sectlbl    varchar(15)
AS

select
csi_csiSym,
csistk1,
csiExchange,
csiCompany,
@sectlbl
   from csi
   where @sectlbl = 1
GO

[/code]

Posts: 14 · Posted: Thu Nov 18, 2004 4:22 am

While I don't have the code handy, you're basically going to have to create a string piecing together the hard coded text and values from your variable(s) and then execute the string. Look for dynamic sql or the prepare statement in books online.

Posts: 101 · Posted: Thu Nov 18, 2004 2:55 pm

Thank You that gets me in the right direction.

Posts: 18 · Posted: Mon Nov 22, 2004 9:09 pm

Just a thought on this. Instead of 50 bit fields why not a varchar(50) and store the values as all one string.

Example:

00100101011101101001000010100111001011001000010011

Each character index holds the value of what those fields would have been. Less fields to mess with, seems easy. I'm interested to see how you all feel about that. Have you done it? Compared performance?

Posts: 5 · Posted: Tue Dec 28, 2004 8:57 am

Each character in a varchar (or char) field requires at least full byte (8bits) of storage space.
A bit field is .... well .... a bit.
So a 50 character varchar occupies 8 times the space of 50 bit fields.

Also, SQL is much faster at accessing the value in a specific bit field than it is at pulling a 50 character text value, locating the 23rd character and reporting if it is a "1" or a "0".

Incidentally, an alternative to the constructing of a text command would be to use a case statemtment to return the field of choice:

Code:

CASE @sectlbl
when 'BitFld1' then [BIT1]
when 'BitFld2' then [BIT2]
when 'BitFld3' then [BIT3]
when 'BitFld4' then [BIT4]
...
else 0 end as [BitFieldValue]