Problem In Calling A Stored Proc With OUTPUT Parms

Sep 26, 2000

I have a problem in calling a procedure.
It has two input parameters and seven(7) output parms.

When I run it this way:

exec usp_List_Relationship_Users @relationship_id = 1851 ,
@status = 'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT

I get this error:
Server: Msg 119, Level 15, State 1, Line 10
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

But when I run it this way :
exec usp_List_Relationship_Users 1851 ,
'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT

I get the expected results. Why should I omit the name of the name of the input parameters? I don't know why I am getting this error if don't run by ommitting the name of the input params.

Any help regarding this matter is greatlt appreciated.
You can assume that all the variables are declared prior to executing the stored procedure.

Regards
Sushruth Nanduri.

View 3 Replies


ADVERTISEMENT

How To Specifiy An Output Parameter When Calling Stored Proc In C#

Oct 2, 2007

How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9  mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0;  // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();

View 7 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Calling CLR Stored Proc

Apr 15, 2008

For a new web project I am working on I wrote a couple of Stored Procs in C#.  I have deployed them to the database, and can run them fine using SQL Mgmt Studio.  The problem is that when I try and run them from my application, I get a 'The Stored Procedure 'ProcName' does not exist' error message.  I orignally thought it might be a permissions issue, so I changed my connection string to connect as 'sa' and it still got the same error.  I am using the Microsoft.Practices.EnterpriseLibrary to connect to the database.  Any ideas on what I need to do to get this to work?

View 1 Replies View Related

Calling DLL From A Stored Proc

Jan 8, 2003

I need to call a DLL from with one of my stored procedures. How do I achieve this?

The DLL is currently on App server (Not on the SQL Server box). Do I have to register this DLL on my SQL server, create an extended stored proc and call it from my stored proc?

Please throw some light on this topic.

Thanks,
Varma

View 3 Replies View Related

What Is Calling A Stored Proc?

Apr 24, 2007

How can I find what is calling a stored proc in a database or on the server?


Currently a job runs daily that calls a couple of DTS packages. Somewhere in this process a stored proc is being called that pumps out several emails. BUT nowhere in the DTS or job can I find any reference to the stored proc.

View 3 Replies View Related

Calling At Stored Proc (DHL)

Sep 17, 2007

I would like to know how to call a Stored Proc with in code. Have an SQL code but need data from a table that needs to be updated by a Stored Proc. Would need to call proc in the middle of code to get the correct data. Just not sure of what the statement looks like to call the proc. Thank you for your help. David (DHL)

View 9 Replies View Related

Calling Stored Proc In ActiveX Script

Dec 13, 2005

Hi!

How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.

Thanks.

View 2 Replies View Related

Calling Stored Proc. Multiple Times...

Feb 1, 2008

I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.

I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.

Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.

Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.

Any help is very much appreciated.

View 2 Replies View Related

Calling A Stored Procedure From Within A Stored Proc

Dec 18, 2007

Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
 Any and all help appreciated.
Kal

View 3 Replies View Related

Calling Stored Proc With Default Params From .NET Not Working.

Mar 20, 2006

All,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]

@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
 
And inside of aaJonTemp, I have the following :




NULL
here2
NULL

NULL
here2
NULL
 
How is this possible?  If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
 
Thx
jonpfl

View 1 Replies View Related

Calling A Stored Proc From Within Another Stored Proc

Feb 20, 2003

I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.

I guess I should state my question to the forum !

Is there a way to call a stored proc from within another stored proc?

Thanks In Advance.

Tony

View 1 Replies View Related

Calling Stored Proc B From Stored Proc A

Jan 20, 2004

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

Thanks

View 14 Replies View Related

Calling T SQL Stored Proc From CLR Stored Proc

Aug 30, 2007

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL
}

spSQL
(

INSERT INTO #tmpABC EXECUTE spSQL2
)


spSQL2
(
// some other t-sql stored proc
)


Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

