Procedure problem. - 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)

Procedure problem.

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-14 : 09:08:56

Hello,

...
@LevelName NVARCHAR(100)
...

DECLARE @LevelId UNIQUEIDENTIFIER
IF EXISTS (SELECT @LevelId = LevelId FROM dbo.Levels WHERE LevelName = @LevelName)
BEGIN
SELECT *
FROM dbo.Documents
WHERE LevelId = @LevelId
END

I am getting an error in this stored procedure. Any idea why?
Basically all I need is to check if there is any Level with given LevelName and get its LevelId. Then go to documents table and get all documents which has that LevelId.

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 09:13:36

Why don't you use a INNER JOIN ?


select d.*
from   dbo.Documents d inner join dbo.Levels l
       on d.LevelId = l.LevelID
where  l.LevelName = @LevelName

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-14 : 09:35:47

Can I set an output parameter such as @Feedback to -1 if no Level is found for given LevelName.

It is just that I use the parameter @Feedback to return status codes so that in my .NET code I can see what is going on.

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-14 : 09:36:54

My entire stored procedure is as follows:

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

-- Define the procedure parameters
@LevelName NVARCHAR(100),
@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
DECLARE @LevelId UNIQUEIDENTIFIER
IF EXISTS (SELECT @LevelId = LevelId FROM dbo.by27_Levels WHERE LOWER(LevelName) = LOWER(@LevelName))
BEGIN

-- Select document for given DocumentId
SELECT *
FROM dbo.by27_Documents
WHERE LevelId = @LevelId

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

END

ELSE

-- Define feedback value
SELECT @Feedback = -1

END -- by27_Documents_GetDocumentByLevel

-- Run procedure definition code
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 09:37:35

[code]
select d.*
from dbo.Documents d inner join dbo.Levels l
on d.LevelId = l.LevelID
where l.LevelName = @LevelName

if @@rowcount = 0 select @Feedback = -1
else select @Feedback = 0
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 09:38:26

[code]DECLARE @LevelId UNIQUEIDENTIFIER

SELECT @LevelId = LevelId
FROM dbo.Levels
WHERE LevelName = @LevelName

IF @LevelID IS NULL
SET @FeedBack = 0
ELSE
BEGIN
SELECT *
FROM dbo.Documents
WHERE LevelId = @LevelId

SET @Feedback = -1
END[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources