Menu
I written the following function.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create FUNCTION NameFunction ( @eid int ) RETURNS varchar AS BEGIN Declare @logid varchar(50); SELECT @logid = E.LoginId from HumanResources.Employee As E where E.BusinessEntityID = @eid RETURN @logid END GO When I am executing it is showing result as a. But expected result is adventure-works\terri0 Where I did the mistake here. Only first character coming. Need to change any thing?
435
RETURNS varchar should be RETURNS varchar(50) . varchar without a length specified is interpreted as varchar(1) in this context (and as varchar(30) in the context of a CAST ).
You can use out parameter or the resulset to return any data type. Show activity on this post. Show activity on this post. A stored procedure's return code is always integer, but you can have OUTPUT parameters that are any desired type -- see http://msdn.microsoft.com/en-us/library/aa174792.aspx .
CREATE PROC [myproc] @output VARCHAR(3) OUTPUT AS SET @output = 'SSS'; RETURN 0; Which you could call like this, DECLARE @output VARCHAR(3); EXEC [myproc] @output OUTPUT; SELECT @output; Or maybe you'd prefer to return a scalar result set?
To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.
Change your RETURN type to include a length, at this point it is just returning 1 character:
RETURNS varchar(100) Full code:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create FUNCTION NameFunction ( @eid int ) RETURNS varchar(100) -- or whatever length you need AS BEGIN Declare @logid varchar(50); SELECT @logid = E.LoginId from HumanResources.Employee As E where E.BusinessEntityID = @eid RETURN @logid END GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
