cant return string to assign to a variable from Store Procedure.

Gloria Makoya

hi, i have a store procedure that insert a record and return a string to assign returned value to variable, im not sure where im making a mistake but it just hang on metastorm. but it the sp works fine on query analyzer. here is the store proc:

 

 ALTER PROCEDURE [dbo].[UnifAddItem]

 @parItemDescription varchar(50) ,

@parItemPrice varchar(50) ,

@parTypeID varchar(10)

 

AS

 

 begin

 

 SET NOCOUNT ON;

 

declare @Itemcode varchar(50), @maxNumber int, @curNumber int

 

set @maxNumber = (select REPLACE(dbo.UnifItems.ItemCode,'SAN','') from dbo.UnifItems where ID = (select max(id) from dbo.UnifItems))

 

set @curNumber = @maxNumber + 1

 

set @Itemcode = 'SAN' + cast(@curNumber as varchar(50))

 

INSERT INTO [Metastorm].[dbo].[UnifItems]

 ([ItemCode] ,[ItemDescription] ,[ItemPrice] ,[TypeID])

 

VALUES (@Itemcode ,@parItemDescription ,@parItemPrice ,@parTypeID )

 

Select @Itemcode

 

end

 

and code on Metastorm, when Button is clicked: %txtItemCode:= %SelectSQL("exec UnifAddItem '%txtItemAdd','%txtItemPrice','2'")

 

please help,

thanx

Scott_Leclerc

Does the account which the engine is connecting to the database have execute permission on the stored procedure and appropriate access to the tables it is using?  Are you seeing the records created by the Engine and not obtaining the return value, or you don't even see the records being inserted into the "UnifItems" table?