I have a stored proc:
CREATE proc usp_Test1
@emp_cd varchar(6) out,
@status_message varchar(20) out
set @status_message = 'Print something'
select @status_message, @emp_cd
When I run this stored proc: exec usp_Test1 '100000', 'aaa', 'bbb'
It has no error and will print: 'Print something' 'aaa'
But when I used in my vb.net program:
emp_cd = objCmd.Parameters("@emp_cd").Value
it will have an exception that shows:
"Cast from type 'DBNull' to type 'String' is not valid."
It's obviously because that output parameter was not assisgned a value.
1. But my question is, why it was null instead of getting the value from the parameter list in exec usp_Test1 '100000', 'aaa', 'bbb', i.e. got a value of 'aaa'?
2. My solution to the above problem was added one more initilization line:
set @emp_cd = ''
Is there any better way or standard way of doing this? Thanks a lot.