Hi, all

I am new to powershell, and practicing to use .net object in Powershell.

Now I can select/update/insert into SQL tables, but cannot get the return value, and output parameter from a store procedure.

Could someone please give me a sample script for these?

Thanks a lot,
Gary

Getting output and return parameters is a slightly more difficult, but here's an example:

I created procedure call InsertCategory in the Northwind database as follows:

CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15)
,@Identity int OUT
AS
SET NOCOUNT ON
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT

And this is the Powershell script to call the procedure and obtain the output parameter and return values (change the serverName variable for your environment):

$serverName='Z002\SQL2K8'
$databaseName='Northwind'
$query='InsertCategory'
$catName='Test'

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$conn = new-object System.Data.SqlClient.SqlConnection $connString
$conn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand("$query", $conn)

$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"

$cmd.Parameters.Add("@RowCount", [System.Data.SqlDbType]"Int")
$cmd.Parameters["@RowCount"].Direction = [System.Data.ParameterDirection]"ReturnValue"

$cmd.Parameters.Add("@CategoryName", [System.Data.SqlDbType]"NChar", 15)
$cmd.Parameters["@CategoryName"].Value = $catName

$cmd.Parameters.Add("@Identity", [System.Data.SqlDbType]"Int")
$cmd.Parameters["@Identity"].Direction = [System.Data.ParameterDirection]"Output"

$cmd.ExecuteNonQuery()
$conn.Close()

$cmd.Parameters["@RowCount"].Value
$cmd.Parameters["@Identity"].Value

Thank you so much, it works like a charm.