SELECT. Need advice. How should I code this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

SELECT. Need advice. How should I code this?

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-04 : 08:18:15

Hello,

I need a procedure to return the value of field [ContentHtml] given values for [ContentName] and [ContentCulture].

In theory only one record will be found so only one [ContentHtml] value will be returned.

If multiple records are found I want to return "#MultipleFound#".
If no record is found I want to return "#NotFound#".

In my .NET code I am executing a ExecuteScalar:
Dim Html As String = CType(command.ExecuteScalar, String)

I believe I should use @@ROWCOUNT to do this.

Could somebody explain to me how to integrate @@ROWCOUNT in my procedure code to achieve what I described?

And should I do it this way or there is a better solution?

My Procedure is as follows:

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

AS

-- Allows @@ROWCOUNT and the return of number of records when ExecuteNonQuery is used
SET NOCOUNT OFF;

-- 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

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 08:35:16

Already gave you hint here:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75765[/url]

Harsh Athalye
India.
"Nothing is Impossible"

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-04 : 09:55:59

Harsh,

But I need to return the content "#NotFound#" or "#MultipleFound#" in [ContentHtml] because in my .NET code I am reading:

Dim html As String = CType(command.ExecuteScalar, String)

Do you mean I can make:
SET @FLAG = "#NotFound"

And my .NET code will read it?

Sorry, but I am starting with SQL and somethings are still a little bit confusing for me. Specially how values are returned from the procedures and how the .NET code seems them.

Thanks,
Miguel

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 10:32:57

You haven't defined output for condition when only single record is found, I assumed it to be '#Found#':

Create Procedure usp_SomeProc
(
@ContentName NVARCHAR(100),
@ContentCulture NVARCHAR(5)
)
AS

SET NOCOUNT ON

DECLARE @ContentId UNIQUEIDENTIFIER,
	@RowCount int,
	@Status varchar(20)

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 @RowCount = count(*)
	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
	
	if @RowCount = 0 
		Set @Status = '#NotFound#'
	else
	begin
		if @RowCount = 1
			Set @Status = '#Found#'
		else
			Set @Status = '#MultipleFound#'
	end
END
Else
	Set @Status = '#NotFound#'

Select @Status as Status
GO

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources