asked on

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

Guy Hengel [angelIII / a3]

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_blank

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

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

Start Free Trial

fritz_the_blank

ASKER

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]

that might get more tricky to solve...
does this occur on the first rs.open, or only on the subsequent loops?

fritz_the_blank

ASKER

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]

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_blank

ASKER

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]

>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_blank

ASKER

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