stored procedure query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure query

Author

Topic

ITSSQL
Starting Member

8 Posts

Posted - 2006-07-14 : 10:50:50

Hello

I have two tables

table1 has two columns as "product_id(PK)" and "product_type"
table2 has three columns as "ID (PK)", product_type and "template_code"

I have written a stored procedure to return template_code based on product_id

what I want to do is,
1. read product_type from table1 using product_id
2. read template_code from table2 using product_type

my stored procedure

Create PROCEDURE [dbo].[MyTest10Procedure]
@product_id int,
@template_code nvarchar(50) output
AS
BEGIN

SET NOCOUNT ON;

Declare @product_type int ;

Select product_type
from dbo.table1
where product_id=@product_id

Select template_code
from dbo.table2
where product_type=@product_type

END

when i execute this store procedure I get data from only table1 but no data from table2.

Can anyone suggests whats wrong here?

Thanks

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-14 : 11:02:07

Try this

Select Product_Type, Template_Code
from dbo.table1 a inner join dbo.table2 b
on a.product_type = b.product_type
where a.product_id = @product_id
and b.product_type=@product_type

datagod
Starting Member

37 Posts

Posted - 2006-07-14 : 11:02:24

Try executing this stored procedure from within Query Analyzer. You should see two result sets.
Some clients will only capture the first result set and ignore all others.

If you are still getting problems, make sure you did not accidentally create an older version of the proc in Master.

Hope it helps...

ITSSQL
Starting Member

8 Posts

Posted - 2006-07-14 : 11:15:46

Thanks, Humanpuck, I do not have product_id column in table2, so I can not use inner join htere.

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-14 : 11:24:38