View 2 Replies View Related

Strange Results When Calling A Sql Stored Proc From Within An Access2000 Project

Nov 18, 2006

Hi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB

View 1 Replies View Related

Best Practice For A Long Running Queries / Asynchronously Calling A Stored Proc?

May 23, 2008

All -

I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.

One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.

What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.

Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?

Thanks.

View 5 Replies View Related

Stored Proc's Using OUTPUT

Jun 16, 2004

How can I view the output of a stored procedure that is returning a OUTPUT variable? I've written a stored proc that uses OUTPUT but when I run it, all I see is "The command(s) completed successfully." I'm at a loss on how to debug/verify/view the output value.

The same thing happens using this example from MS.

CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @TITLE
SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO


TIA - KB

View 5 Replies View Related

Esql/C Calling Stored Procedure With Output Parameters

Dec 6, 2004

I'm trying to write an esqlc program that will run a stored procedure that returns several output parameters. I haven't been able to find any documentation to date that explains how to run the "EXEC SQL EXECUTE procname" command and specify the output parameters.

My stored procedure "aek_proc1" takes one input parameter (p1 - an 8-character string) and 3 output parameters (p2 - an integer; p3 - an 8-character string, and p4 a 40-character string).

My esqlc program contains the following code….

EXEC SQL BEGIN DECLARE SECTION;
char p1[9];
int p2;
char p3[9];
char p4[41];
WXEC SQL END DECLARE SECTION;

sprintf(&p1[0], "GL");
p2 = 0;
sprintf(&p3[0], "");
sprintf(&p4[0], "");

EXEC SQL EXECUTE aek_proc1 :p1,
:p2 OUTPUT,
:p3 OUTPUT,
:p4 OUTPUT;

I am getting errors at runtime about constants being passed for OUTPUT parameters.

I can run the same stored procedure in Query Analyser and it works beautifully (see below)

declare @p1 char(8)
declare @p2 integer
declare @p3 char(8)
declare @p4 char(40)

set @p1 = 'GL'

execute aek_proc1 @p1, @p2 output, @p3 output, @p4 output

select @p1 p1, @p2 p2, @p3 p3, @p4 p4

Any idea what I'm doing wrong or how it should be coded?

I'd really appreciate any advice you can offer!!

I've spend hours browsing this newsgroup and found lots of examples of how to do this in VB and from Query Analyser but I can't find any examples for ESQL/C that work.

So, please help!!!

Thanks,

AllanK :rolleyes:

View 3 Replies View Related

Using A Cursor Output From Stored Proc

Feb 19, 2005

Has anyone ever tried to use a cursor as an output variable to a stored proc ?

I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc
@parentId integer,
@outputCursor CURSOR VARYING OUTPUT
AS
BEGIN TRAN T1
DECLARE parent_cursor CURSOR STATIC
FOR
SELECT parentTable.childId, parentTable. parentValue
FROM parentTable
WHERE parentTable.parentId = @parentId
OPEN parent_cursor

SET @outputCursor = parent_cursor

DECLARE @childId int
DECLARE @parentValue varchar(50)
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId

FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END

CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.

My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1

Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?

Thanks
Martin

View 6 Replies View Related

Output Of A Stored Proc Into A Table

Sep 2, 2004

Want to obtain the outpur of a xp_cmdshell (or any other procedure call) command to a table. Is it possible ?

View 6 Replies View Related

Retrieving Output Parameter From Stored Proc

Oct 2, 2006

I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.The problem is that  cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@UserExists", SqlDbType.Int);
cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output;
cn.Open();
int x = (int)cmd.Parameters["@UserExists"].Value;
cn.Close();
return (x>1);
}  And the corresponding stored procedure: ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName
(
@UserName varchar(16),
@UserExists int OUTPUT
)
AS
SET NOCOUNT ON
SELECT @UserExists = count(*)
FROM mys_Profiles
WHERE UserName = @UserName


