Return varchar from stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return varchar from stored procedure

Author

Topic

vishu.av
Starting Member

26 Posts

Posted - 2007-04-26 : 09:51:07

Hi All,
I have a Stored procedure as below..
create procedure sp_sample
@name varchar(12)
as
begin
set @name = (select name from mytable where id = 1)
select @name
end

how can i return the varchar value from the above Stored procedure?
I want to capture it in SQLFetch ODBC call.

Thanks in advance!!

vishu
Bangalore

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 09:54:13

[code]
create procedure sp_sample
@name varchar(12) output
as
begin
~~set @name =~~ (select @name = name from mytable where id = 1)
~~select @name~~
end
[/code]

vishu.av
Starting Member

26 Posts

Posted - 2007-04-26 : 10:02:09

actually i have my query like this..

CREATE PROCEDURE SP_REC
( @LOCATION VARCHAR(20),
@TEMPLOCATION VARCHAR(20) OUTPUT)
AS
SET @TEMPLOCATION = ( SELECT LOC_RELATEDLOCATION FROM LOC_LOCATION WHERE LOC_LOCATIONID = @LOCATION)
IF @TEMPLOCATION != 'NULL'
BEGIN
SELECT @TEMPLOCATION
EXEC SP_REC @TEMPLOCATION
END

NOW I WANT TO RETURN @TEMPLOCATION AND CAPTURE IT IN SQLFetch() odbc call..
using....
retcode = SQLPrepare((hStmt),(SQLCHAR*)"{CALL SP_REC(?,?)}",SQL_NTS);
retcode = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 20, 0,
strUserNameID, 0, &Num2IndOrLen1);
retcode = SQLBindParameter(hStmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 20, 0,
strOutLoc, 0, &Num2IndOrLen2);
retcode = SQLExecute (hStmt);
if (SQL_SUCCESS==retcode)
{

SQLBindCol (hStmt, 1, SQL_C_CHAR,strLocation ,20, &cbModel);
retcode=SQLFetch(hStmt);

while((SQL_SUCCESS==retcode)||(SQL_SUCCESS_WITH_INFO==retcode))
{
retcode=0;
printf((const char*)strOutLoc);
retcode=SQLFetch(hStmt);
}
}

The stuff is not working..
everything is fine till ...retcode = SQLExecute (hStmt);
after that it fails..

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 16:50:38

[code]CREATE PROCEDURE SP_REC
(
@LOCATION VARCHAR(20),
@TEMPLOCATION VARCHAR(20) OUTPUT
)
AS

SET NOCOUNT ON

SELECT @TEMPLOCATION = LOC_RELATEDLOCATION
FROM LOC_LOCATION
WHERE LOC_LOCATIONID = @LOCATION

IF @TEMPLOCATION IS NOT NULL
BEGIN
EXEC SP_REC @TEMPLOCATION -- HERE THE CODE FAILS! YOU ARE DOING A RECURSIVE CALL! WHERE IS THE SECOND PARAMETER?
SELECT @TEMPLOCATION
END[/code]

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 03:38:57

Thanks for the response. I have modified my query accordingly..
ALTER PROCEDURE SP_REC
( @LOCATION VARCHAR(20),
@TEMPLOCATION VARCHAR(20) OUTPUT)
AS
DECLARE @TEMPLOCATION1 VARCHAR(100)
SET @TEMPLOCATION = ( SELECT LOC_RELATEDLOCATION FROM LOC_LOCATION WHERE LOC_LOCATIONID = @LOCATION)
IF @TEMPLOCATION != 'NULL'
BEGIN
SELECT @TEMPLOCATION
EXEC SP_REC @TEMPLOCATION,@TEMPLOCATION1
END

what wrong in the beow piece of code? Why i dont get 'strOutLoc' populated?

retcode = SQLPrepare((hStmt),(SQLCHAR*)"{CALL SP_RECTEST(?,?)}",SQL_NTS);

//retcode = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 20, 0,
// strUserNameID, 0, &Num2IndOrLen1);
long Num = 101;
retcode = SQLBindParameter(hStmt,1, SQL_PARAM_INPUT, SQL_C_ULONG,SQL_NUMERIC,10, 0,
&Num, 0, &Num2IndOrLen1);

retcode = SQLBindParameter(hStmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 100, 0,
strOutLoc, 0, &Num2IndOrLen2);

if (retcode != SQL_SUCCESS)
return 1;

// if (retcode != SQL_SUCCESS) {printf("Error2");}
// Execute the SQL statement handle
retcode = SQLExecute (hStmt);
//SQLRETURN SC= SQLError(hEnv,hDBC,hStmt,(SQLCHAR*)Sqlstate,(SQLINTEGER*)NativeError,(SQLCHAR*)MessageText,BufferLength,&TextLength);
//retcode=NativeError;
if (SQL_SUCCESS==retcode)
{

//SQLBindCol (hStmt, 1, SQL_C_CHAR,strLocation ,100, &cbModel);
printf((const char*)strOutLoc);
retcode=SQLFetch(hStmt);

while((SQL_SUCCESS==retcode)||(SQL_SUCCESS_WITH_INFO==retcode))
{
retcode=0;
printf((const char*)strOutLoc);
retcode=SQLFetch(hStmt);
}
}

Thanks in advance..

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 04:29:12

First of all, run the stored procedure in QA or SSMS to see that all is ok BEFORE you add another piece of layer that can disturb the expected outcome.

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 04:41:45

The Stored Procedure fetchs me the desired result.

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 04:53:06

Great!

Next question. Why are you using ODBC API instead of OLEDB?

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 05:10:51

I am working on a porting project and i need to code using ODBC APIs :-))
I am all successful with insert/ select statements using ODBC API but got struck here using Stored Procedure. Please let me know if there is anything wrong in above piece of c-code
Thanks

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 05:54:36

What is the error you get?

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 06:17:40

Hi Peter,
There is no error..Infact i have 4 rows of data when i execute my Stored procedure on SSMS.
But i get retcode as 100 ( SQL_NO_DATA) and out of the loop.
I have to get those values through ODBC call.

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 06:19:51

Yes, you get this "error message" because there are no more records to fetch after the fourth one, hence "no more data".

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 06:23:42

Ok. but My string doesn't contain any value returned from the SP.

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 06:27:18

Where is the string strOutLoc declared?

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 06:32:35

It is declared at the begining of the program as SQLUCHAR strOutLoc[100]={'\0'};

vishu
Bangalore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 06:39:19

Ok. C/C++ is not my ballpark. Maybe you should post your question in a proper forum.
It is NOT a database question anymore.

Peter Larsson
Helsingborg, Sweden

vishu.av
Starting Member

26 Posts

Posted - 2007-04-27 : 07:36:23

THANX ALL.
IT WAS OF GREAT HELP

vishu
Bangalore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources