Stored Procedure parameter

Is it possible to pass a SQL command, as a character string, as a parameter 
to a stored procedure?

If so, any suggestions on how this could be done?

Thanks
0
mike
12/11/2001 10:50:55 PM
sybase.ase.administration 7058 articles. 2 followers. Follow

4 Replies
104 Views

Similar Articles

[PageSpeed] 11

Certainly.


create procedure myprocedure @p1 varchar(255)
as
select @p1
go
declare @cmdstring varchar(255)
select @cmdstring = "select * from sysobjects"
execute myprocedure @cmdstring
go


Now, if you want the stored procedure to be able to actually execute
that string, you would have to be on a version of ASE that supports EXECUTE
IMMEDIATE,
and you would write the procedure as:

create procedure myprocedure @p1 varchar(255)
as EXECUTE (@p1)
go


-bret
mike wrote:

> Is it possible to pass a SQL command, as a character string, as a parameter
> to a stored procedure?
>
> If so, any suggestions on how this could be done?
>
> Thanks

0
Bret
12/11/2001 11:06:14 PM
does ASE 12.0 support execute
immediate?

THanks
0
mike
12/12/2001 3:29:21 PM
Yes.

<mike> wrote in message
news:C45719D9682D5E29005515AB85256B20.0082F7D785256B1F@webforums...
> does ASE 12.0 support execute
> immediate?
>
> THanks


0
Carl
12/12/2001 4:21:50 PM
you can alsu use sp_remotesql system proc if you have ASE 11.5.X version.
Best regards
ALPER �NEY
"Bret Halford" <bret@sybase.com> wrote in message
news:3C169166.1F84B86C@sybase.com...
> Certainly.
>
>
> create procedure myprocedure @p1 varchar(255)
> as
> select @p1
> go
> declare @cmdstring varchar(255)
> select @cmdstring = "select * from sysobjects"
> execute myprocedure @cmdstring
> go
>
>
> Now, if you want the stored procedure to be able to actually execute
> that string, you would have to be on a version of ASE that supports
EXECUTE
> IMMEDIATE,
> and you would write the procedure as:
>
> create procedure myprocedure @p1 varchar(255)
> as EXECUTE (@p1)
> go
>
>
> -bret
> mike wrote:
>
> > Is it possible to pass a SQL command, as a character string, as a
parameter
> > to a stored procedure?
> >
> > If so, any suggestions on how this could be done?
> >
> > Thanks
>


0
ALPER
12/26/2001 9:46:20 AM
Reply:

Similar Artilces:

Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')  but I also have a second stored procedure that needs to make reference to&n...

Java enabled ASE 12.0
Can a stored procedure define an input parameter of java.lang.String and have a client pass (via jconnect) a string greater than 255 characters to this parameter? I am having difficulties doing this. Throws "Error: com.sybase.jdbc2.jdbc.SybSQLException: Received an unrecognized datatype 35 from TDS datastream." Otherwise, it works fine for passing a string less than 256 characters. Also is ok when stored procedure is invoked from T-SQL with 256+ string. I am using ASE 12.0 and jconnect 5.2 and 5.5. Thanks for any help The gory details: Stored procedure has has 1...

Bug : wrong ODBC API return code from ASE's stored procedure with raiserror and output parameter.
Environment : Server : ASE 11.9.2 (ESD 8335) Client : Win NT, ..., ODBC driver from EBF 8617 (12.0.0 ODBC Driver Kit (32 bit) for NT). I'm have stored procedure with output parameter: create procedure t1_raise( @doc_type_id smallint, @doc_id int output ) AS select @doc_id=1234 raiserror 20000 't1_raise' return 1 return 0 I'm cannot trap raiserror from this stored proc via ODBC (API or another level), because return code from SQLExecute always SQL_SUCCESS (0) !!! There is no difference on parameters order. Without 'output' mag...

calling a stored procedure from a stored procedure
Hi Group! I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL. I have a sproc which breaks a given string-parameter into pieces. It's result set contains the pieces, e.g. last line of sproc: "select item from mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

Calling a stored procedure with output parameter and parameter name binding does not work
Hello NG, Named parameters are a fine feature, you don't have to obey the parameter order and can omit parameters that have default values. BUT if you try to use output parameters in the escaped syntax for jdbc like the following: { ? = call myProc ( @MyOutParam = ? ) } it doesn't seem to work anymore. Or am I simply too stupid? Maybe I have missed some documentation detail? Maybe someone can point me to the right direction? I'm really desperate. For implementation details see code snippet below. Thanks in advance. With best regards, have a merry christmas and a ha...

Using stored procedure with parameters gives 'parameter not supplied'
Hi all,I'm using an SqlDataSource to execute a stored procedure in a sybase database, which takes two arguements: create procedure pGetCodes @i_code varchar(32), @i_code_scheme char(8)asbegin select ECM2.code, ECM2.code_scheme, ECM2.source_id, ECM2.stock_exchange_id from refdb_rep..EXTERNAL_CODE_MARKET ECM1 join refdb_rep..EXTERNAL_CODE_MARKET ECM2 on ECM1.instrument_id = ECM2.instrument_id where ECM1.code = @i_code and ECM1.code_scheme = @i_code_scheme order by ECM2.code_schemeendThese arguements are supplied by a textbox and a dropdown as ControlParameters: &...

Running stored procedure within a stored procedure
I have a custom built users table for storing some values and I am also utilizing the aspnet_Users table. I want to delete a user from my users tables then execute the aspnet_Users_DeleteUser sproc and pass into the stored procedure the username of the user to delete because the DeleteUser method requires this. When I execute the command from within my asp.net web application I get the exception below. Both values are being obtained from the asp.net application and are represented in my DAL that is also below. Any thoughts as to why I am receiving this exception? Thanks. Procedure or functi...

Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...

stored procedure parameters
I have a sybase stored procedure with 5 parameters, of which 2 are "optional" in a sense that they have "default" values. In Powerbuilder 6.5, is it a requirement to pass all 5 parameters even though 2 are not mandatory? Thanks Nick N. >>I have a sybase stored procedure with 5 parameters, of which 2 are "optional" in a sense that they have "default" values. In Powerbuilder 6.5, is it a requirement to pass all 5 parameters even though 2 are not mandatory? << Yes if you are using the sp as a dw source. No if you a...

Calling Stored Procedure fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames I believe you want to use 'EXEC' abad...

how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...

Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...

Web resources about - Stored Procedure parameter - sybase.ase.administration

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Robust Growth In Procedures, Beneficial Product Mix Drive Intuitive Surgical's Strong Q3 Performance
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 12/15/2015 4:25:48 AM