Author |
Topic |
m1k3m41
Starting Member
9 Posts |
Posted - 2013-04-03 : 14:08:55
|
Hi,I was wondering how would you write a procedure to return more than one row? The procedure I am trying to write will take in a description and then return all the items that have that description within it. Right now I have CREATE PROCEDURE Search(descript varchar2) ASBEGIN SELECT * FROM Product WHERE Description = descript;END;/ Thanks guys. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-03 : 14:11:32
|
Yes, it can and it would. The procedure you wrote will return 0 or more rows depending on how many rows in the Product table satisfy the where clause. Your syntax though, does not seem correct for SQL Server. It should be:CREATE PROCEDURE Search(@descript varchar2) ASBEGIN SELECT * FROM Product WHERE Description = @descript;END; Is the Description column at most two characters wide? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 14:18:33
|
quote: Originally posted by James K Yes, it can and it would. The procedure you wrote will return 0 or more rows depending on how many rows in the Product table satisfy the where clause. Your syntax though, does not seem correct for SQL Server. It should be:CREATE PROCEDURE Search(@descript varchar(length)) ASBEGIN SELECT * FROM Product WHERE Description = @descript;END; Is the Description column at most two characters wide?
specify a length also for varchar------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
m1k3m41
Starting Member
9 Posts |
Posted - 2013-04-03 : 14:45:18
|
I'm doing this in SQL*Plus btw. I'm getting this error when trying your way.PLS-00103: Encountered the symbol "@" when expecting one of the following: <an identifier> <a double-quoted delimited-identifier> current delete exists prior |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 15:04:54
|
i think you are using a different RDBMS. the above syntax will work only in SQL Server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
m1k3m41
Starting Member
9 Posts |
Posted - 2013-04-03 : 15:22:16
|
How would I change the syntax to Oracle SQL*Plus then? It seems to be the right syntax, it just won't work for some reason |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-03 : 15:55:00
|
I don't know what the syntax for Oracle SQL*Plus would be, and I suspect most people on the forum may be unable to help as well. This forum is for Microsoft SQL Server, so there are very few Oracle people here, if any at all. You may want to ask at an Oracle forum. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 01:10:07
|
quote: Originally posted by m1k3m41 How would I change the syntax to Oracle SQL*Plus then? It seems to be the right syntax, it just won't work for some reason
try you luck at oracle forums ile www.orafaq.com------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 03:15:27
|
Refer this linkhttp://decipherinfosys.wordpress.com/2007/08/06/oracle-returning-a-recordset-from-a-stored-procedure/ |
|
|
|