July 10, 2008 at 5:03 pm
Greetings,
The following stored procedure returns " No Column Name " row? with a record in it.
I'm trying to figure out where in this code it is making the result come up as a record for " ( No Column Name ) ".
Thanks!
USE [CustomerDB]
GO
/****** Object: StoredProcedure [dbo].[spRONNIE] Script Date: 07/10/2008 16:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spRONNIE] --Gets the key code for active products for the site
(
-- input variables
@siteid int,
-- output variables
@KeyCode varchar(5) output
)
AS
begin
--testing
--declare @siteid int
--declare @KeyCode varchar(5)
--set @SiteID = 1
--end testing
set @KeyCode = ''
select @KeyCode = isnull((left(pa.keycode, 1) + cast(sum(cast(substring(pa.keycode,2,3) as int)) as varchar(5))),'')
from productattributes as pa
where pa.productid in(
select pa.productid
from bundlelist as bl join bundleproducts as bp on (bl.bundleid = bp.bundleid)
join productattributes as pa on (bp.productid = pa.productid)
join products p on pa.productid = p.productid
where siteid = @siteid and bl.status = 1 and bl.bundletype in (1,3,4) and pa.productid <> 1205 and p.fileformat in (4,5)
union
select pa.productid
from productlist as pl join productattributes as pa on (pl.productid = pa.productid)
where siteid = @siteid and pl.status = 1 and pl.producttype = 1 and pa.productid <> 1205)
group by left(pa.keycode, 1)
select ((left(pa.keycode, 1)) + cast(sum(cast(substring(pa.keycode,2,3) as int)) as varchar(5)))
from productattributes as pa
where pa.productid in(
select pa.productid
from bundlelist as bl join bundleproducts as bp on (bl.bundleid = bp.bundleid)
join productattributes as pa on (bp.productid = pa.productid)
join products p on pa.productid = p.productid
where siteid = @siteid and bl.status = 1 and bl.bundletype in (1,3,4) and pa.productid <> 1205 and p.fileformat in (4,5)
union
select pa.productid
from productlist as pl join productattributes as pa on (pl.productid = pa.productid)
where siteid = @siteid and pl.status = 1 and pl.producttype = 1 and pa.productid <> 1205)
group by left(pa.keycode, 1)
end
return
July 10, 2008 at 5:23 pm
The first line in the last query needs a name.
select ((left(pa.keycode, 1)) + cast(sum(cast(substring(pa.keycode,2,3) as int)) as varchar(5))) AS SomeName
The first query sets the variable @keycode, but I do not see where you are using it.
July 10, 2008 at 5:31 pm
Ken,
After posting this i figured that out...
The weird part, the website guys are using some sort of php script to connect and obtain the infomation from me.
If i give it to them in a @blah then they cannot get the results, if I do it without assigning it to a @blah and the no column name apears then they get their results?
July 10, 2008 at 5:39 pm
It depends on how they are capturing the output. They may not be expecting an output parameter.
July 11, 2008 at 8:52 am
rsheppick (7/10/2008)
Ken,After posting this i figured that out...
The weird part, the website guys are using some sort of php script to connect and obtain the infomation from me.
If i give it to them in a @blah then they cannot get the results, if I do it without assigning it to a @blah and the no column name apears then they get their results?
Just means they're looking at the recordset it returns, instead of at the output parameter. Two different ways to get data from a proc. (They may or may not know how to get an output parameter. Some don't.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply