Sored Proc + return values

[vlitim]

I am using the following sp:

CREATE PROCEDURE sp_matchSkus
(
@newName varchar(255)
)
AS

DECLARE @mystr varchar(500)
DECLARE @ok int

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@newName+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
SELECT @mystr='UPDATE '+@newName+' SET skuID= (SELECT CAPRICE.dbo.VIPS_products.id FROM CAPRICE.dbo.VIPS_products WHERE sku = dbo.'+@newName+'.sku)'
exec (@mystr)

SET @ok=1
SELECT @ok as 'ok'
end
else
begin
SET @ok=0
SELECT @ok as 'ok'
end
GO


with the following asp:

SQL="sp_matchSKUS '"&user("upload_dir&quot&"_"&filename&"_REQ'"
set disti=posConn.execute(SQL)
If disti("ok&quot = 1 then
%>
<script language=javascript>
window.opener.option2.distiID.value=<%=distiID%>;
window.opener.option2.submit();
</script>
<%
else
response.write(&quot;Incorrect State, table does not exist&quot
End if
disti.close


but I get the error:
Item cannot be found in the collection corresponding to the requested name or ordinal.

for the disti(&quot;ok&quot part. If I remove the part of the SP that does the update ie the SELECT then it works fine, so I have no odea why this doesn't work!!!

help please

Tim

 

[rac2]

Maybe the first SELECT statement defines the fields in the recordset, none of them are named ok, the recordset is already created and populated and the SELECT @ok AS 'ok' has no place to go. This is a hypothesis.

So the way around that might be to put the results of that first SELECT into a variable and test the variable.

DECLARE @anySysObjectByThisName VARCHAR(200)

SELECT @anySysObjectByThisName = id FROM etc.

IF ISNULL( @anySysObjectByThisName )
BEGIN
etc.

Don't have my T-SQL reference here so the ISNULL() is just a guess, oh I mean pseudo-code, but you get the idea.