Pass parameter from powershell to stored procedure

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 9 months ago.

  • Author
    Posts
  • #6383

    by apriljuly at 2013-03-11 06:56:41

    Hi,

    I was trying to pass 2 parameters to my stored procedure using powershell.

    My code is as below, when I run it in powershell there's no error. However, it doesn't seem the parameters has been passed, because I can't get the correct result. Is there anything wrong on below?

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=localhost;Database=AMSDataWarehouse Test;Integrated Security=SSPI"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection
    $sqlcmd.CommandText = "YQBReport1"
    $param1=$sqlcmd.Parameters.Add("@from" , [System.Data.SqlDbType]::DateTime)
    $param1.Value = "2013-03-13"
    $param2=$sqlcmd.Parameters.Add("@to" , [System.Data.SqlDbType]::DateTime)
    $param2.Value = "2013-03-14"
    $SqlConnection.Open()
    $sqlcmd.ExecuteNonQuery()
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    Write-Host $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $SQLResult =$DataSet.Tables[0]
    $commands = $SQLResult | foreach-object -process { $_.output }> output.ps1
    .\output.ps1

    by cmille19 at 2013-03-12 08:59:53

    If you're expecting a result set, you're not going to get is using ExecuteNonQuery. ExecuteNonQuery like the name implies is used for executing insert, update and deletes (nonquery).

    You can either use a datareader or fill a dataset. I find it easier to work with datasets:
    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

    $dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)

    $dt = New-Object System.Data.DataTable

    $null = $dataAdapter.fill($dt)
    $dt

    by MaxTrinidad at 2013-04-16 12:17:45

    Passing parameters can be as simple as the following sample:

    $parm1 = 'dbo.MonthlySales'
    $parm2 = 'false'
    $sqlqry = "exec sp_spaceused '$parm1', '$parm2' "
    :
    $dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($sqlqry,$connString)
    :

    🙂

You must be logged in to reply to this topic.