Execute Store Procedure with Parameters in SQL SERVER

Try This Example: 

USE [TEST_01_05_2020]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[SP_OUTBOUNDDELIVERYNOTE]

@MAWB = N'0700000064',

@EXTERNORDERKEY = N'0700000064',

@EXTERNLINENO = N'900001',

@BILLTOKEY = N'''''',

@DELIVERYDATE = N'20191205',

@CONSIGNEEKEY = N'0000005691',

@C_COMPANY = N'Samsonite Philippines (DS)',

@C_ADDRESS1 = N'2/F ENZO Building, 399 Sen. Gi',

@C_ADDRESS2 = N'''''',

@C_ZIP = N'1200',

@C_CITY = N'Makati',

@REFERENCENUM = N'''''',

@ODREFERENCENUM = N'4500581905',

@EXTERNALORDERKEY2 = N'4500581905',

@SKU = N'000000000000033824',

@ALTSKU = N'1041',

@LOTTABLE01 = N'1INU',

@CONVERTEDQTY = 10.00,

@UNITPRICE = 72.03,

@SUSR1 = N'PHP',

@OSUSR1 = N'''''',

@SCHEDULEDDELVDATE = N'20191210',

@ORDERSID = N'''''',

@TRADINGPARTNER = N'''''',

@PACKNOTES = N'''''',

@HUNDREDWEIGHT = 27.330,

@PRODUCT_WEIGHT = 22.670,

@PRODUCT_CUBE = 511.290,

@OSUSR2 = N'CDM',

@RETAILSKU = N'''''',

@NOTES = N'''''',

@CREATEDON = N'20191210',

@TYPE = N'SAL',

@NOTES2 = N'''''',

@OSUSR4 = N'5690',

@OSUSR5 = N'0001'

SELECT 'Return Value' = @return_value

GO

EXEC sp_configure for SP procedure

EXEC sp_configure 'Ole Automation Procedures';
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

3 4 5 6 7 8 9 10 11

DECLARE @status int DECLARE @responseText as table(responseText nvarchar(max)) DECLARE @res as Int; DECLARE @url as nvarchar(1000) = 'https://www.google.it' EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false' EXEC sp_OAMethod @res, 'send' EXEC sp_OAGetProperty @res, 'status', @status OUT INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText' EXEC sp_OADestroy @res SELECT @status, responseText FROM @responseText

how to create user in sql server and grant select permission only

Follow the 4 steps for creating user and grant permission:

Example:

step 1: create LOGIN rochelle with password ='admin@123'
step 2: use DatabaseName
step 3: create user s_rochelle for login rochelle
step 4: grant select to s_rochelle
Step 5: GRANT SELECT ON SCHEMA :: wmwhse2 TO DBUAT02 WITH GRANT OPTION;

Create new APPPOOL in sql server

IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\MLM')
BEGIN
    CREATE LOGIN [IIS APPPOOL\MLM]
      FROM WINDOWS WITH DEFAULT_DATABASE=[master],
      DEFAULT_LANGUAGE=[us_english]
END
GO
CREATE USER [MLMWebDatabaseUser]
  FOR LOGIN [IIS APPPOOL\MLM]
GO
EXEC sp_addrolemember 'db_owner', 'MLMWebDatabaseUser'
GO

Get all column names from database table with specific schema in SQL Server

Many times there’s a requirement to get all columns for a particular table in database. Hence here is a SQL Query that does that.

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Your Table Name'

ORDER BY ORDINAL_POSITION

How to Executing SQL Query in Entity Framework and Linq MVC C#

I will explain to you how we can use different types of SQL Query methods.

Introduction

 In this post, we will see how to execute native SQL queries, using DB Context.

 You can execute SQL queries using the following types of SQL Query methods. 

 1- SQL Query for a specific entity type.

 2- SQL Query for a primitive data type.

 3- SQL commands.

Let’s start.

SQL Query for a specific entity type

We can use SQLQuery() method to write SQL queries which return an entity object.

Example:

==============================================================

//DbContext  

DbPersonnesEntities db = new DbPersonnesEntities();  

var customerList = db.Customers.SqlQuery("Select * From Customers").ToList<Customers>();  

==============================================================

The code, shown above, selects all the data rows from customer's table. I would like to add that columns returned by SQLQuery must match the property of an entity type, otherwise it will throw an exception.

SQL Query for a primitive data type

In this type, the SQL Query() method will return any primitive types created using the SQL Query method.

Example:

========================================================

//DbContext  

DbPersonnesEntities db = new DbPersonnesEntities();  

int customerId = db.Database.SqlQuery<int>("Select customerId From Customers where customerName='MAHDI'").FirstOrDefault<int>(); 

========================================================

SQL commands

We can use ExcecuteSqlCommand() method to ensure the CRUD operation.

Example:

===========================================================

//DbContext  

DbPersonnesEntities db = new DbPersonnesEntities();  

int result = db.Database.ExecuteSqlCommand("delete from Customers where customerId = 100");  

===========================================================

As you can see, code displayed above will delete a customer that has 100 as customerId.