How to return a value from a stored procedure to EF

[Origin]: https://stackoverflow.com/questions/35352605/how-to-return-a-value-from-a-stored-procedure-to-ef

I’m attempting to call a stored proc through EF and retrieve a return value from the stored proc. I’ve used this answer as a guide. Here is my stored proc:

CREATE PROCEDURE [dbo].[usp_test]
(
    @result int OUT
)
AS
BEGIN

--DO STUFF

SET @result = 0
END

Here is how I’m calling it from EF:

var status = new SqlParameter
{
    ParameterName = "result",
    DbType = DbType.Int32,
    Direction = ParameterDirection.Output 
};
var result = context.Database.SqlQuery<int>("EXEC usp_test @result", status);
var wasSuccessful = result.First() == 1;
if (!wasSuccessful)
{
    //DO STUFF
}

I’m getting this error message:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types

What am I doing wrong?

shareedit

Try select @result before end of procedure.

CREATE PROCEDURE [dbo].[usp_test]
(
    @result int OUT
)
AS
BEGIN

--DO STUFF

SET @result = 0
Select @result
END

Hope it works.

shareedit
Advertisements

How to retrieve output parameter from stored procedure in Entity Framework

[Author]: https://code.msdn.microsoft.com/How-to-retrieve-output-e85526ba

How to get output parameter in stored procedure from Entity Framework(CSEFOutputParameter)

Introduction

This sample demonstrates how to use ObjectParameter instance to get the value of output parameter in Entity Framework.

Building the Sample

  1. Start Visual Studio 2012 and select File > Open > Project/Solution.
  2. Go to the directory in which you download the sample. Go to the directory named for   the sample, and double-click the Microsoft Visual Studio Solution (.sln) file.
  3. Attach the database file EFDemoDB.mdf under the folder _External_Dependencies to your SQL Server 2008R2 database instance.
  4. Modify the connection string in the App.config according to your SQL Server 2008R2 database instance name.
  5. Press F7 or use Build > Build Solution to build the sample.

Running the Sample

  1. Right click the solution and built it.
  2. Press F5 to run the project, a console window will appear.

3

3.Follow the prompt to input person information.

Using the Code

Stored Procedure

ALTER PROCEDURE [dbo].[InsertPerson]    
@Name varchar(50),    
@Description varchar(200),      
@ID int OUT    
AS    
INSERT INTO Person(Name,Description) VALUES(@Name,@Description)    
SET @ID = SCOPE_IDENTITY() 

The code below demonstrates how to get the value of output parameter.

// Create a ObjectParameter instance to retrieve output parameter from stored procedure 
ObjectParameter Output = new ObjectParameter("ID", typeof(Int32)); 
context.InsertPerson(Name, Description, Output);   

Console.Write("ID: {0}", Output.Value); 

How to Reset Identity Column Values in Sql Server

[Origin]: http://www.c-sharpcorner.com/blogs/how-to-reset-identity-column-values-in-sql-server1

Step 1: Create table.

CREATE TABLE dbo.Emp
(
  ID INT IDENTITY(1,1),
  Name VARCHAR(10)
)

Step 2: Insert some sample data.

INSERT INTO dbo.Emp(name)
VALUES ('Rakesh')
INSERT INTO dbo.Emp(Name)
VALUES ('Rakesh Kalluri')

build status
When we run above query the second insert statement will failed because of varchar(10) length.

Step 3: Check the identity column value.

DBCC CHECKIDENT (<span class="string">'Emp'</span>)

error
Even second insert was failed but the identity value is increased .if we insert the another record the identity value is 3.

INSERT INTO dbo.Emp(Name)
VALUES ('Kalluri')

SELECT * FROM Emp

id name

Step 4: Reset the identity column value.

DELETE FROM EMP WHERE ID=3

DBCC CHECKIDENT ('Emp', RESEED, 1)

INSERT INTO dbo.Emp(Name)
VALUES ('Kalluri')

SELECT * FROM Emp

id name table

Change Schema Name Of Table In SQL

[Origin]: https://stackoverflow.com/questions/15482838/change-schema-name-of-table-in-sql

I want to change schema name of table Employees in Database. In the current table Employeesdatabase schema name is dbo I want to change it to exe. How can I do it ?

Example:

FROM

dbo.Employees

TO

exe.Employees

I tried with this query:

ALTER SCHEMA exe TRANSFER dbo.Employees

But this gives me an error:

Cannot alter the schema ‘exe’, because it does not exist or you do not have permission.

What did I miss?

shareedit

Create Schema :

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe')) 
BEGIN
    EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END

ALTER Schema :

ALTER SCHEMA exe TRANSFER dbo.Employees
shareedit

How do I UPDATE from a SELECT in SQL Server?

[Origin]: https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server

UPDATE
    Table_A
SET Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'
shareedit

Multiple rows to one comma-separated value [duplicate]

[Origin]: https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value

I want to create a table valued function in SQL Server, which I want to return data in comma separated values.

For example table: tbl

ID | Value
---+-------
 1 | 100
 1 | 200
 1 | 300     
 1 | 400 

Now when I execute the query using the function Func1(value)

SELECT Func1(Value) 
FROM tbl 
WHERE ID = 1

Output that I want is: 100,200,300,400

shareimprove this question

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
       FROM @Table1 WHERE ID = t.ID
       FOR XML PATH(''), TYPE)
       .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
 ID      List_Output     
╠════╬═════════════════════╣
  1   100, 200, 300, 400 
╚════╩═════════════════════╝
shareimprove this answer