EXISTS error - 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 2005 Forums

Transact-SQL (2005)

EXISTS error

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 11:57:06

Hi,

Why is this giving me an error:
DECLARE @Document BIT
SET @Document = EXISTS(SELECT * FROM dbo.Documents WHERE DocumentId = @DocumentId)

I am just trying to set @Document to 1 if a record exists or 0 if it does not.

I am getting the error:
Line 29 Incorrect syntax near the keyword 'EXISTS'.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 12:00:21

Either do
SELECT @Document = SIGN(COUNT(*)) FROM dbo.Documents WHERE DocumentId = @DocumentId

or this
IF EXISTS(SELECT * FROM dbo.Documents WHERE DocumentId = @DocumentId)
SET @Document = 1
ELSE
SET @Document = 0

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-12 : 12:00:59

SET @Doucment = 0
SELECT @Document = SELECT 1 FROM dbo.Documents WHERE DocumentId = @DocumentId

However, I would guess the indicator isn't needed

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 12:03:43

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 12:07:18

Peso,

My idea is to have various declaration as follows:
SELECT @Document = SIGN(COUNT(*)) FROM dbo.Documents WHERE DocumentId = @DocumentId
SELECT @User = SIGN(COUNT(*)) FROM dbo.Users WHERE UserId = @UserId
...

CASE
WHEN @Document AND NOT @User THEN @Feedback = -1
WHEN NOT @Document AND @User THEN @Feedback = -2
WHEN NOT @Document AND NOT @User THEN @Feedback = -3
...

I suppose this can be done right?
I am using BIT because I think, but I am not sure, that BIT it works as Boolean in SQL.

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 12:15:16

Sorry Peter.

Usually in .NET I post a thread for each problem I have but I very often receive the message to separate the problem in various parts and post various threats concerning each problem ... I got used to do that.

Basically, what I am trying to do is:
> Check various INPUT parameters and return a value in OUTPUT parameter @Feedback according to certain conditions.
> If all conditions are OK then I would insert the record.

I followed this example:
http://aspalliance.com/688_Get_Started_with_the_Enterprise_Library_Data_Access_Application_Block#Page7

And this:
http://www.4guysfromrolla.com/webtech/102704-1.shtml

I am just starting to figure the best way to do this.

Sorry, if I posted to much posts.

I usually work with C# in .NET but now I am doing the SQL part to and somethings sometimes are a little bit confusing.

As I said I have this working but I am not sure if the best way:

-- Begin of procedure code
ALTER PROCEDURE [dbo].[by27_Levels_CreateLevel]

-- Define the procedure parameters
@LevelName NVARCHAR(100),
@LevelDescription NVARCHAR(MAX),
@Feedback INT OUTPUT

AS
BEGIN

-- When NOCOUNT is OFF, @@ROWCOUNT returns the number of records if ExecuteNonQuery is used
SET NOCOUNT ON

-- Check for an existing record with the given LevelName
IF EXISTS (SELECT * FROM dbo.by27_Levels WHERE LOWER(LevelName) = LOWER(@LevelName))

-- Define feedback value
SET @Feedback = -1

ELSE
BEGIN

-- Create new level in by27_Levels
INSERT dbo.by27_Levels (LevelName, LevelDescription)
VALUES (@LevelName, @LevelDescription)

-- Save error value in feedback
SET @Feedback = @@ERROR

END

-- Return feedback
RETURN @Feedback

END

-- Run procedure definition code
GO

.NET code:

' CreateLevel
Public Shared Function CreateLevel(ByVal levelName As LevelName, ByVal levelDescription As String) As LevelCreateStatus

' Create a new level
Try

' Create database
Dim dbBonsAlunos As Database = DatabaseFactory.CreateDatabase("By27.BonsAlunos")

' Create database command
Dim dbcBonsAlunos As DbCommand = dbBonsAlunos.GetStoredProcCommand("by27_Levels_CreateLevel")

' Add command parameters
With dbBonsAlunos
.AddInParameter(dbcBonsAlunos, "@LevelName", DbType.String, levelName.ToString)
.AddInParameter(dbcBonsAlunos, "@LevelDescription", DbType.String, levelDescription.ToString)
.AddOutParameter(dbcBonsAlunos, "@Feedback", DbType.Int32, 0)
End With

' Execute non query
dbBonsAlunos.ExecuteNonQuery(dbcBonsAlunos)

' Get feedback parameter
Dim feedback As Integer = CType(dbBonsAlunos.GetParameterValue(dbcBonsAlunos, "@Feedback"), Integer)

' Verify feedback value
Select Case feedback
Case 0
Return LevelCreateStatus.LevelCreated
Case -1
Return LevelCreateStatus.LevelAlreadyFound
Case Else
Return LevelCreateStatus.LevelNotCreated
End Select

Catch ex As Exception

' Return "NotCreated" status when an exception occurs
Return LevelCreateStatus.LevelNotCreated

End Try

End Function ' CreateLevel

See?

It is just that because I will use something like this really often I want to figure what is the best way to take care of the parameters validation.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 13:13:36

The code above seems right.
RETURN creates a new "parameter" named "RETURN_VALUE" in ADO/OLEDB, and the SET @FeedBack returns a value for that parameter.
What error do you get with the code above?

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 13:46:44

Hi Peter,

I don't get any error but now I understood that:
I don't need to use RETURN to return the @Feedback value as I declared as an Output parameter.
I didn't know that.

My other problem (in terms of making my code short) is to check, for example, various input parameters.
Consider I have 3 input parameters A, B and C.
I need to output an error code in @Feedback for each situation.

So I created the following:

DECLARE @A BIT
IF EXISTS (SELECT * FROM dbo.A WHERE A = @A)
SET @A = 1
ELSE
SET @A = 0

DECLARE @B BIT
IF EXISTS (SELECT * FROM dbo.B WHERE B = @B)
SET @B = 1
ELSE
SET @B = 0

DECLARE @C BIT
IF EXISTS (SELECT * FROM dbo.C WHERE C = @C)
SET @C = 1
ELSE
SET @C = 0

SET @Feedback =
CASE
WHEN @A = 1 AND @B = 1 AND @C = 1 THEN 0
WHEN @A = 1 AND @B = 0 AND @C = 1 THEN -1
...

IF @Feedback = 0
-- INSERT Record.

This approach does not seem very good if I need to check many parameters.

What do you think?

Maybe should I create multiple output parameters?

That's my problem. It is not a problem of error. It is more a problem of approach.

Thanks,
Miguel

P.S: The errors I get are due my trying to implement a simpler solution then the one I have.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 14:07:44

Make this simpler!

SET @Feedback =
CASE
WHEN @A + @B + @C = 3 THEN 0
ELSE -1
END

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-12 : 14:18:06

Thanks Peter. That seems a good solution.

Thanks again,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 14:34:21

Or

SET @Feedback =
CASE
WHEN @A = 0 OR @B = 0 OR @C = 0 THEN -1
ELSE 0
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 14:35:51

Or some better logic?

DECLARE @Items TINYINT

SELECT	@Items = COUNT(*)
FROM	(
		SELECT 1 AS Hit FROM dbo.TableA WHERE ColGreen = @A
		UNION ALL
		SELECT 1 FROM dbo.TableB WHERE ColRed = @B
		UNION ALL
		SELECT 1 FROM dbo.TableC WHERE ColBlue = @C
	) AS x

IF @Items < 3
	INSERT Record

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources