How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps

How to return a Table to VS using Stored Procedure

i have a Stored Procedure like below but it can't seem to return any table to Visual Studio

Code Snippet

ALTER PROCEDURE dbo.SelectHWCategoryBased

(
@.title varchar /* Like Processor/RAM */
)

AS

SELECT Hardwares.HWID, Hardwares.Title
FROM Hardwares INNER JOIN
Category ON Hardwares.CategoryID = Category.CategoryID
WHERE (Category.Type LIKE '%Hardware%') AND (Category.Title = @.Title)

There is no issue on your SP.

You have to use the SQLDataAdapater to fill the dataset. Use the current command object as SelectCommand of the Adapter & call the Fill() method.. Check with ADO.NET forum

|||

@.title varchar /* Like Processor/RAM */

The default size for an incoming varchar parameter is one character. If your incoming parameter value is longer than one character, it is being truncated, and the WHERE clause is looking for a Category.Title that matches the single character.

Probably won't find one.

Change the parameter to varchar(n) -n being sized appropriate to your Title field.

|||Thanks, that works, missed out that.