December 10, 2013 at 11:51 am
I have a powershell script I am trying to return a specific parameter form a sql SP, it always returns 1 no matter what and at this point I am confused as to why it does so, when I take out the return at the bottom and do a select on the @Returnval, it returns the correct value for what I input, but when I switch back to the scipt it does not.
sql sp
Alter PROCEDURE usp_CheckValidAgreement2
-- Add the parameters for the stored procedure here
@Logon varchar(20), @ReturnV int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Declare all variable sfor in use in SP
Declare @ValidLogon varchar(10), @Signed varchar(10), @AupExist varchar(10), @ReturnVal int;
--@Return values
-- 0 Means that logon was invalid and scipt should end as it is a Service account or its a Admin Card.
-- 1 means that they need to sign yearly AUP.
-- 2 means that they are good to go and have no worries.
--First check to see if it is a vaild user logon.
Select @ValidLogon = strEdipi from MNNGPersonnel..tblMNNatPersonnel where strFtLogon = @Logon;
if @ValidLogon != ''
BEGIN
select @AupExist = strEDIPI from tblSignedAup where strEdipi = @ValidLogon;
if @AupExist != ''
BEGIN
select @Signed = strEDIPI from tblSignedAup where strEdipi = @ValidLogon and datediff(d, dtsigned, getdate()) > 365
if @Signed != ''
BEGIN
set @ReturnVal = 1
END
ELSE
BEGIN
set @ReturnVal = 2
END
END
else
BEGIN
set @ReturnVal = 1;
END
END
else
BEGIN
set @ReturnVal = 0;
END
Set @ReturnV = @ReturnVal
RETURN
END
GO
here is the powershell script I am calling it from.
#$user = $env:username
$user = 'svc.test.ngmn'
$Returnvalue = ''
$MyConnectionString = "????"
$Connection = new-Object System.Data.SqlClient.SqlConnection($MyConnectionString)
$Connection.Open() | out-null
$Command = new-Object System.Data.SqlClient.SqlCommand("usp_CheckValidAgreement", $Connection)
$Command.CommandType = [System.Data.CommandType]'StoredProcedure'
$Command.Parameters.Add("@Logon",$user) | out-Null
$Command.Parameters.Add("@ReturnV",0) | out-null
$Command.Parameters["@ReturnV"].Direction = [system.Data.ParameterDirection]::Output
$Command.ExecuteNonQuery() | Out-Null
$Returnvalue = $Command.Parameters["@ReturnV"].value
$Connection.Close() | Out-Null
$Command.Dispose() | Out-Null
$Connection.Dispose() | Out-Null
write-host $Returnvalue
write-host $user
December 10, 2013 at 4:11 pm
Try changing:
!= ''
to
IS NOT NULL.
The variables should contain NULL, not be empty, if no row is found.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply