Error While Running My StoredProc

[mickeyj2]

Hi,

Can somebody help me out and tell me what I'm doing wrong here?

I have an in-line query that I'm converting into a stored procedure that takes one parameter.

The in-line query worked just fine in my application.

Here it is:

SELECT b.*, pc.categoryname, psc.subcategoryname, ff.*
FROM Board b
left join ProductCategory pc on b.CategoryID = pc.CategoryID
left join ProductSubCategory psc on b.SubCategoryID = psc.SubCategoryID
left join FormFactor ff on psc.SubCategoryID = ff.SubCategoryID
WHERE b.BoardName = 'APEX-344'

Here's that same query as a stored procedure that I've saved in my SQL Server database:

ALTER PROCEDURE [dbo].[GetBoard]
-- Add the parameters for the stored procedure here
@boardname nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
/* */
SELECT b.*, pc.categoryname, psc.subcategoryname, ff.*
FROM Board b
left join ProductCategory pc on b.CategoryID = pc.CategoryID
left join ProductSubCategory psc on b.SubCategoryID = psc.SubCategoryID
left join FormFactor ff on psc.SubCategoryID = ff.SubCategoryID
WHERE b.BoardName = '@boardname'

END

Here's the procedure call in my ColdFusion where I pass it the parameter.

<cfstoredproc procedure="GetBoard" datasource="#dsn#">
<cfprocparam CFSQLType = "CF_SQL_VARCHAR" variable = "boardname" value="APEX-344">
<cfprocresult name="result" resultset="1">
</cfstoredproc>
<cfdump var="#result#">

I am able to get my resultset dumped, but when I make a statement like:

<cfset session.BoardID = GetBoard.BoardID> or

<cfset session.BoardID = result.BoardID>

I get the following error:
Element BOARDNAME is undefined in GETBOARD.

I know that Boardname exists in my resultset.

Also, when I execute the SP in SQL Server, it runs successfully.

All I'm trying to do is use the results of that SP in the reset of my application. By the way, I've changed the datatype in my SP from varchar to nvarchar and back to no avail, and I also removed the quotes from @boardname, etc.

Please help.

Thanks in advance.

mickeyj2

 

[overmodulation]

Take out the quotes for @boardname in the stored proc. Since you specify the datatype in your cfprocparam you shouldn't need single quotes around the db variable.

Also, use dbvarname="@boardname" instead of variable="boardname".

That's happened to me before too..

 

[mickeyj2]

Hi overmodulation,

Thanks for the feedback. I did what you recommended, but I'm still getting that same message. Element BOARDNAME is undefined in GETBOARD.

Are they talking about in my stored procedure? Do I have to spell out each and every column?

Also, is this ok in my app? I'm trying to use the result?

<cfset session.boardid = GetBoard.BoardID>

Also, what about

<cfset session.boardid = result.BoardID>?

I'm really starting to get irritated. This should be a basic call. I really can't see the needle in the haystack. I've checked my datatypes, etc.

Thanks for any help.

mickeyj2

 

[overmodulation]

GetBoard is the name of your proc so that's not a variable. You're returning a query called result so that's what you should try to access elements from..

Does it say the same thing when you try to get #result.BoardID#?

 

[mickeyj2]

Yes, that's actually what I have now. Weird.

 

[mickeyj2]

Hey, I figured it out. User error of course. I named my SP the old name of my inline SQL and I was still referencing the old inline SQL name in my code instead of referencing "result". Sorry for the confusion. Thanks a bunch for your help!

 

[overmodulation]

Doh!

Been there done that.