Stupid Problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Stupid Problem

Author

Topic

modulopedia
Starting Member

1 Post

Posted - 2011-06-19 : 08:39:17

New to SQL and especially Stored Procedures.

Have VB.NET code:

Public Function GetTwitterID(ByVal Username As String) As Long
Dim sqlcmd As New SqlClient.SqlCommand
sqlcmd.CommandText = "EXEC GetTweetID " & Username
sqlcmd.Connection = SQLCon
Return sqlcmd.ExecuteNonQuery
End Function

AND SP:

ALTER PROCEDURE [dbo].[GetTweetID]
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@Username as nvarchar(50)

AS
declare @TwitterID bigint
SET NOCOUNT ON
SELECT @TwitterID = TwitterID from Tweeters WHERE Twitter_Name = @Username

RETURN @TwitterID

Have tested the SP and it works but can't get @TwitterID to return.

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-19 : 10:26:45

You need to add a SqlParameter with ParameterDirection Property set to ReturnValue. This page has the description and examples: http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-20 : 14:00:14

the reason your posted code is not returning @twitterID is because RETURN only returns the datatype INT.

Be One with the Optimizer
TG

abbikhan
Starting Member

26 Posts

Posted - 2011-06-22 : 02:36:33

i hope this is what you want....

alter PROCEDURE [dbo].[GetTweetID]

(
@Username varchar(50),
@TwitterID bigint OUTPUT
)
AS
set @TwitterID=(SELECT TwitterID from Tweeters WHERE Twitter_Name = @Username)

RETURN @TwitterID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-22 : 08:50:08

Nope that still won't work for the reason I gave above. There are two ways to get a BIGINT value out from a stored procedure. SELECT as a result set and/or OUTPUT parameter.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources