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

difference between varchar(500) vs varchar(max) in sql server

[Origin]: https://stackoverflow.com/questions/3682821/difference-between-varchar500-vs-varcharmax-in-sql-server

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.

To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. In fact, BLOBs are being deprecated in future releases of SQL Server.

Credit: http://www.teratrax.com/articles/varchar_max.html


In SQL Server 2005 and SQL Server 2008, The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB – 1 bytes). The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

Full Interesting read for you: http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

Reference: http://msdn.microsoft.com/en-us/library/ms143432.aspx

shareedit

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