Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 procedure to return more than 1 row

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) AS
BEGIN
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) AS
BEGIN
SELECT *
FROM Product
WHERE Description = @descript;
END;
Is the Description column at most two characters wide?
Go to Top of Page

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)) AS
BEGIN
SELECT *
FROM Product
WHERE Description = @descript;
END;
Is the Description column at most two characters wide?



specify a length also for varchar

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-04 : 03:15:27
Refer this link
http://decipherinfosys.wordpress.com/2007/08/06/oracle-returning-a-recordset-from-a-stored-procedure/
Go to Top of Page
   

- Advertisement -