April 25, 2009 at 7:11 am
Hi.
I want to call the stored procedure below with "IF EXEC IsActiveCust 1", but I get this error: "Incorrect syntax near the keyword 'EXEC'." How can I make this work?
ALTER PROCEDURE [dbo].[IsActiveUser] (@CustID int)
AS
IF EXISTS (Select * from Customer where CustomerID = @CustID)
Select 1
ELSE Select 2
Thanks!
April 25, 2009 at 7:45 am
Hi
As you implemented you have to use a temporary table to get the result into:
DECLARE @Ret TABLE (Value INT)
INSERT INTO @Ret
EXECUTE IsActiveUser
IF (1 = (SELECT TOP(1) Value FROM @Ret))
BEGIN
PRINT 'Do something'
END
I would suggest to use either "RETURN 1" in your procedure instead of SELECT, then you can call it like this:
CREATE PROCEDURE IsActiveUser
@CustId INT
AS
IF EXISTS (Select * from Customer where CustomerID = @CustId)
RETURN 1
RETURN 2
GO
DECLARE @i INT
EXECUTE @i = IsActuve
IF (@i = 1)
PRINT 'Don something'
... or use a scalar function instead of a procedure. This would allow to use it directly in your IF-clause:
CREATE FUNCTION dbo.IsActiveUser
(
@CustId INT
)
RETURNS INT
AS
BEGIN
IF EXISTS (Select * from Customer where CustomerID = @CustId)
RETURN 1
RETURN 2
END
GO
IF (dbo.IsActiveUser(1) = 1)
PRINT 'Do something'
Greets
Flo
April 25, 2009 at 7:56 am
Thanks! I investigated a little further and this works also:
ALTER PROCEDURE [dbo].[IsActiveUser] (@CustID int, @res bit OUTPUT)
AS
IF EXISTS (Select * from Customer where CustomerID = @CustID)
Select @res = 1
ELSE Select @res = 2
Then the call is:
DECLARE @IsActive as bit
Exec IsActiveUser 1234, @IsActive OUTPUT
IF @IsActive = 1 PRINT 'Yup'
ELSE IF @IsActive = 0 PRINT 'Nope'
But I was really hoping to use: IF Exec IsActiveUser 1234 = 1
But doesn't want to work.
Buz
April 25, 2009 at 7:58 am
Hi Buz
Correct, OUTPUT parameters work also. As I wrote before, if you want to use it directly in your IF-clause consider to use a user defined function.
Greets
Flo
April 25, 2009 at 8:18 am
Thanks Flo.
It's good to know that I can use a function. I guess I'll have to accept that I can't do what I want in the procedure.:crying:
Buz
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply