Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return correct parameter via stored Proc Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 11:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:57 PM
Points: 54, Visits: 107
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
Post #1521625
Posted Tuesday, December 10, 2013 4:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 1,734, Visits: 2,534
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)
I'm not fat, I'm gravity challenged.
Post #1521707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse