Dynamic SQL in Sybase SQL

Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im 
used to MS SQL Server so im not sure what im doing wrong in Sybase.
Heres my bit of code

declare @sql varchar(100)
declare @sql2 varchar(100)

Select @sql = 
'''0001/100212'',''0001/100218'',''0001/100240'','0001/100243'''

select @sql2 = 'select * from table where table_tra_code in (' + @sql + ')'

Exec @sql2


now i would have thought this would have executed @sql2 and returned a 
result set,  but no :(. Any ideas?, idealy i will be passing in @SQL as a 
paramenter.

Cheers

Ben

p.s please email me at BenScott@halifax.co.uk
0
BenScott
3/15/2001 10:29:11 AM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

3 Replies
629 Views

Similar Articles

[PageSpeed] 31

What version you are using, since dynamic sql was only introduced in ASE-12.

<BenScott@halifax.co.uk> wrote in message
news:7577DF91CA86BE6700399AAF85256A10.00399ABF85256A10@webforums...
> Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im
> used to MS SQL Server so im not sure what im doing wrong in Sybase.
> Heres my bit of code
>
> declare @sql varchar(100)
> declare @sql2 varchar(100)
>
> Select @sql =
> '''0001/100212'',''0001/100218'',''0001/100240'','0001/100243'''
>
> select @sql2 = 'select * from table where table_tra_code in (' + @sql +
')'
>
> Exec @sql2
>
>
> now i would have thought this would have executed @sql2 and returned a
> result set,  but no :(. Any ideas?, idealy i will be passing in @SQL as a
> paramenter.
>
> Cheers
>
> Ben
>
> p.s please email me at BenScott@halifax.co.uk


0
Andy
3/15/2001 11:03:39 AM
Go to http://www.sypron.nl/dynsql.html for some really good documentation
and examples on how to do it in sybase.

<BenScott@halifax.co.uk> wrote in message
news:7577DF91CA86BE6700399AAF85256A10.00399ABF85256A10@webforums...
> Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im
> used to MS SQL Server so im not sure what im doing wrong in Sybase.
> Heres my bit of code
>
> declare @sql varchar(100)
> declare @sql2 varchar(100)
>
> Select @sql =
> '''0001/100212'',''0001/100218'',''0001/100240'','0001/100243'''
>
> select @sql2 = 'select * from table where table_tra_code in (' + @sql +
')'
>
> Exec @sql2
>
>
> now i would have thought this would have executed @sql2 and returned a
> result set,  but no :(. Any ideas?, idealy i will be passing in @SQL as a
> paramenter.
>
> Cheers
>
> Ben
>
> p.s please email me at BenScott@halifax.co.uk


0
Germano
3/15/2001 12:28:14 PM
You need to enclose the variable holding your dynamic SQL string in parens
like so:

EXEC (@sql2)

Neville

<BenScott@halifax.co.uk> wrote in message
news:7577DF91CA86BE6700399AAF85256A10.00399ABF85256A10@webforums...
> Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im
> used to MS SQL Server so im not sure what im doing wrong in Sybase.
> Heres my bit of code
>
> declare @sql varchar(100)
> declare @sql2 varchar(100)
>
> Select @sql =
> '''0001/100212'',''0001/100218'',''0001/100240'','0001/100243'''
>
> select @sql2 = 'select * from table where table_tra_code in (' + @sql +
')'
>
> Exec @sql2
>
>
> now i would have thought this would have executed @sql2 and returned a
> result set,  but no :(. Any ideas?, idealy i will be passing in @SQL as a
> paramenter.
>
> Cheers
>
> Ben
>
> p.s please email me at BenScott@halifax.co.uk


0
Neville
3/15/2001 2:45:19 PM
Reply:

Similar Artilces:

SQL Injection and Dynamic SQL
The consensus on avoiding SQL injection attacks is to pass all your collected data to a stored procedure. If you have to return a result set from a complex form, the answer seems to be to use dynamic SQL. On further study, I discovered that dynamic SQL has a lot of potential pitfalls and low and behold can still be vulnerable to injection attacks. I know that I am not the first person to ever try and collect data from a web form and return a result set, so what are the best practices? Say I have a complex form with about 25 search criteria (most of which are optional), I go through all t...

Dynamic SQL in Transact SQL
Does Dynamic SQL can be done in transact sql format in stored procedure? If yes, any reference can be read? Thx..... ...

Dynamic SQL
We have a problem with one cursor, in Embedded SQL/C that uses DYNAMIC SQL. The cursor works fine in SQL Advantage, and when NOT using the ? (dynamic SQL). Other similar dynamic sql cursors work fine also. This cursor always returns SQL NOT FOUND - even though it should be returning multiple rows. We have SQLSERVER 11.0.x - UNIXWARE And we have Adaptive Server 11.5 - NT Same results. Please help. Todd.C.Hampson@boeing.com Could you post your cursor code (declare, open, fetch, while loop) along with index structures of your cursor's table. Not...

Avoiding SQL Injection with Dynamic SQL
I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure? Jason Pacheco From here http://www.microsoft.com/technet/prodtechnol/sql/2000/maintai...

Is Dynamic SQL Possible in T/SQL
I come from a Microsoft SQL Server environment, so I am used to being able to create dynamic SQL statements within a stored procedure with the EXEC command. It appears that Sybase's equivalent command only recognizes othere stored procedures. Does anyone know of a way to do this in Sybase? ASE 12.0 supports dynamic execution of strings, see "execute" in Ref Manual. In versions ASE 11.5 and higher, you can also use the sp_remotesql against the "local" server, for much the same effect, although there are more limits using this than the "execute imm...

SQL function using dynamic sql.
 Hi all I am trying to pass a column name as a variable in a function but it will not let me.  I am sure it is something stupid. I know that you cannot pass a column in a normal select statement as a variable. You have to execute it dynamically, however you cannot use the exec in a function. Please see code belowALTER FUNCTION dbo.GetData(@ColumnName varchar(50))RETURNS Decimal(18,4)ASBEGINDECLARE @Value Decimal(18,4)SET @Value = (SELECT + @ColumnName + FROM Policy   WHERE Grade = 'Revised') -- Tried this way, does not work need to use ExecSET @Value = Exec(&...

Linq to SQL. sproc or dynamic sql?
Im of the old school belif that all data should come from a DB via sprocs for many reasons that i wont go into here. Im getting into website creation and want to call data from my db via linq to sql. I will want to have some form of pagination. is this easily possible using sprocs( without modifying the sprocs) or are you better just use dynamic sql and skip and take keywords? Hi, Please refer to the following link below for the information about "LINQ to SQL (Retrieving Data Using Stored Procedures)" http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrie...

Dynamic SQL and mixing T-SQL/Watcom
SQL Anywhere version 5.5.01 I believe that Watcom SQL is my only option for dynamic SQL execution (i.e. resolving object names at run time) in a procedure using EXECUTE IMMEDIATE. True? However we are likely to want to upgrade to SYBASE and would like to use (the inferior?) T-SQL and its data types for portability. I am currently calling a Watcom proc (using EXECUTE IMMEDIATE) from a T-SQL procedure. Seems to work fine but this seems philosophically wrong and I suspect will lead to problems. True? Ideas, Advice?? Thanks <pc> P.S. Please someone tell me I ca...

Dynamic SQL Format 2 SQL statement
Hi , I tried to implement Dynamic SQL Format 2 SQL statement but it is running fine with out error but it is not saving data to data base INT Dept_id_var =3d 156 String Dept_name_var SetNull(Dept_name_var) PREPARE SQLSA FROM "INSERT INTO dept VALUES (?,?) using my_transaction " ; EXECUTE SQLSA USING :Dept_id_var,:Dept_name_var ; [1] Commit using my_transaction ; [2] To test I tried my_transaction.autocommit =3d true my SQLSA satatement =85. my_transaction.autocommit =3d false but in both case I am not able to insert data to database...

Linq to SQL and stored procedure with dynamic sql
I have a stored procedure like create proc test as ..... set @sql = 'select ' + .... + ' from ....' exec(@sql)The select list is dynamically (in fact, pivot table). However, after drag the stored procedure to dbml design view and called, the stored procedure only return an integer.How to get the result set of the stored procedure when using dbml?Thanks,  Is this the only SELECT statement that @sql gets?  I created a simple example and it performed as I would expect returning a complete recordset....I'm wondering if your stored proc is returning 2 record se...

SQL syntax error using dynamic SQL
I have below code:    set @SQL='(select top 1 @idOUT = id from prospects where result=0' + @SQL_excludeprospects + ' order by id ASC)'    print @SQL    SET @ParmDefinition = N'@idOUT int OUTPUT';    EXECUTE sp_executesql @SQL, @ParmDefinition, @idOUT=@id OUTPUT;    SELECT @id; I get this output:(select top 1 @idOUT = id from prospects where result=0 AND id<>2 AND id<>6 AND id<>9 order by id ASC)Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'order'.I do...

Stored Prodesure and Dynamic SQL for SQL injections ??
hi friends sqldatasource1.selectcommand="select * from table1 where username=@username" sqldatasource1.selectparamaters.add=("username", textbox1.text) sqldatasource1 from data of toolbox and it connects gridview1.. Should i use stored prosedure for security ? or my code is enough ? my second question is.. if i dont use textbox (i mean user cant enter a value for username), should i use paramaters ? or can i use only "select * from table1 where username='John'"Mark as me if my question or my answer can be helpful for you :) Hey, Using comman...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <john.kingan@abibuildingdata.com> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

SQL Express to SQL ?
I am developing a site using the express edition of all tools (VWD and SQL Express).  I want to use GoDaddy hosting and was wondering if I will be able to effectively put my SQL database and website up on their hosting even though it was developed via Express edition of Microsoft programs?...

SQL > My SQL
I have a problem, my asp.net page wants to connect to the sql server, but it has to be the mysql. How can I solve this problem? This is my code: <%@ page explicit="true" language="VB" debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script runat="server"> sub page_load(sender as object, e as eventargs) If Not IsPostBack Then dim connect as sqlconnectio...

Web resources about - Dynamic SQL in Sybase SQL - sybase.sqlserver.general

Dynamics - Wikipedia, the free encyclopedia
Brownian dynamics , the occurrence of Langevin dynamics in the motion of particles in solution (e.g. a grain in water, as was first seen by Brown); ...

Datacenter Dynamics
Datacenterdynamics for data center information, conferences, news, videos, whitepapers and reports globally

Dynamic Chiropractic - chiropractic, news, articles, research & information for chiropractors - Find ...
Dynamic Chiropractic - chiropractic, news, articles, research & information for chiropractors - Find a Chiropractor

Walmart's Everyday Low Prices Face Amazon's Dynamic Price Push
... low prices" strategy is becoming an everyday price-adjustment strategy. In what seems to be a direct response to Amazon's sophisticated dynamic ...

Mea Culpa: Confidence Proceedings Edition: I Really Do Think Dynamic Scoring Is a Bad Idea
... *Brad DeLong noted that when he was a Treasury political appointee, one of the Treasury career staff economists lectured me him about dynamic ...

dynamic billboards to up your photo game
NYC is one of the most photographed places in the world, especially this time of year. And while Instagram has made every one of us feel like ...

SNL perfectly captured the dynamics of the Republican presidential race
The "Saturday Night Live" recap of the latest Republican presidential debate reflected fairly accurately the state of the GOP primary. Beck ...

Yolanda Foster explains her divorce: ‘chronic illness changed our dynamic’
... is extremely challenging, not only for the person that’s sick, but also for the caregiver,” Foster admitted. “It for sure changed the dynamic ...

CES 2016 TV tech: 4K yawns, high dynamic range dawns
... Show in Las Vegas this January, TVs with 4K resolution are old hat. So what's new hat? The next-generation TV technology known as high dynamic ...

Ford's 'Dynamic Shuttle' tests out a competitor for Uber
... group transportation options, its next competition could come from Ford. Today at an event in Dearborn, the carmaker showed off the Dynamic ...

Resources last updated: 12/26/2015 8:05:26 AM