Returning a string from a Stored Proc

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

SQL Server 2000 Forums

SQL Server Development (2000)

Returning a string from a Stored Proc

Author

Topic

dcunningham
Starting Member

25 Posts

Posted - 2005-07-14 : 11:13:28

I feel like an newbie asking this question but I'm up against a dead end on what to try next. The Problem:

I have variable in a sp that is labeled as an output. See below:

The asp page is calling the sp. The sp is just a barebones type of function just to get some data out of it. The sp is now:

CREATE PROCEDURE spdac_MemberPromotion

@MyOut varchar(50) OUTPUT

AS

select @MyOut = 'NewPhase'
Return

The asp page is sending an empty string to the @MyOut parameter of the sp. The sp should set the string 'NewPhase' into the @myOut variable and return it to me in my asp page as return value. But its not.

It is returning a Zero.

that could mean an empty string? / null or something else?

Am I missing something in the setup of this kind of technique?

Thanks.
Dale

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 12:29:01

I expect your problem is in how you ahve defined the call in ASP.

Suggest you try testing in Query Analyser:


DECLARE @MyString varchar(50)
SET @MyString ''
EXEC spdac_MemberPromotion @MyOut = @MyString
SELECT [Result] = @MyString

If that gives the right answer its in the definition in ASP (or possibly something mucked up in the ADO layer)

Kristen

dcunningham
Starting Member

25 Posts

Posted - 2005-07-14 : 13:48:54

The ASP side of this is fine, I can return a string now from the sp. There is one caveat. Below you'll see some notes/comments in my sp code that shows the various forms of strings that I can send back to the asp page. Heck, SQL even took two integers, added them and sent me back the total through the return variable declared as a string.

I need to send back 3 pieces of information form the sp, I didn't want to make a recordset call since the data was right there in the sp, manipulated/modified and contains the new values I need. SQL uses the '+' operator to concantenate strings together. I'm trying to do this both implicitly as well as explicitly. No luck. Please review.

-- Return the new state to the user

SET @intPhase = (SELECT CurrentPhaseID_FK FROM dbo.Customers WHERE ID_PK = @UID)
SET @intBoard = (SELECT CurrentBoardID_FK FROM dbo.Customers WHERE ID_PK = @UID)
SET @blnRecycle = (SELECT RecycleInThisPhase FROM dbo.Customers WHERE ID_PK =@UID )

-- SET @intPhase = CAST(@intPhase AS VARCHAR(12))
-- SET @intBoard = CAST(@intBoard AS VARCHAR(12))
-- SET @blnRecycle = CAST(@blnRecycle AS VARCHAR(12))

SET @NewPosition = 'NewPhase=' + @intPhase + ' NewBoard= ' + @intBoard + ' Rec= ' + @blnRecycle
SET @NewPosition = CAST(@NewPosition AS VARCHAR(100))

-- These items below - work! Perhaps its the string concantenation?
-- SET @NewPosition = @intPhase + @intBoard
-- SET @NewPosition = 10 + 10
-- SET @NewPosition = 'NewPhase=54789666aaasd '
-- SET @NewPosition = @intPhase
-- SET @NewPosition = 'NewPhase='

SET @MyReturn = @NewPosition

RETURN
GO

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 14:00:52

You didn't post the definitions of the variables, so I don't know if your naming convention of @intXXX means the variable was declared as an INT, or not.

If it was

SET @NewPosition = 'NewPhase=' + @intPhase + ' NewBoard= ' + @intBoard + ' Rec= ' + @blnRecycle

would give you a runtime error - so I don't think they can have been, unless @NewPosition was ALSO declared as a numeric type [in which case your naming convention is a little "loose" to say the least!!] - and if @NewPosition IS numeric then I can't see what this will achieve

SET @NewPosition = CAST(@NewPosition AS VARCHAR(100))

This code:
SET @intPhase = (SELECT CurrentPhaseID_FK FROM dbo.Customers WHERE ID_PK = @UID)
SET @intBoard = (SELECT CurrentBoardID_FK FROM dbo.Customers WHERE ID_PK = @UID)
SET @blnRecycle = (SELECT RecycleInThisPhase FROM dbo.Customers WHERE ID_PK =@UID )

would be much more efficient if written as:


SELECT @intPhase = CurrentPhaseID_FK,
       @intBoard = CurrentBoardID_FK,
       @blnRecycle = RecycleInThisPhase
FROM   dbo.Customers 
WHERE  ID_PK = @UID

Kristen

dcunningham
Starting Member

25 Posts

Posted - 2005-07-14 : 14:13:34

Thanks for your help Kristen!

yes an int in the variable name is an int in its declaration, the same with the bln.

Thanks for the tip on the select statement, I'll be sure to implement that.

The problem is as you can se from the explicit cast statements - I need to return a string with the string equivelents of the data in the int variables.

I hope that made sense.

dcunningham
Starting Member

25 Posts

Posted - 2005-07-14 : 14:36:13

I have a new strategy and it worked. SQL can send back all three variables back at once if each one is set to an output variable.

Thanks for all the help group.

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 16:27:06

"each one is set to an output variable"

Yup, that should do the trick

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources