Returning output parameters from stored procedures
-
Hi, I am trying to return a varchar to my calling application (VB.NET).
Basically i am looping through a set of databases whos names are derived from a comma seperated list passed into the procedure. I am checking if the username i send to the procedure exits. If it does i want to add a '1' to the varchar variable i am rtrying to return and a 0 if the name is not found. The code i have is as follows.
alter procedure sp_GetUserValidation
(
@UserName varchar(50),
@Courses varchar(50),
@Return varchar(150) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CourseName varchar(100), @UserValArray varchar(200), @Pos int
SET @Courses = LTRIM(RTRIM(@Courses))+','
SET @Pos = CHARINDEX(',',@Courses, 1)
IF REPLACE(@Courses, ',', '') <> ''
BEGIN
WHILE @Pos > 0
Begin
SET @CourseName = LTRIM(RTRIM(LEFT(@Courses, @Pos - 1)))
IF @CourseName <> ''
BEGIN
Declare @sql varchar(300)
select @sql = 'SELECT Username FROM ' + @CourseName + '.dbo.Users WHERE UserName = ''' + @UserName + ''''
exec (@SQL)
IF @sql <> ''
BEGIN
SET @Return = @Return + '1'
END
ELSE
BEGIN
SET @Return = @Return + '0'
END
END
SET @Courses = RIGHT(@Courses, LEN(@Courses) - @Pos)
SET @Pos = CHARINDEX(',', @Courses, 1)
END
END
Return
END
The error recieved when trying to execute this stored procedure is that the variable @return is not been sent into the procedure.
Any help would be great
Thanks Dave
-
For this application dont set @Return as an OUTPUT variable.
Try adding ISNULL(@SQL, '') <> '' to your BEGIN as well.
What you told the app with the OUTPUT was that it was to be set to be the OUTPUT of an SP and you dont have the params built to pass back or a sp being called.
You should also change the SET @Return = @Return + '1' to be :
SET @Return = @UserName + '1' because prior to using this SET the value of @Return IS NULL
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hi, Thanks for the help, I get the following error
Server: Msg 245, Level 16, State 1, Procedure sp_GetUserValidation, Line 48
Syntax error converting the varchar value 'andym1' to a column of data type int.
With the code changed as you suggested. Any ideas. Have a placed the changes to the @sql in the right place?
alter procedure sp_GetUserValidation
(
@UserName varchar(50),
@Courses varchar(50)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CourseName varchar(100), @UserValArray varchar(200), @Pos int, @Return varchar(2000)
SET @Courses = LTRIM(RTRIM(@Courses))+','
SET @Pos = CHARINDEX(',',@Courses, 1)
IF REPLACE(@Courses, ',', '') <> ''
BEGIN
WHILE @Pos > 0
Begin
SET @CourseName = LTRIM(RTRIM(LEFT(@Courses, @Pos - 1)))
IF @CourseName <> ''
BEGIN
Declare @sql varchar(300)
select @sql = 'SELECT Username FROM ' + @CourseName + '.dbo.Users WHERE UserName = ''' + @UserName + ''''
exec (@SQL)
IF ISNULL(@SQL, '') <> ''
BEGIN
SET @Return = @UserName + '1'
END
ELSE
BEGIN
SET @Return = @UserName + '0'
END
END
SET @Courses = RIGHT(@Courses, LEN(@Courses) - @Pos)
SET @Pos = CHARINDEX(',', @Courses, 1)
END
END
Return @Return
END
Regards Dave
-
Try this
alter procedure sp_GetUserValidation
(
@UserName varchar(50),
@Courses varchar(50),
@Return varchar(150) OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @CourseName varchar(100), @UserValArray varchar(200), @Pos int
DECLARE @sql nvarchar(500)
DECLARE @Result char(1)
SET @Return = ''
SET @Courses = LTRIM(RTRIM(@Courses))
IF @Courses = '' RETURN
SET @Courses = @Courses + ','
SET @Pos = CHARINDEX(',', @Courses)
WHILE @Pos > 0
BEGIN
SET @CourseName = LTRIM(RTRIM(LEFT(@Courses, @Pos - 1)))
IF @CourseName <> ''
BEGIN
SET @Result = '0'
SET @sql = 'IF EXISTS(SELECT 1 FROM ' + @CourseName + '.dbo.Users WHERE UserName = ''' + @UserName + ''') SET @Result = ''1'' ELSE SET @Result = ''0'''
EXEC sp_executesql @sql,N'@Result char(1) OUTPUT',@Result OUTPUT
SET @Return = @Return + @Result
END
SET @Courses = RIGHT(@Courses, LEN(@Courses) - @Pos)
SET @Pos = CHARINDEX(',', @Courses, 1)
END
GO
DECLARE @Return varchar(150)
exec sp_GetUserValidation 'name','db1.db2,db3', @Return OUTPUT
SELECT @Return
Far away is close at hand in the images of elsewhere.
Anon. -
tHANKYOU
-
Hi this works and builds a concatonated list of 1's and 0's whcih is what i want. But it only ever shows 3, 1's or 0's. The database list that i am passing in with the comma seperated list contains over 60 database's to loop through and build the string.
Also do you know the code to access this stored procedure in VB.NET and see the output parameter that way, i have this so far,
Public
Function GetUserDetails(ByVal aname As String, ByVal aCoursesString As String)
Dim SQLReturnString As String
Try
_DBMasterConnection.Open()
Dim SprocCommand As New SqlClient.SqlCommand
SprocCommand.CommandType = CommandType.StoredProcedure
SprocCommand.CommandText = "sp_GetUserValidation3"
Dim GetUserValParam As New SqlClient.SqlParameter(aname, aCoursesString)
Dim ResultParam As New SqlClient.SqlParameter
SprocCommand.Parameters.Add(GetUserValParam)
SprocCommand.Parameters.Add(ResultParam)
SQLReturnString = SprocCommand.ExecuteNonQuery
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Oh Eck")
End Try
Return SQLReturnString
Thanks
Dave
-
Increase the size of @Courses. It must be large enough to hold all the database names (including a comma) plus the final comma.
This is how I do .NET
Public Function GetUserDetails(ByVal aname As String, ByVal aCoursesString As String) As String
Dim SQLReturnString As String
Dim param As SqlParameter
Dim paramRET As SqlParameter
Try
_DBMasterConnection.Open()
Dim SprocCommand As New SqlClient.SqlCommand
SprocCommand.Connection = _DBMasterConnection
SprocCommand.CommandType = CommandType.StoredProcedure
SprocCommand.CommandText = "sp_GetUserValidation3"
SprocCommand.Parameters.Clear()
param = New SqlParameter
param.ParameterName = "@UserName"
param.SqlDbType = VarChar
param.Direction = ParameterDirection.Input
param.Value = aname
SprocCommand.Parameters.Add(param)
param = New SqlParameter
param.ParameterName = "@Courses"
param.SqlDbType = VarChar
param.Direction = ParameterDirection.Input
param.Value = aCoursesString
SprocCommand.Parameters.Add(param)
paramRET = New SqlParameter
paramRET.ParameterName = "@Return"
paramRET.SqlDbType = VarChar
paramRET.Direction = ParameterDirection.Output
paramRET.Value = ""
SprocCommand.Parameters.Add(paramRET)
SprocCommand.ExecuteNonQuery()
SQLReturnString = CType(paramRET.Value, String)
Catch ex As SqlException
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Oh Eck")
End Try
Return SQLReturnString
End Function
Far away is close at hand in the images of elsewhere.
Anon. -
Hi, I have chnaged this to the following to make it compile, however, There is value in the Return Parameter apart from '''. This Stored Procedure is now working in SQL Server. This is SQL Server 7. Could that be an issue?
Regards
Dave
-
Sorry, forgot the new code
Dim SQLReturnString As String
Dim param1 As New SqlClient.SqlParameter
Dim param2 As New SqlClient.SqlParameter
Dim paramRET As New SqlClient.SqlParameter
Dim aReturnVal As String
Try
_DBMasterConnection.Open()
Dim SprocCommand As New SqlClient.SqlCommand
SprocCommand.Connection = _DBMasterConnection
SprocCommand.CommandType = CommandType.StoredProcedure
SprocCommand.CommandText = "sp_GetUserValidation3"
SprocCommand.Parameters.Clear()
'param = New SqlParameter
param1.ParameterName = "@UserName"
param1.SqlDbType = SqlDbType.VarChar
param1.Direction = ParameterDirection.Input
param1.Value = aname
SprocCommand.Parameters.Add(param1)
param2.ParameterName = "@Courses"
param2.SqlDbType = SqlDbType.VarChar
param2.Direction = ParameterDirection.Input
param2.Value = aCoursesString
SprocCommand.Parameters.Add(param2)
paramRET.ParameterName = "@Return"
paramRET.SqlDbType = SqlDbType.VarChar
paramRET.Direction = ParameterDirection.Output
paramRET.Value = ""
SprocCommand.Parameters.Add(paramRET)
SprocCommand.ExecuteNonQuery()
SQLReturnString =
CType(paramRET.Value, String)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Oh Eck")
End Try
Return SQLReturnString
End Function
-
Hi All, Just wanted to say thankyou for all the help with this problem. I now have a working solution. The code is below for you reference/interest.
Regards, Dave
The stored Procedure looks like this
CREATE procedure sp_GetUserValidation3
(
@UserName as varchar(50),
@Courses as varchar(8000),
@xReturn as varchar(150) OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @CourseName varchar(100), @UserValArray varchar(200), @Pos int
DECLARE @sql nvarchar(500)
DECLARE @Result char(1)
SET @xReturn = ''
SET @Courses = LTRIM(RTRIM(@Courses))
IF @Courses = '' RETURN
SET @Courses = @Courses + ','
SET @Pos = CHARINDEX(',', @Courses)
WHILE @Pos > 0
BEGIN
SET @CourseName = LTRIM(RTRIM(LEFT(@Courses, @Pos - 1)))
IF @CourseName <> ''
BEGIN
SET @Result = '0'
SET @sql = 'IF EXISTS(SELECT * FROM ' + @CourseName + '.dbo.Users WHERE UserName = ''' + @UserName + ''') SET @Result = ''1'' ELSE SET @Result = ''0'''
EXEC sp_executesql @sql,N'@Result char(1) OUTPUT',@Result OUTPUT
SET @xReturn = @xReturn + @Result
END
SET @Courses = RIGHT(@Courses, LEN(@Courses) - @Pos)
SET @Pos = CHARINDEX(',', @Courses, 1)
END
GO
The VB code to get the procedure and access the output value looks like this.
Dim
SQLReturnString As String
Dim param1 As New SqlClient.SqlParameter
Dim param2 As New SqlClient.SqlParameter
Dim paramRET As New SqlClient.SqlParameter
Dim aReturnVal As String
Try
_DBMasterConnection.Open()
Dim SprocCommand As New SqlClient.SqlCommand
SprocCommand.Connection = _DBMasterConnection
SprocCommand.CommandType = CommandType.StoredProcedure
SprocCommand.CommandText = "sp_GetUserValidation3"
SprocCommand.Parameters.Clear()
param1 =
New SqlClient.SqlParameter
param1.ParameterName = "@UserName"
param1.SqlDbType = SqlDbType.VarChar
param1.Direction = ParameterDirection.Input
param1.Value = aname
SprocCommand.Parameters.Add(param1)
param2 =
New SqlClient.SqlParameter
param2.ParameterName = "@Courses"
param2.SqlDbType = SqlDbType.VarChar
param2.Direction = ParameterDirection.Input
param2.Value = aCoursesString
SprocCommand.Parameters.Add(param2)
paramRET =
New SqlClient.SqlParameter
paramRET.ParameterName = "@xReturn"
paramRET.SqlDbType = SqlDbType.VarChar
paramRET.Size = 150
paramRET.Direction = ParameterDirection.Output
SprocCommand.Parameters.Add(paramRET)
SprocCommand.ExecuteNonQuery()
SQLReturnString =
CType(paramRET.Value, String)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Oh Eck")
End Try
_DBMasterConnection.Close()
Return SQLReturnString
again, many thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply