Checking for Nulls

[ChopinFan]

I have sprocs that execute sql statements stored as a string, i.e.:

CREATE PROCEDURE dbo.SprocName

AS
set nocount on
declare @SQL as varchar (2000)
set @SQL = 'SELECT yada yada etc.'
exec(@SQL)

Using the above procedure, I need to be able to check for null values the query may be returning and return an error message specifying which columns are null. How can I do this?

 

[AlexCuse]

Are there particular columns you need to check?

Ignorance of certain subjects is a great part of wisdom

 

[ChopinFan]

Yes, only specific columns I would test for nulls.

 

[AlexCuse]

Well, this would be pretty hard using dynamic SQL. But from the looks of things you don't need to use dynamic SQL. Can you post your whole proc?

Ignorance of certain subjects is a great part of wisdom

 

[ChopinFan]

Yes, we tried the standard method for an access data project, putting parameters in the report properties, but it didn't like it and wouldn't do what we wanted, so this was the solution we got to work. This is my whole procedure. I didn't mention earlier that it also has input parameters. Thanks!


CREATE PROCEDURE dbo.proc_MM_Profile_Specialist

@StudentNumber varchar(500),
@Country varchar (2)

AS
set nocount on
declare @SQL as varchar (2000)
set @SQL = '
SELECT Stuff(Stuff(tblLDPStudentInfo.LDPStudentID, 6, 0, ''-''), 3, 0, ''-''), dbo.tblLDPStudentInfo.LDPStudentID,dbo.tblLDPStudentInfo.FullTime, dbo.tblLDPCompassStudentData.BirthDate,
dbo.tblLDPCompassStudentData.StudentName,dbo.tblLDPStudentInfo.Thesis, dbo.tblLDPStudentInfo.CourseOfStudy, dbo.tblLDPStudentInfo.EnrolledDate, dbo.tblLDPUniversity.UnivName,
dbo.tblLDPUniversityLocation.City, dbo.tblLDPStudentInfo.ProgramDuration,dbo.tblRegions.RegionID, dbo.tblRegions.RegionDescription, dbo.vw_RS_LatestWhereLiving.Location, dbo.vw_RS_LatestWhereLiving.IfOtherExplain,
dbo.tblLDPStudentInfo.Apprenticeship, dbo.tblLDPStudentInfo.License, dbo.tblLDPStudentInfo.DescribeCourse, dbo.tblLDPStudentInfo.SpecialAchievements, dbo.tblLDPStudentInfo.ReasonsSelected
FROM dbo.tblLDPStudentInfo INNER JOIN
dbo.tblLDPCompassStudentData ON dbo.tblLDPStudentInfo.LDPStudentID = dbo.tblLDPCompassStudentData.LDPStudentID INNER JOIN
dbo.tblRegions ON dbo.tblLDPStudentInfo.RegionID = dbo.tblRegions.RegionID INNER JOIN
dbo.tblCountry ON dbo.tblRegions.CountryID = dbo.tblCountry.CountryID INNER JOIN
dbo.vw_RS_LatestUnivTransferInfo ON dbo.tblLDPStudentInfo.LDPStudentID = dbo.vw_RS_LatestUnivTransferInfo.LDPStudentID INNER JOIN
dbo.tblLDPUniversityLocation ON dbo.vw_RS_LatestUnivTransferInfo.LDPSUnivLocID = dbo.tblLDPUniversityLocation.LDPSUnivLocID INNER JOIN
dbo.tblLDPUniversity ON dbo.tblLDPUniversityLocation.LDPSUnivID = dbo.tblLDPUniversity.LDPSUnivID INNER JOIN
dbo.vw_RS_LatestWhereLiving ON dbo.tblLDPStudentInfo.LDPStudentID = dbo.vw_RS_LatestWhereLiving.LDPStudentID
WHERE (right(dbo.tblLDPStudentInfo.LDPStudentID,4) in (' +@StudentNumber+ ')) and (dbo.tblCountry.CountryID in('''+@Country+'''))'

exec (@SQL)

 

[AlexCuse]

I was afraid of that. Here is a function to split a comma-separated string into a table variable:

[COLOR=blue]CREATE[/color] [COLOR=#FF00FF]FUNCTION[/color] [dbo].[Split]
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue [COLOR=blue]table[/color] 
(
		
	Id [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1),
	[COLOR=blue]Value[/color] nvarchar(100)
) 
[COLOR=blue]AS[/color]  
[COLOR=blue]BEGIN[/color]

[COLOR=blue]While[/color] ([COLOR=#FF00FF]Charindex[/color](@SplitOn,@List)>0)
[COLOR=blue]Begin[/color]  
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @RtnValue ([COLOR=blue]value[/color])
[COLOR=blue]Select[/color] 
    [COLOR=blue]Value[/color] = [COLOR=#FF00FF]ltrim[/color]([COLOR=#FF00FF]rtrim[/color]([COLOR=#FF00FF]Substring[/color](@List,1,[COLOR=#FF00FF]Charindex[/color](@SplitOn,@List)-1))) 
    [COLOR=blue]Set[/color] @List = [COLOR=#FF00FF]Substring[/color](@List,[COLOR=#FF00FF]Charindex[/color](@SplitOn,@List)+len(@SplitOn),len(@List))
[COLOR=blue]End[/color]  
    [COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @RtnValue ([COLOR=blue]Value[/color])
    [COLOR=blue]Select[/color] [COLOR=blue]Value[/color] = [COLOR=#FF00FF]ltrim[/color]([COLOR=#FF00FF]rtrim[/color](@List))

    [COLOR=blue]Return[/color]
[COLOR=blue]END[/color]

Use that to split your string into a table variable, like this:

[COLOR=blue]declare[/color] @stuNumber (snum [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] @stuNumber
[COLOR=blue]select[/color] dbo.Split(@StudentNumber, [COLOR=red]','[/color])

You can then write your query to inner join to this table variable, and it will only display student numbers that were in your original string.

As far as filtering for nulls, it now becomes as simple as adding where SomeColumn is not null to your where clause.

Does this make sense?

I hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom