[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 :)