string output parameters - 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)

string output parameters

Author

Topic

nikb
Starting Member

5 Posts

Posted - 2008-02-23 : 12:35:28

Hi can any one explain why when I specify an outparameter as a string I only get back the first letter of the string when I call the procedure from .net?

my procedure is...

ALTER PROCEDURE dbo.UpdateUserPACSPASDetails
@UserGUID uniqueidentifier,
@UserPACSName varchar(32),
@PASName varchar(32),
@UserPACSPassword varchar(95),
@UserPACSPasswordIV varchar(95),
@UserFriendlyName varchar(32),
@Message varchar(32) OUTPUT,
@MessageNumber int OUTPUT

AS

SET NOCOUNT ON

DECLARE @tmpGUID as uniqueidentifier /*stores the temp guid from the function */
DECLARE @ReturnDomainName as varchar(32) /*stores the tmp domainname of the user who has the PACS name */

SET @Message=CAST('' as varchar(32))
SET @MessageNumber= 0

/* first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPACSName(@UserPACSName)

IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN

/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 1
RETURN

END
END

/*now check the PAS name to see if it exists or is already in use by someone else
first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPASName(@PASName)

IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN

/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 2
RETURN

END
END

UPDATE tblUsers
SET UserPACSName=@UserPACSName,UserPACSPassword=@UserPACSPassword,
UserPACSPasswordIV=@UserPACSPasswordIV, PASName=@PASName, UserFriendlyName=@UserFriendlyName

WHERE UserGUID=@UserGUID

SET @Message=CAST('' as varchar(32))
SET @MessageNumber=3

RETURN

I call this proc via a vb.net program using a command.executenonquery and then look at the parameter.

For some reason I only get the first letter of the string back! i.e. if the string is LSDNET/nik I get 'L'.

If I run the proc from visual studio step in to procecure it works fine!

the vb I'm using is... (the createaddparameter simply creates a bunch of parameters to add to a command -with the specified names and dbtypes).

I'm obviously missing a trick but what it is I can't see.
I seem to be able to pass all other data types back easily!

Dim LoadUserCommand As New SqlCommand

'create add command
Dim CreateAddParameter() As String = {"@UserGUID", _
"@UserPACSName", "@PASName", "@UserPACSPassword", _
"@UserPACSPasswordIV", "@UserFriendlyName", "@Message", "@MessageNumber"}
Dim UpdateDBTypes() As DbType = {DbType.Guid, DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, _
DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, DbType.Int32}

LoadUserCommand = lclSQLHelper.CreateOLEDBCommand("UpdateUserPACSPASDetails", CreateAddParameter, UpdateDBTypes, lclDBConn.DBConnection)

LoadUserCommand.Parameters(6).Direction = ParameterDirection.Output
LoadUserCommand.Parameters(7).Direction = ParameterDirection.Output

'update the data in the data table in the database

LoadUserCommand.Parameters(0).Value = lclUser.UserGUID
LoadUserCommand.Parameters(1).Value = lclUser.UserPACSName
LoadUserCommand.Parameters(2).Value = lclUser.UserPASName
LoadUserCommand.Parameters(3).Value = lclUser.UserPACSPassword
LoadUserCommand.Parameters(4).Value = lclUser.PACSPasswordIV
LoadUserCommand.Parameters(5).Value = lclUser.UserFriendlyName
LoadUserCommand.Parameters(6).Value = ""
LoadUserCommand.Parameters(7).Value = 0

Try

Dim lclRet As Integer = LoadUserCommand.ExecuteNonQuery()

Dim tmpDomainName As String
tmpDomainName = LoadUserCommand.Parameters(6).Value.ToString 'read the name back

Dim tmpMsgNumber = LoadUserCommand.Parameters(7).Value 'read the message number back

Select Case tmpMsgNumber 'not written yet as I haven't got this far!!!
Case 1 'pacs username already exists
Case 2 'pas username already exists
Case 3 'execute fine and all names updated

End Select

nikb
Starting Member

5 Posts

Posted - 2008-02-23 : 12:36:33

by the way thanks in advance for any help.

es335
Starting Member

1 Post

Posted - 2008-03-15 : 01:14:30

Not sure if we have precisely the same scenario, but I came to this forum with a similar problem. I just now discovered that the maximum size (Size property) of my command parameter was being set by the current size of the string to which I was assigning the parameter value. If my string was declared but not initialized, I got null in return. If it was initialized to " ", then I just got the first character from the SQL result column. I have no idea why this is so. So, I went ahead and overrode the Size property of my parameter and set it to 80, and now the contents of the data make it into to my string variable in .NET.

Here's a snippet of my code (C#):
sqlConnection = new SqlConnection(Properties.Settings.Default.LibraryConnectionString);
sqlCommand = new SqlCommand("dbo.GetTitleAuthorByISBN", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@isbn", isbn);
sqlCommand.Parameters.AddWithValue("@title_author", titleAuthor).Direction = ParameterDirection.Output;

//The next line is what I needed to solve the problem...
sqlCommand.Parameters["@title_author"].Size = 80;

sqlConnection.Open();

sqlReader = sqlCommand.ExecuteReader();

titleAuthor = sqlCommand.Parameters["@title_author"].Value.ToString();

TimMartin
Starting Member

1 Post

Posted - 2008-04-22 : 10:42:05

Setting the size worked for me also. It is incredible that this requirement is not mentioned or clearly shown even in the Microsoft examples and many others that I found on the net. There might be more to it, perhaps size is not usually an issue but providing a size worked for me in my situation. Thank you, es335.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources