commands

^

New-DbaSqlParameter

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Synopsis

Creates a new SQL parameter.

Description

Creates a new SQL parameter.

Syntax

New-DbaSqlParameter
    [[-CompareInfo] <String>]
    [[-DbType] <String>]
    [[-Direction] <String>]
    [-ForceColumnEncryption]
    [-IsNullable]
    [[-LocaleId] <Int32>]
    [[-Offset] <String>]
    [[-ParameterName] <String>]
    [[-Precision] <String>]
    [[-Scale] <String>]
    [[-Size] <Int32>]
    [[-SourceColumn] <String>]
    [-SourceColumnNullMapping]
    [[-SourceVersion] <String>]
    [[-SqlDbType] <String>]
    [[-SqlValue] <String>]
    [[-TypeName] <String>]
    [[-UdtTypeName] <String>]
    [[-Value] <Object>]
    [-EnableException]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> New-DbaSqlParameter -ParameterName json_result -SqlDbType NVarChar -Size -1 -Direction Output

Creates a SqlParameter object that can be used with Invoke-DbaQuery

Example: 2
PS C:\> $output = New-DbaSqlParameter -ParameterName json_result -SqlDbType NVarChar -Size -1 -Direction Output
PS C:\> Invoke-DbaQuery -SqlInstance localhost -Database master -CommandType StoredProcedure -Query my_proc -SqlParameter $output
PS C:\> $output.Value

Creates an output parameter and uses it to invoke a stored procedure.

Optional Parameters

-CompareInfo

Sets the CompareInfo object that defines how string comparisons should be performed for this parameter.

Alias
Required False
Pipeline false
Default Value
Accepted Values None,IgnoreCase,IgnoreNonSpace,IgnoreKanaType,IgnoreWidth,BinarySort2,BinarySort
-DbType

Sets the SqlDbType of the parameter.

Alias
Required False
Pipeline false
Default Value
Accepted Values AnsiString,Binary,Byte,Boolean,Currency,Date,DateTime,Decimal,Double,Guid,Int16,Int32,Int64,Object,SByte,Single,String,Time,UInt16,UInt32,UInt64,VarNumeric,AnsiStringFixedLength,StringFixedLength,Xml,DateTime2,DateTimeOffset
-Direction

Sets a value that indicates whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter.

Alias
Required False
Pipeline false
Default Value
Accepted Values Input,Output,InputOutput,ReturnValue
-ForceColumnEncryption

Enforces encryption of a parameter when using Always Encrypted. If SQL Server informs the driver that the parameter does not need to be encrypted, the query using the parameter will fail. This property provides additional protection against security attacks that involve a compromised SQL Server providing incorrect encryption metadata to the client, which may lead to data disclosure.

Alias
Required False
Pipeline false
Default Value False
-IsNullable

Sets a value that indicates whether the parameter accepts null values. IsNullable is not used to validate the parameter's value and will not prevent sending or receiving a null value when executing a command.

Alias
Required False
Pipeline false
Default Value False
-LocaleId

Sets the locale identifier that determines conventions and language for a particular region.

Alias
Required False
Pipeline false
Default Value 0
-Offset

Sets the offset to the Value property.

Alias
Required False
Pipeline false
Default Value
-ParameterName

Sets the name of the SqlParameter.

Alias Name
Required False
Pipeline false
Default Value
-Precision

Sets the maximum number of digits used to represent the Value property.

Alias
Required False
Pipeline false
Default Value
-Scale

Sets the number of decimal places to which Value is resolved.

Alias
Required False
Pipeline false
Default Value
-Size

Sets the maximum size, in bytes, of the data within the column.

Alias
Required False
Pipeline false
Default Value 0
-SourceColumn

Sets the name of the source column mapped to the DataSet and used for loading or returning the Value.

Alias
Required False
Pipeline false
Default Value
-SourceColumnNullMapping

Sets a value which indicates whether the source column is nullable. This allows SqlCommandBuilder to correctly generate Update statements for nullable columns.

Alias
Required False
Pipeline false
Default Value False
-SourceVersion

Sets the DataRowVersion to use when you load Value.

Alias
Required False
Pipeline false
Default Value
Accepted Values Original,Current,Proposed,Default
-SqlDbType

Sets the SqlDbType of the parameter.

Alias
Required False
Pipeline false
Default Value
Accepted Values BigInt,Binary,Bit,Char,DateTime,Decimal,Float,Image,Int,Money,NChar,NText,NVarChar,Real,UniqueIdentifier,SmallDateTime,SmallInt,SmallMoney,Text,Timestamp,TinyInt,VarBinary,VarChar,Variant,Xml,Udt,Structured,Date,Time,DateTime2,DateTimeOffset
-SqlValue

Sets the value of the parameter as an SQL type.

Alias
Required False
Pipeline false
Default Value
-TypeName

Sets the type name for a table-valued parameter.

Alias
Required False
Pipeline false
Default Value
-UdtTypeName

Sets a string that represents a user-defined type as a parameter.

Alias
Required False
Pipeline false
Default Value
-Value

Sets the value of the parameter.

Alias
Required False
Pipeline false
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False

 

Want to see the source code for this command? Check out New-DbaSqlParameter on GitHub.
Want to see the Bill Of Health for this command? Check out New-DbaSqlParameter.