Return a Single String - 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

Transact-SQL (2000)

Return a Single String

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-03 : 21:00:56

Hello,

I created a stored procedure which selects a value according to ContentId.
I know that will be only one value returned or none.

So if a record is found I want to return the string contained in ContentHtml.
Else I want to return the string "NotFound"

Could somebody help me out with this?

Here is my present stored procedure:

-- Specifies the SQL-92 equals compliant behavior
SET ANSI_NULLS ON
GO

-- Specifies the SQL-92 quotation mark rules
SET QUOTED_IDENTIFIER ON
GO

-- Alter procedure
ALTER PROCEDURE [dbo].[by27_Content_GetContent]

-- Define the procedure parameters
@ContentName NVARCHAR(100),
@ContentCulture NVARCHAR(5)

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @ContentId UNIQUEIDENTIFIER;
SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName

-- Check if ContentId is Not Null
IF @ContentId IS NOT NULL
BEGIN

-- Select localized content from by27_ContentLocalized
SELECT dbo.by27_ContentLocalized.ContentHtml
FROM dbo.by27_Content
INNER JOIN dbo.by27_ContentLocalized
ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId
WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName);

END

-- Create procedure
GO

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-03 : 23:35:55

How about this?

IF EXISTS(SELECT * FROM dbo.by27_Content WHERE ContentName = @ContentName)
SELECT dbo.by27_ContentLocalized.ContentHtml
FROM dbo.by27_Content
INNER JOIN dbo.by27_ContentLocalized
ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId
WHERE dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName
ELSE
SELECT 'NotFound' as ContentHtml

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 09:33:48

>>So if a record is found I want to return the string contained in ContentHtml.
Else I want to return the string "NotFound"

If you use front end application, you can check there also

If Rs.eof then
--No records

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources