July 9, 2009 at 5:33 am
Hi,
I have a SP here which i want to get t1.CompanyID but am not sure this is correct.
And also if it is possible to get the t1.CompanyID without passing in @CompanyID into the
the select query ?
Alter PROCEDURE [dbo].[sp_GetCompany]
@CompanyID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
select t1.CompanyID FROM t1
where EXISTS
(Select d.CompanyID, e.CompanyID FROM t2 d, t3 e
where d.CompanyID=t1.CompanyID or
e.CompanyID=t1.CompanyID)
if @@error=0
begin
Commit transaction
SELECT @CompanyID = CompanyID
end
else
begin
Rollback transaction
end
END
July 9, 2009 at 6:32 am
If you try to execute it it will say you its wrong.
And it's overcomplicated.
What you need the transaction for?
Do you do any data modification here?
My version would look like this:
Alter PROCEDURE [dbo].[sp_GetCompany]
@CompanyID int OUTPUT
AS
SET NOCOUNT ON;
SELECT @CompanyID = t1.CompanyID
FROM t1
WHERE EXISTS
(Select 1
FROM t2 d
where d.CompanyID=t1.CompanyID
union all
SELECT 1
FRPM t3 e
WHERE e.CompanyID=t1.CompanyID
)
GO
_____________
Code for TallyGenerator
July 9, 2009 at 6:52 am
i want to output the CompanyID from t1 that exist in t2 & t3,
if possible to create a parameter explicitly without any input of external parameter like @CompanyID
which will be able to show the Output CompanyID.
July 9, 2009 at 7:16 am
Glad that it works thanks!
July 9, 2009 at 9:35 pm
hope someone will see this though is better that i post a new thread:
i need to add a comma to each output and got this error
'Error converting data type varchar to bigint'
Not sure how to cast @CompanyID with bigint. @CompanyID does not have null value.
Alter PROCEDURE [dbo].[sp_GetCompany]
@CompanyID bigint OUTPUT
AS
SET NOCOUNT ON;
SELECT @CompanyID = t1.CompanyID + ','
FROM t1
WHERE EXISTS
(Select 1
FROM t2 d
where d.CompanyID=t1.CompanyID
union all
SELECT 1
FRPM t3 e
WHERE e.CompanyID=t1.CompanyID
)
--SELECT @CompanyID = ISNULL(@CompanyID + ',', '') + [CompanyID]
--FROM t1
--WHERE EXISTS
--(Select 1
--FROM t2 d
--where d.CompanyID=t1.CompanyID
--union all
--SELECT 1
--FRPM t3 e
--WHERE e.CompanyID=t1.CompanyID
)
GO
July 10, 2009 at 2:26 am
the modified noting 'Error converting data type varchar to bigint'
even though CompanyID is a bigint in DB:
Alter PROCEDURE [dbo].[sp_GetCompany]
@CompanyID VARCHAR(200) OUTPUT
AS
SET NOCOUNT ON;
SELECT @CompanyID = ISNULL(CAST(@CompanyID AS VARCHAR(200))+ ',', '') + CAST([CompanyID] AS VARCHAR(200))
FROM t1
WHERE EXISTS
(Select 1
FROM t2 d
where d.CompanyID=t1.CompanyID
union all
SELECT 1
FRPM t3 e
WHERE e.CompanyID=t1.CompanyID
)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply