SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 procedure to return more than 1 row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

m1k3m41
Starting Member

9 Posts

Posted - 04/03/2013 :  14:08:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 04/03/2013 :  14:11:32  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/03/2013 :  14:18:33  Show Profile  Reply with Quote
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 - 04/03/2013 :  14:45:18  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/03/2013 :  15:04:54  Show Profile  Reply with Quote
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 - 04/03/2013 :  15:22:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 04/03/2013 :  15:55:00  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/04/2013 :  01:10:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/04/2013 :  03:15:27  Show Profile  Reply with Quote
Refer this link
http://decipherinfosys.wordpress.com/2007/08/06/oracle-returning-a-recordset-from-a-stored-procedure/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000