?
Solved

Table name as a input parameter to stored procedure in Sybase (use in the join condition)

Posted on 2008-06-17
4
Medium Priority
?
4,417 Views
Last Modified: 2010-04-21
     I have a question, can we pass table name as input parameter to store procedure.
The answer is yes we can.
      However, can we use this table name in the join condition for example&.
I have two tables Product_data and Inventory. I want to pass Product_data as input parameter to store procedure and in WHERE clause join the product_data and Inventory table on id
(product_data.id = inventory.id ).


Create procedure DeleteFromDataTables @product_name varchar(50)
as
 
DECLARE @table_name varchar(50)
select @table_name = @product_name + "_data"
 
declare @sqlstring varchar(2000)
select @sqlstring =  "DELETE from " 
                               + @table_name 
                               + "where @table_name.id = Inventory.id" +  
 
execute (@sqlstring)

Open in new window

0
Comment
Question by:jeet_78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 21806347
You really have two questions here:
 - How do I pass a table name to a piece of dynamic SQL in a stored procedure?, and
 - How do I use a JOIN in a DELETE statement?

You are on the right track passing the name in but your code got lazy toward the end.  You cannot do "where @table_name.id = Inventory.id" because you have quoted @table_name as a literal.  You need something like:
"where " + @table_name + ".id = Inventory.id"

Now, as far as the syntax for doing a Join in a DELETE (or UPDATE for that matter goes, I use the ANSI SQL syntax because it is clearer and less ambiguous, particularly when you start doing outer joins.  The syntax without all the dynamic table name substitution is:

DELETE FROM Garments_data gd JOIN Inventory iv ON gd.id = iv.id

In some cases, it is better/easier/necessary to write the join in the opposite order which would confuse the DELETE and wind up deleting rows from Inventory.  The full syntax is...

DELETE Garments_data
  FROM Inventory iv
    JOIN Garments_data gd
      ON gd.id = iv.id

BTW, it is usually desirable to use table name aliases in SQL statements involving joins as it allows you an easier to read short-hand for the table names.  This is, of course, a necessity when you have to join a table to itself.

Regards,
Bill
0
 

Author Comment

by:jeet_78
ID: 21826635
While executing the following procedure I am getting the error&


create procedure SelectFromDataTables
@product_name varchar(50)
as
DECLARE @table_name varchar(50)
select @table_name = @product_name
declare @sqlstring varchar(2000)
select @sqlstring = "SELECT top 10* from " 
                               + @table_name
                               + "where" + @table_name + ".pt_sp_type_c=PTypeDesc.pt_type_c"
                               
execute (@sqlstring)



Execute the store procedure
exec SelectFromDataTables
@product_name = Position

Error
Server Message:  Number  102, Severity  15
(This error occurs when Adaptive Server detects a syntax error in a Transact-
SQL command or query.)
Server 'IDEV', Line 1:
Incorrect syntax near '='.
(1 row affected)
(return status = 0)

Please advice ......

0
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 21826708
Try...

exec SelectFromDataTables 'Position'

Also, you have some syntax issues...

Make certain you have spaces every where you need them or you will get garbage as well.  For instance, there is no space between the WHERE and @table_name.  You are also missing a space between 10 and * in SELECT top 10*...  Also, I am not certain you are using the top 10 functionality correctly.  Usually you use TOP with an order by clause which you don't have.  The right way to do this is with the "set rowcount #" but since you are doing it with dynamic SQL, you can probably get away with it as long as you don't need the same rows back each time.

What I would do is put a SELECT @sqlstring just before the execute statement and perhaps comment out the execute.  You will be able to see syntax problems much more easily that way.  Also, you can grab the text as output by the SELECT, paste it into a command window on your favorite tool, and try running it.

Regards,
Bill
0
 

Author Closing Comment

by:jeet_78
ID: 31468073
Thank you
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question