RETURN  

View 1 Replies View Related

Stored Proc With Varchar Output Parameter

Nov 30, 2004

Hi Guys
I am wondering if you could spare some time and help me out with this puzzle.
I am new to this stuff so please take it easy on me.

I’m trying to create procedure which will take 2 input parameters and give me 1 back.
Originally there will be more outputs but for this training exercise 1 should do.
There are 2 tables as per diagram below and what I’m trying to do is
Verify username & password and pull out user group_name.

|---------------| |-----------------------|
| TBL_USERS | |TBL_USER_GROUPS|
|---------------| |-----------------------|
| USERNAME | /|GROUP_ID |
| PASSWORD | / |GROUP_NAME |
| GROUP_ID |< | |
|---------------| |-----------------------|

For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.

'====================================================
ALTER PROCEDURE dbo.try01
(
@UserName varchar(50),
@Password varchar(50),
@Group varchar Output
)
AS
SET NOCOUNT ON;
SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME,
TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID
FROM TBL_USERS INNER JOIN TBL_USER_GROUPS
ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID
WHERE (TBL_USERS.USERNAME = @UserName)
AND (TBL_USERS.PASSWORD = @Password)
'====================================================


and this is what i'm getting in VS.Net while trying to save.


'====================================================
ADO error: A select statement that assigns a value to variable must
not be combined with data-retrieval operation.
'====================================================


I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?

Please help.

CC

View 1 Replies View Related

Capture Stored Proc Output To File

Jan 8, 2001

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

View 3 Replies View Related

Stored Proc Output Parameter To A Variable

Mar 16, 2006

I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.

Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;

Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4

When this task is run, I get the following error:


0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong?
Thanks.

View 4 Replies View Related

Calling A Stored Procedure That Inserts Records And Generates An Output Parameter

Apr 12, 2006

I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.

How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.

View 4 Replies View Related

Output Param Of Stored Proc - Oledb Command

Jun 5, 2007

Hello

This is my first post so please be gentle!!

I have a stored proc that returns a value:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN

--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end

[There are some more methods here]

Return

END



How do I get at the output value?

I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.

View 10 Replies View Related

Stored Proc - Output Parameter Does Not Work With Nested Level

Apr 26, 2004

Anyone can help with this question: thanks

in a asp .net application, I call a stored procedure which have a output parameter.
the output parameter works find in sql session, but not in the asp .net application.

if I put select msg_out = "error message" in position A(see below for stored proc), it works fine
if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session
The stored proc was created like this:

Create procedure XXXXXXX
(@msg_out varchar(80) OUTPUT
)
as
begin

while exists (*******)
begin
//position A
if certain condition
begin

select msg_out = "error message"
return 1
end

end


end


end

It seems to me that anything inside if - the second begin...end - it wont get executed.

Anyone has got a clue

Any help much appreciated!

View 5 Replies View Related

What Am I Missing Here? Using A Stored Proc Output To Build Table Rows

Nov 27, 2007

Hi all,

I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.

To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS

select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from

(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc

GO

The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>

This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.

Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.

I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.

Any thoughts?

View 6 Replies View Related

Integration Services :: Calling Oracle Stored Procedure With Output Parameters In OleDB Command Task?

Nov 7, 2015

I want to call "oracle" stored procedure with output parameter from SSIS ole db command task.

Actually I am able to successfully call the procedure but my Output value is not updating in the mapped column.

I used below PL/SQL query.

DECLARE
IS_VALID VARCHAR2(200);
BEGIN
IS_VALID(
PARAM1 => ?,
PARAM2 => ?,
IS_VALID => IS_VALID
);
? := IS_VALID;
END;

If I try to supply "OUTPUT" word I get error:

"ORA-06550: line 1, column 45:

PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following:   . ( ) , * @ % & = - + < / >"
BEGIN
IS_VALID(
?,
?,
? OUTPUT
);
END;

how to receive output parameter value of oledb command while calling oracle stored procedures.

View 4 Replies View Related

Read Rows AND An Output Parameter From Codebehind Returned By Stored Proc?

Feb 5, 2008

I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID  @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read    dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!

View 2 Replies View Related

Trigger Error When Inserting Stored Proc Output Into Temp Table.

Feb 18, 2008





I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?

--Table being called.


ALTER PROCEDURE dbo.wbGetxxxxxUserReturns

@nxxxxtyId smallint,

@sxxxxxxxxUser varchar(32),

@sxxxxName varchar(32)

AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)




ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest



AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)

INSERT #Scorecard_Returns(

NAME_COL ,

ACCT_ID

ACCT_NUMBER ,

ENTITY_ID,

NAME,

ID,

NUM_ACCOUNT,

A_OFFICER,

I_OFFICER,

B_CODE,

I_OBJ ,

LAST_MONTH

LAST_3MONTHS,

IS

)

EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns

@nId = 1,

@sSUser = 'SELECTED USER',

@sUName = 'VALID USER'

View 4 Replies View Related

Stored Procedure Parms Disappearing

Jun 4, 2007

I'm executing a stored procedure from an Execute SQL Task in my SSIS package. The procedure takes two input variables and creates and executes an SQL statement based on those variables. I don't have it set to return anything.



When I run the package in debug mode on my desktop, it runs just fine.



However, when I save it to the RDBMS and execute it as a step in a job, the stored procedure errors out because it doesn't receive the input parameters. If I replace the ? in the statement with the value I know the parm should have, it executes just fine. The parms are mapped to the 0 (and, where applicable, 1) input parms.



As an aside, the package variable supplying the value is a byte value. However, in the parm mapping for the step, I define it as varchar because that's what the stored procedure is expecting. Could this be goofing me up?

View 6 Replies View Related

Passing Parms With No Values To A Stored Procedure

Feb 28, 2008

I'm want to pass empty parm value to a stored procedure. I'm new at this stuff and not sure how to do this. To make it work I can put values in parms and it works great. I don't want to populate some of the fields in the table with values. The fields are cnty,dist,beat,convertedkey. Anyone know how I can get it done? Code below. Thanks for your help!

int shift = Convert.ToInt16(dr.Cells[1].Value.ToString());

int prior = Convert.ToInt16(dr.Cells[2].Value.ToString());

string lname = Convert.ToString(dr.Cells[3].Value.ToString());

string fname = Convert.ToString(dr.Cells[4].Value.ToString());

string unit = Convert.ToString(dr.Cells[5].Value.ToString());

string handle = Convert.ToString(dr.Cells.Value.ToString());

int badge = Convert.ToInt16(dr.Cells[7].Value.ToString());

string cnty = Convert.ToString('1');

string dist = Convert.ToString('1');

int beat = Convert.ToInt16(1);

int convertedkey = Convert.ToInt16(1);

char action = 'I';

char assign = 'Y';

var db = new dpoffassignedDataContext();

var q = db.SP_DpAssignAllOfficers(action, convertedkey, shift, prior, lname, fname, unit, handle, cnty, dist, beat, badge, assign);





Stored procedure below;

ALTER PROCEDURE SP_DpAssignAllOfficers

(

@action nvarchar(1),

@int_id int,

@shift int,

@prior int,

@lname nvarchar(20),

@fname nvarchar(20),

@unit nvarchar(5),

@handle nvarchar(10),

@cnty nvarchar(10),

@dist nvarchar(10),

@beat int,

@badge int,

@assign char



)

AS

INSERT

INTO dpofficers(dpo_prior,dpo_lname,dpo_fname,dpo_unit,dpo_handle,dpo_cnty,dpo_dist,dpo_beat,dpo_badge,dpo_shift)

VALUES (@prior,@lname,@fname,@unit,@handle,@cnty,@dist,@beat,@badge,@shift)

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved