Procedure,Function SQL server - VB.NET

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

Procedure,Function SQL server - VB.NET

Author

Topic

ducletan
Starting Member

25 Posts

Posted - 2010-12-09 : 03:20:07

Help me:
create procedure sp1
@giatri bit,
@output nvarchar(max) OUTPUT
as
begin
if @giatri
set @output='OK '
else
set @output='Failse '
end

create Function F1
@giatri bit,
returns nvarchar(max)
as
begin
declare @output nvarchar(max)
if @giatri
set @output='OK '
else
set @output='Failse '

return @output
end

In VB.NET
sp1 return: 'O' or 'F' (first char)
F1 return 'OK ' or 'Failse '

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-09 : 04:01:59

quote:
Originally posted by ducletan

In VB.NET
sp1 return: 'O' or 'F' (first char)
F1 return 'OK ' or 'Failse '

Thanks

I feel its not the problem with SP code but may be the way you are capturing the output returned by SP.

You might have declared the variable and not specified its length.

Can you show us the code used for calling the SP

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-09 : 04:07:14

First of all there are syntax errors in the function and the procedure:

create procedure sp1
	@giatri bit,
	@output nvarchar(max) OUTPUT
as
begin
if @giatri = 1
	set @output='OK '
else
	set @output='Failse '
end

create Function F1 (
	@giatri bit)
returns nvarchar(max)
as
begin
declare @output nvarchar(max)
if @giatri = 1
	set @output='OK '
else
	set @output='Failse '

return @output
end

Then the following code will give you exactly the same result:

select dbo.F1(0)

declare @out varchar(max)
exec sp1 0, @out output
select @out

In other words: you have probably declared the output variable in your VB.NET code too narrow.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

ducletan
Starting Member

25 Posts

Posted - 2010-12-09 : 04:48:10

create procedure sp1
@giatri bit,
@output nvarchar(max) OUTPUT
as
begin
if @giatri = 1
set @output='OK '
else
set @output='Failse '
end

create Function F1 (
@giatri bit)
returns nvarchar(max)
as
begin
declare @output nvarchar(max)
if @giatri = 1
set @output='OK '
else
set @output='Failse '

return @output
end
IN VB.NET

dim colecc as new collection
colecc.add(new Data.SQLClient.sqlParameter("@giatri",0) 'or 1
dim str as string
str=execFunction(SQL_connection,"F1",colecc)
return 'OK ' or 'Failse '

dim colecc as new collection
colecc.add(new Data.SQLClient.sqlParameter("@giatri",0) 'or 1
dim str as string
str=execProc(SQL_connection,"SP",colecc)
return 'O' or 'F'

Public Function execFunction(byref SQL_connection as sqlconnection,ByVal funcName As String, Optional ByVal params As Collection = Nothing) As String
Dim cmd As SqlCommand
Dim param As SqlParameter
Dim sCmdText As String
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = SQL_connection
cmd.CommandText = funcName
cmd.CommandTimeout = 0
If Not params Is Nothing Then
For iParam = 1 To params.Count
cmd.Parameters.Add(New Data.SqlClient.SqlParameter(params(iParam).ToString, params(iParam).value))
Next
End If
cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@OutPut", Space(100)))
cmd.Parameters("@OutPut").Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
Return cmd.Parameters("@OutPut").Value.ToString.Trim
End Function

Public Function execProc(byref sql_ByVal funcName As String, Optional ByVal params As Collection = Nothing) As String
Dim cmd As SqlCommand
Dim param As SqlParameter
Dim sCmdText As String
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = SQL_connection
cmd.CommandText = funcName
cmd.CommandTimeout = 0
If Not params Is Nothing Then
For iParam = 1 To params.Count
cmd.Parameters.Add(New Data.SqlClient.SqlParameter(params(iParam).ToString, params(iParam).value))
Next
End If
cmd.Parameters.Add(New Data.SqlClient.SqlParameter("@OutPut", Space(100)))
cmd.Parameters("@OutPut").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Return cmd.Parameters("@OutPut").Value.ToString.Trim
End Function

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 05:22:12

Would try changing the nvarchar(max) to nvarchar(10) and varchar(10)
Also check what sqldbtype the parameter has been created as. I always set the type.

Would be easier to test with inline code rather than calling execproc.
Also the execproc definition doesn't seem to match the call - maybe you aren't calling the function you think you are.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources