[RESOLVED] Help with the SQL Query

[vuyiswam]

Good Day All

I have the Following SP

ALTER PROC GET_BASED_PATTERN
(
@SUBJECT VARCHAR(30),
@Pattern  nvarchar(128) output 
)
AS
SET @Pattern = (SELECT TOP 1 TERM.[CYCLETEMPLATES]FROM tbl_term TERM 
INNER JOIN dbo.TBL_ACTV v
on TERM.ID = V.TERM
INNER JOIN  DBO._Subjects S
ON V.TERM = S.[LEVEL]
WHERE CODE = @SUBJECT)
SELECT 
CASE 
WHEN LEN(@Pattern) > 1 THEN @Pattern
ELSE '00000000'
END

It returns

00000000 and Null

and NUll at the Bottom if the len is not greater than 1. i want it to return 00000000 if its like that.

Thank you

[Alsvha]

If I understand you correct then you want it to return 00000000 both if LEN(@Pattern) is less then 1 and @Pattern is null?

There are many ways to do this, and depending on how "pretty" you want the solution. But the easiest way would properly be to just assign a value of '' if your pattern is null, so something like this:

IF @Pattern IS NULL
 SET @Pattern = ''

SELECT 
CASE 
WHEN LEN(@Pattern) > 1 THEN @Pattern
ELSE '00000000'
END

[vuyiswam]

Thank you for your Help. i did it this way and its working.

ALTER PROC GET_BASED_PATTERN
(
@SUBJECT VARCHAR(30),
@Pattern  nvarchar(128) output 
)
AS
SET @Pattern = (SELECT TOP 1 TERM.[CYCLETEMPLATES]
FROM tbl_term TERM
INNER JOIN dbo.TBL_ACTV v 
on TERM.ID = V.TERM
INNER JOIN  DBO._Subjects S  ON 
V.TERM = S.[LEVEL]
WHERE CODE = @SUBJECT)
IF @Pattern is null Set @Pattern = '00000000'Select @Pattern

Thank you

[Alsvha]

Thank you for your Help. i did it this way and its working.

ALTER PROC GET_BASED_PATTERN
(
@SUBJECT VARCHAR(30),
@Pattern  nvarchar(128) output 
)
AS
SET @Pattern = (SELECT TOP 1 TERM.[CYCLETEMPLATES]
FROM tbl_term TERM
INNER JOIN dbo.TBL_ACTV v 
on TERM.ID = V.TERM
INNER JOIN  DBO._Subjects S  ON 
V.TERM = S.[LEVEL]
WHERE CODE = @SUBJECT)
IF @Pattern is null Set @Pattern = '00000000'Select @Pattern

Thank you

Well - that approach seem to violate your first listed example, but it might be because of a bug in the first.
Because your first - the requirement was the pattern had to be larger then 1 in length so it had to be 2 characters or more.

[vuyiswam]

I did the Count of a Length to check for Empty or Null

[Alsvha]

Well yeah - but LEN() > 1 also means that 1 letter patterns would not be filtered, and thus constraint would catch more then just empty strings.
I'd guess it should then be LEN() > 0 or >= 1, but I thought it was a restriction based on your ealier code that patterns with the length of 1 should also get a row of zeros