Save the value return from stored procedure in a varibale 1

[nomi2000]

Hi guys
I have a stored proc name usp_SysData_Select what i want is to store a column which my stored procedure will return in a variable so i can use it
like
DECLARE @RC int
EXEC @RC = [MOAD].[dbo].[usp_SysData_Select] DEFAULT
i watn @RC to be a column returned from the usp_SysDataSelect how i can do this?
Regards
Nouman


Nouman Zaheer
Software Engineer
MSR

 

[JohnDTampaBay]

The example above captured the RETURN value of the procedure, not a record set. RETURN can only be used for integer values. To capture a record set from a stored procedure call, you will need to use INSERT...EXEC:

DECLARE @ProcColumn TABLE (
   ProcColumn varchar(100)
)

INSERT @ProcColumn (ProcColumn) EXEC [MOAD].[dbo].[usp_SysData_Select] DEFAULT

You can then access the results of the procedure call by accessing the @ProcColumn table. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[nomi2000]

Hi angle
Thanks
i am getting following error
"EXECUTE cannot be used as a source when inserting into a table variable."
Regards
Nouman

Nouman Zaheer
Software Engineer
MSR

 

[JohnDTampaBay]

()@$*)#@! I forgot about that.

CREATE TABLE #ProcColumn (
   ProcColumn varchar(100)
)

INSERT #ProcColumn (ProcColumn) EXEC [MOAD].[dbo].[usp_SysData_Select] DEFAULT

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[nomi2000]

HI angle
thanks yes i can do this
but how i can tell in a SP to have TABLE type variable which would have the above query
let me explain the above thing
way like
Declare @SQL varchar(4000)
CREATE TABLE [#TEMP_SYSDATA] (
[SYSN_DfltOrder_Autho] [int] NULL,
[SYSN_DfltOrder_Country] [int] NULL,
[SYSN_DfltOrder_Currency] [int] NULL,
[SYSN_DfltOrder_Ports] [int] NULL ,
[SYSN_DfltOrder_States] [int] NULL ,
[SYSN_DfltOrder_Terms] [int] NULL ,
[SYSN_DfltOrder_Measure] [int] NULL ,
[SYSN_DfltOrder_MOT] [int] NULL ,
[SYSN_DfltOrder_HS] [int] NULL ,
[SYSN_DfltOrder_EntryType] [int] NULL,
[SYSN_DfltOrder_Charges] [int] NULL
)
Select @SQL='select SYSN_DfltOrder_Autho,SYSN_DfltOrder_Country,
SYSN_DfltOrder_Currency,SYSN_DfltOrder_Ports,
SYSN_DfltOrder_States,SYSN_DfltOrder_Terms,
SYSN_DfltOrder_Measure,SYSN_DfltOrder_MOT,
SYSN_DfltOrder_HS,SYSN_DfltOrder_EntryType,
SYSN_DfltOrder_Charges
from SysData where SYSC_System=''MOAD_USVI'''
INSERT INTO [#TEMP_SYSDATA]
EXEC (@SQL)
select * from [dbo].[#TEMP_SYSDATA]
drop table [dbo].[#TEMP_SYSDATA]

it will made the #TEMPSYSDATA and this will ba a storedprocedure say my_sp now in another stored procedure i want to use this my_sp in this way

Declare @MyTable as TABLE
@MyTable=EXEC (my_sp)
Declare @MyCol int
Select @MyCol=SomeColName from @MyTable
If @MyCol=1
begin

----
end
else
begin

end

Regards
Nouman


Nouman Zaheer
Software Engineer
MSR

 

[JohnDTampaBay]

You can't capture a record set from a stored procedure that way.

@MyTable=EXEC (my_sp)

You *can* create a user-defined function that returns a record set, then use the function instead of a table.

CREATE FUNCTION dbo.fnSysDataTable(
	@System	varchar(100)
)
RETURNS @SysData TABLE
   (
    [SYSN_DfltOrder_Autho] [int] NULL,
    [SYSN_DfltOrder_Country] [int] NULL,
    [SYSN_DfltOrder_Currency] [int] NULL,
    [SYSN_DfltOrder_Ports] [int] NULL ,
    [SYSN_DfltOrder_States] [int] NULL ,
    [SYSN_DfltOrder_Terms] [int] NULL ,
    [SYSN_DfltOrder_Measure] [int] NULL ,
    [SYSN_DfltOrder_MOT] [int] NULL ,
    [SYSN_DfltOrder_HS] [int] NULL ,
    [SYSN_DfltOrder_EntryType] [int] NULL,
    [SYSN_DfltOrder_Charges] [int] NULL 
   )
AS BEGIN
  INSERT INTO @SysData
  SELECT SYSN_DfltOrder_Autho,SYSN_DfltOrder_Country,
    SYSN_DfltOrder_Currency,SYSN_DfltOrder_Ports,
    SYSN_DfltOrder_States,SYSN_DfltOrder_Terms,
    SYSN_DfltOrder_Measure,SYSN_DfltOrder_MOT,
    SYSN_DfltOrder_HS,SYSN_DfltOrder_EntryType,
    SYSN_DfltOrder_Charges
  FROM SysData 
  WHERE SYSC_System = @System

  RETURN
END

SELECT @MyCol=SomeColName from dbo.fnSysDataTable('MOAD_USVI')
If @MyCol=1
begin

----
end
else
begin

end

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[nomi2000]

Hi Angel you are amazing
Thank you very very much
But one thing
I was working on this logic

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROC usp_SystemDefaults_Select
( @Col_Name varchar(100),
-- @SYSC_System varchar(100)='',
@Output_Col int OUTPUT )
AS
--SET NOCOUNT ON
Declare @SQL varchar(4000)
Declare @MyCol int
select @SQL= 'select @Output_Col= '+ @Col_Name + ' from SysData
where SYSC_System=''MOAD_USVI'''
print @SQL
Exec(@SQL)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- and calling the stored procedure
DECLARE @autho_title varchar(100)
EXEC usp_SystemDefaults_Select 'SYSN_Dflt3461Type',@Output_Col=@autho_title OUTPUT
PRINT convert(varchar(6),@autho_title)

but its giving me error must declare ' @Output_Col'
but thanks again
Here is a star for you
Regards

Nouman Zaheer
Software Engineer
MSR

 

[Dorine]

Can you pass parameters with the stored procedure as well?


CREATE TABLE #ProcColumn (
ProcColumn varchar(100)
)

INSERT #ProcColumn (ProcColumn) EXEC [MOAD].[dbo].[usp_SysData_Select] DEFAULT