[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

stored procedure question

tom taol

3/29/2007 3:53:00 PM

I need to create a stored procedure thats take a parameter, then within that
stored procedure I need to run a select statement based on a different
parameter.

so I need to do something like this


select customersID from custTable where custname = @custname

then I need to take that customerID and do this

if @@rowcount = 0
'' i need to exit the stored procedure
else
'do this
select salesprice, make, model, year, salesman from salesTable where custId
= customerID

'then i need to end the stored procedure

then return that data to the screen, how can i accomplish this in a stored
procedure


2 Answers

Aaron [SQL Server MVP]

3/29/2007 4:18:00 PM

0

I'm not sure why you would want to pass the customer name into the stored
procedure. WHat if there are two John Smiths? Shouldn't someone be
selecting John A. Smith or John Q. Smith from a dropdown (which stores the
customer ID as the value), and then you could pass the customer ID in
directly?

Anyway, here is how you would continue to do it this way;

CREATE PROCEDURE dbo.Customer_GetSales
@custname NVARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;

SELECT
st.SalesPrice,
st.make,
st.model,
st.[year],
st.Salesman
FROM
SalesTable st
INNER JOIN
CustTable c
ON
c.CustomersID = st.custID
WHERE
c.custName = @CustName;
END
GO

As an aside, why on earth is a customer represented by "CustomersID" in one
table, and "CustID" in another? Do a lot of people in your shop curse and
mutter under their breath when working in the database?

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...





"Tom" <Tom@yahoo.com> wrote in message
news:%23xxvFphcHHA.4012@TK2MSFTNGP03.phx.gbl...
>I need to create a stored procedure thats take a parameter, then within
>that stored procedure I need to run a select statement based on a different
>parameter.
>
> so I need to do something like this
>
>
> select customersID from custTable where custname = @custname
>
> then I need to take that customerID and do this
>
> if @@rowcount = 0
> '' i need to exit the stored procedure
> else
> 'do this
> select salesprice, make, model, year, salesman from salesTable where
> custId = customerID
>
> 'then i need to end the stored procedure
>
> then return that data to the screen, how can i accomplish this in a stored
> procedure
>


tom taol

3/29/2007 4:46:00 PM

0

I know I do!

I just got here and took some of the work over, its a nightmare


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%237gsX3hcHHA.3960@TK2MSFTNGP04.phx.gbl...
> I'm not sure why you would want to pass the customer name into the stored
> procedure. WHat if there are two John Smiths? Shouldn't someone be
> selecting John A. Smith or John Q. Smith from a dropdown (which stores the
> customer ID as the value), and then you could pass the customer ID in
> directly?
>
> Anyway, here is how you would continue to do it this way;
>
> CREATE PROCEDURE dbo.Customer_GetSales
> @custname NVARCHAR(255)
> AS
> BEGIN
> SET NOCOUNT ON;
>
> SELECT
> st.SalesPrice,
> st.make,
> st.model,
> st.[year],
> st.Salesman
> FROM
> SalesTable st
> INNER JOIN
> CustTable c
> ON
> c.CustomersID = st.custID
> WHERE
> c.custName = @CustName;
> END
> GO
>
> As an aside, why on earth is a customer represented by "CustomersID" in
> one table, and "CustID" in another? Do a lot of people in your shop curse
> and mutter under their breath when working in the database?
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sq...
> http://www.aspfa...
>
>
>
>
>
> "Tom" <Tom@yahoo.com> wrote in message
> news:%23xxvFphcHHA.4012@TK2MSFTNGP03.phx.gbl...
>>I need to create a stored procedure thats take a parameter, then within
>>that stored procedure I need to run a select statement based on a
>>different parameter.
>>
>> so I need to do something like this
>>
>>
>> select customersID from custTable where custname = @custname
>>
>> then I need to take that customerID and do this
>>
>> if @@rowcount = 0
>> '' i need to exit the stored procedure
>> else
>> 'do this
>> select salesprice, make, model, year, salesman from salesTable where
>> custId = customerID
>>
>> 'then i need to end the stored procedure
>>
>> then return that data to the screen, how can i accomplish this in a
>> stored procedure
>>
>
>