Need Help with Query Please - 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 2012 Forums

Transact-SQL (2012)

Need Help with Query Please

Author

Topic

trepidity21
Starting Member

1 Post

Posted - 2014-12-22 : 01:43:10

Hi All,
I am running into a strange issue with a query I am trying to write I cannot figure out yet. I have re-written this query about 15 different ways at this point. The issue I am running into now is with the WHERE clause.

It should be comparing a NOT Null Int column to an Int variable, and only returning the records from the query where that column equals the variable value:
where col = @someValue

I know the result set has the value, if I remove the where clause I can see it in the resultset. However adding the clause yields zero records:


Declare @SiteId int 
Declare @ArsYear int = 2014
Declare	@OrganizationId int = 1510
Declare	@QuestionIds varchar(max) = '7114,7115'

Declare @SurveyInstanceId int
	SET NOCOUNT ON;
	If(@SiteId is null)
	Begin
		Select @SurveyInstanceId = si.SurveyInstanceId
		from dbo.SurveyInstance si
		join dbo.Survey s on (si.SurveyId = s.SurveyId)
		where
		si.SiteId is null
		and si.OrganizationId = @OrganizationId
		and s.ArsYear = @ArsYear
	End
	Else
	Begin
		Select @SurveyInstanceId = si.SurveyInstanceId
		from dbo.SurveyInstance si
		join dbo.Survey s on (si.SurveyId = s.SurveyId)
		where
		si.SiteId = @SiteId
		and si.OrganizationId = @OrganizationId
		and s.ArsYear = @ArsYear
	End

	Select q.QuestionId,
	q.QuestionGlobalId,
	qs.QuestionStatusTypeId,
	qs.SurveyInstanceId
	from
	dbo.SurveyInstanceQuestionStatus qs
	Join
	dbo.SplitStrings_CTE(@QuestionIds, ',') qg on(qs.QuestionId = qg.Item)
	join
	dbo.Question q on (q.QuestionId = qs.Questionid)
	where qs.SurveyInstanceId = @SurveyInstanceId

Yields no results


Declare @SiteId int 
Declare @ArsYear int = 2014
Declare	@OrganizationId int = 1510
Declare	@QuestionIds varchar(max) = '7114,7115'

Declare @SurveyInstanceId int
	SET NOCOUNT ON;
	If(@SiteId is null)
	Begin
		Select @SurveyInstanceId = si.SurveyInstanceId
		from dbo.SurveyInstance si
		join dbo.Survey s on (si.SurveyId = s.SurveyId)
		where
		si.SiteId is null
		and si.OrganizationId = @OrganizationId
		and s.ArsYear = @ArsYear
	End
	Else
	Begin
		Select @SurveyInstanceId = si.SurveyInstanceId
		from dbo.SurveyInstance si
		join dbo.Survey s on (si.SurveyId = s.SurveyId)
		where
		si.SiteId = @SiteId
		and si.OrganizationId = @OrganizationId
		and s.ArsYear = @ArsYear
	End

	Select q.QuestionId,
	q.QuestionGlobalId,
	qs.QuestionStatusTypeId,
	qs.SurveyInstanceId
	from
	dbo.SurveyInstanceQuestionStatus qs
	Join
	dbo.SplitStrings_CTE(@QuestionIds, ',') qg on(qs.QuestionId = qg.Item)
	join
	dbo.Question q on (q.QuestionId = qs.Questionid)

Yields the full resultset. I have tested by adding a
Select @SurveyInstanceId
line to see the value, and harcoding the value to something I know is in the resultset.

Any ideas what could be causing that?

Thanks so much in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-22 : 09:51:31

post a few rows of the result set from the second query along with a value for @SurveyInstanceId that you think it should find.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources