Variable length Char() parameter used as variable field name in SP

Hello,

I have a stored procedure that starts like this:

CREATE proc dbo.qryFleetRebateByMonth
@strYear  char(4),
@intDealershipID  int,
@strField  char(9)

AS

exec('SELECT
            tblRebateClaims_Jan.' + @strField + 'AM AS numAM_Jan,
            tblRebateClaims_Jan.' + @strField + 'DL AS numDL_Jan,

and which I call like this:

      objCommand.Parameters.Append objCommand.CreateParameter("@strYear",adVarChar,adParamInput ,4,"2005")
      objCommand.Parameters.Append objCommand.CreateParameter("@intDealershipID",adInteger,adParamInput , ,58)
      objCommand.Parameters.Append objCommand.CreateParameter("@strField",adVarChar,adParamInput ,9,strSomeVar
      Set objRS = Server.CreateObject ("ADODB.Recordset")
      objRS.Open objCommand,,3,3

and that works fine.

However, I want the @strField to be of any length, not just 9.

I have tried using a longer length and then using RTrim, but I have had no luck.

Any ideas?

Thank you very much,

Fritz the Blank
LVL 46
fritz_the_blankAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE proc dbo.qryFleetRebateByMonth
@strYear  char(4),
@intDealershipID  int,
@strField  varchar(9)

AS

exec('SELECT
          tblRebateClaims_Jan.' + @strField + 'AM AS numAM_Jan,
          tblRebateClaims_Jan.' + @strField + 'DL AS numDL_Jan,
fritz_the_blankAuthor Commented:
Two questions, please:

1) why do I need to specify a length? varchar(9)
2) when using the command object in a loop with parameters, do I need to do anything else?

for intArrayIndex=0 to UBound(arrFieldsGroup1)
      objCommand.Parameters.Append objCommand.CreateParameter("@strYear",adVarChar,adParamInput ,4,"2005")
      objCommand.Parameters.Append objCommand.CreateParameter("@intDealershipID",adInteger,adParamInput , ,58)
      objCommand.Parameters.Append objCommand.CreateParameter("@strField",adVarChar,adParamInput ,len(arrFieldsGroup1(intArrayIndex)),arrFieldsGroup1(intArrayIndex))
      Set objRS = Server.CreateObject ("ADODB.Recordset")
      objRS.Open objCommand,,3,3

I am receiving the error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Procedure or function qryFleetRebateByMonth has too many arguments specified.


Thank you very much!

FtB
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) why do I need to specify a length? varchar(9)
you actually specify a maximum length for the variable. you might increase up to 8000

2) when using the command object in a loop with parameters, do I need to do anything else?
you only need to specify (and append) the parameter once, and only set (change) the values as needed in the loop:

dim pYear as adodb.parameter
dim pDealership as adodb.parameter
dim pField as adodb.parametetr

set pYear = objCommand.CreateParameter("@strYear",adVarChar,adParamInput ,4,"2005")
set pDealership = objCommand.CreateParameter("@intDealershipID",adInteger,adParamInput , ,58)
set pField = objCommand.CreateParameter("@strField",adVarChar,adParamInput , 8000)  << let me assume you defined the procedure with 8000 as lenght for the parameter
     objCommand.Parameters.Append pYear
     objCommand.Parameters.Append pDealership
     objCommand.Parameters.Append pField

for intArrayIndex=0 to UBound(arrFieldsGroup1)
 pField.value = arrFieldsGroup1(intArrayIndex)
 Set objRS = Server.CreateObject ("ADODB.Recordset")
 objRS.Open objCommand,,3,3
 ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

fritz_the_blankAuthor Commented:
Thank you once again for posting.

I tried what you have above, and I am getting an error that I got earlier when I tried to call the SP in a loop:

Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/communications/reporting/sptest.asp, line 32


Line 32 = objRS.Open objCommand,,3,3


FtB
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that might get more tricky to solve...
does this occur on the first rs.open, or only on the subsequent loops?
fritz_the_blankAuthor Commented:
If I set the value to length 9 in both the parameter and the SP declaration and then pass a parameter with lenght 9, it works fine.

However, I can't get this to work outside of the loop even if I don't specify the exact length. I suspect that is because of this:

exec('SELECT
            tblRebateClaims_Jan.' + @strField + 'AM AS numAM_Jan,

....


If the @strField has any spaces on either side, the sql select will have a syntax error.

I don't suppose that SQL keeps a log of that somewhere?

FtB
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the procedure starts like this:

CREATE proc dbo.qryFleetRebateByMonth
@strYear  char(4),
@intDealershipID  int,
@strField  varchar(9)
AS

and the code is like this:
set pField = objCommand.CreateParameter("@strField",adVarChar,adParamInput , 9)  

the value of 9 is only the maximum length, and does not need to be the exact length.
However, if you have a field value of which the lenght would be higher than 9, then you might run into the error described.


fritz_the_blankAuthor Commented:
I am very sorry! I ommitted the code that appended the parameters, and that has solved the problem.

So, in review:

1) the precision on the varChar sets the upper limit
2) I need only change the value of the parameter in the loop rather than clear it.

Thank you for all of your help!

FtB
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>1) the precision on the varChar sets the upper limit
yes

>2) I need only change the value of the parameter in the loop rather than clear it.
absolutely
fritz_the_blankAuthor Commented:
Thank you once again. I am self taught, and there are many, many holes in my bacground. This isn't the first time you went out of your way to volunteer your time, and I really appreciate it.

FtB
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.