You're going to have to show sample data and desired results given different calls to the stored procedure. I can't make heads or tails of your narrative. -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ (Reverse e-mail to reply.) [quoted text, click to view] "Agnes" <agnes@dynamictech.com.hk> wrote in message
news:Om$6M9lREHA.2936@TK2MSFTNGP12.phx.gbl...
>I know how to pass the parameter to store procedure, however, can it return
> some item results ???
> I want to get back the name,address1---4, Thanks a lot
>
> my code is
> CREATE PROCEDURE dbo.companyinfo_shipper_search
> @searchcode varchar(10)
>
> as
> select code,name,country,address1,address2,address3,address4 ,telno,faxno
> from companyheader
> where code = @searchcode
> GO
>
> From Agnes
>
>
Your stored procedure is returning a resultset consisting of name, address, etc so I am not clear what you want. You are already doing what you want to do! Regards Partha Mandayam Software Consultant Home page: http://partha.tripod.com *** Sent via Developersdex http://www.developersdex.com ***
[quoted text, click to view] On Sun, 30 May 2004 23:59:03 +0800, Agnes wrote:
>I know how to pass the parameter to store procedure, however, can it return
>some item results ???
>I want to get back the name,address1---4, Thanks a lot
>
>my code is
>CREATE PROCEDURE dbo.companyinfo_shipper_search
>@searchcode varchar(10)
>
>as
>select code,name,country,address1,address2,address3,address4 ,telno,faxno
>from companyheader
>where code = @searchcode
>GO
>
>From Agnes
>
Hi Agnes, Do you mean like this? CREATE PROCEDURE dbo.companyinfo_shipper_search @searchcode varchar(10), @name varchar(25) output, @address1 varchar(50) output, @address2 varchar(50) output, @address3 varchar(50) output, @address4 varchar(50) output as select @name = name, @address1 = address1, @address2 = address2, @address3 = address3, @address4 = address4 from companyheader where code = @searchcode GO Best, Hugo --
I know how to pass the parameter to store procedure, however, can it return
some item results ???
I want to get back the name,address1---4, Thanks a lot
my code is
CREATE PROCEDURE dbo.companyinfo_shipper_search
@searchcode varchar(10)
as
select code,name,country,address1,address2,address3,address4 ,telno,faxno
from companyheader
where code = @searchcode
GO
From Agnes
Here's how you need to run this stored procedure in Query Analyzer --Create variables to hold output parameters declare @name varchar(25), declare @address1 varchar(50), declare @address2 varchar(50) , declare @address3 varchar(50), declare @address4 varchar(50) --execute sp exec dbo.companyinfo_shipper_search 'testcode', @name output, @address1 output, @address2 output, @address3 output, @address4 output Then you should not get any error. Make sure you have created the stored procedure exactly as suggested by Hugo. [quoted text, click to view] > as
> select @name = name, @address1 = address1, @address2 = address2,
> @address3 = address3, @address4 = address4
> from companyheader
> where code = @searchcode
> GO
Regards Partha Mandayam Software Consultant Home page: http://partha.tripod.com *** Sent via Developersdex http://www.developersdex.com ***
Dear Hugo, As I run the following programe , I got an error in SQL Analyzer Procedure 'companyinfo_shipper_search' expects parameter '@name', which was not supplied. What does it mean ?? Please help ~~~ [quoted text, click to view] > Hi Agnes,
>
> Do you mean like this?
>
> CREATE PROCEDURE dbo.companyinfo_shipper_search
> @searchcode varchar(10),
> @name varchar(25) output,
> @address1 varchar(50) output,
> @address2 varchar(50) output,
> @address3 varchar(50) output,
> @address4 varchar(50) output
> as
> select @name = name, @address1 = address1, @address2 = address2,
> @address3 = address3, @address4 = address4
> from companyheader
> where code = @searchcode
> GO
>
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Remove the commas at the end of each DECLARE. -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql [quoted text, click to view] "Agnes" <agnes@dynamictech.com.hk> wrote in message
news:e66bW%239REHA.2552@TK2MSFTNGP11.phx.gbl...
declare @name varchar(25), declare @address1 varchar(50), declare @address2 varchar(50) , declare @address3 varchar(50), declare @address4 varchar(50) exec dbo.companyinfo_shipper_search 'testcode', @name output, @address1 output, @address2 output, @address3 output, @address4 output Thanks Partha Mandayam, Do u mean I put the above code in SQL analyzer and run it ?? However, I am fail again. it said "Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'declare'. Please help ~~
declare @name varchar(25),
declare @address1 varchar(50),
declare @address2 varchar(50) ,
declare @address3 varchar(50),
declare @address4 varchar(50)
exec dbo.companyinfo_shipper_search 'testcode',
@name output, @address1 output, @address2 output, @address3 output,
@address4 output
Thanks Partha Mandayam, Do u mean I put the above code in SQL analyzer and
run it ??
However, I am fail again. it said "Server: Msg 156, Level 15, State 1, Line
2
Incorrect syntax near the keyword 'declare'.
Please help ~~
I strongly recommend you to test the code in the solution you are providing. Otherwise explicitly state that it is not tested. -- Roji. P. Thomas SQL Server Programmer [quoted text, click to view] "Partha Mandayam" <mcp111@hotmail.com> wrote in message news:O5jLys0REHA.3220@TK2MSFTNGP10.phx.gbl... > Here's how you need to run this stored procedure in Query Analyzer > > --Create variables to hold output parameters > > > declare @name varchar(25), > declare @address1 varchar(50), > declare @address2 varchar(50) , > declare @address3 varchar(50), > declare @address4 varchar(50) > > --execute sp > > exec dbo.companyinfo_shipper_search 'testcode', > @name output, @address1 output, @address2 output, @address3 output, > @address4 output > > Then you should not get any error. Make sure you have created the stored > procedure exactly as suggested by Hugo. > > > as > > select @name = name, @address1 = address1, @address2 = address2, > > @address3 = address3, @address4 = address4 > > from companyheader > > where code = @searchcode > > GO > > > Regards > > Partha Mandayam > Software Consultant > Home page: http://partha.tripod.com > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Don't see what you're looking for? Try a search.
|
