We help IT Professionals succeed at work.

How to use "if exists" with column name as variable

lescluster
lescluster asked
on
670 Views
Last Modified: 2012-05-05
How do I use "if exists" in MS SQL 2000 with column name as variable.
Here is what I have that does not work - I am getting syntax errors near EXEC and near ELSE. :
Thanks for your help.

DECLARE @MySQL nvarchar(500)
set @MySQL = 'SELECT * from   dbo.CAL_RecCrsSchedule where ' +Left(DATENAME(weekday,@tryDate),3)+ '<>'''' and RecCrs_id=@recId'

if exists EXEC(@MySQL)
begin
--....some processing here
end
else
begin
--...some more processing here
end



Comment
Watch Question

try this

DECLARE @MySQL nvarchar(500)
set @MySQL = 'SELECT * from   dbo.CAL_RecCrsSchedule where ' +Left(DATENAME(weekday,@tryDate),3)+ '<>'''' and RecCrs_id=' + Cast(@recId as varchar)

if exists EXEC(@MySQL)
begin
--....some processing here
end
else
begin
--...some more processing here
end
Sorry, ignore first and use this

DECLARE @MySQL nvarchar(500)

if exists (SELECT * from   dbo.CAL_RecCrsSchedule where Left(DATENAME(weekday,@tryDate),3) <> '' and RecCrs_id= @recId)
begin
--....some processing here
end
else
begin
--...some more processing here
end

Author

Commented:
it wont work - the select statement alone wont even work like that. That is the whole trick -  the part "Left(DATENAME(weekday,@tryDate),3)" is actually dynamically generated column name - so as far as I know select statement should be pre-assembled and then executed in order for column name as variable to work.
Now try this as this is the only way. May be you will have to adjust something. If this doesnt work then just try remove Else @Ans = 0 and then try again.

DECLARE @MySQL nvarchar(500)
Declare @Ans Bit

SET @Ans = 0

set @MySQL = 'if exists(SELECT * from dbo.CAL_RecCrsSchedule where ' +Left(DATENAME(weekday,@tryDate),3)+ '<>'''' and RecCrs_id=@recId) @Ans=1 Else @Ans=0 End'

execute sp_executesql @MySQL , N'@tryDate DateTime, @recId int, @Ans Bit Output', @tryDate, @recid, @Ans Output

if @Ans = 1
begin
--....some processing here
end
else
begin
--...some more processing here
end
Top Expert 2007

Commented:
That's NOT the only way btw..
Top Expert 2007

Commented:
Give this a try
create table #tmp ( result bit ) -- for messaging
 
DECLARE @MySQL nvarchar(500)
  --- multi line T-SQL used for clarity, SQL Server will accept
set @MySQL = '
insert into #tmp
select case when exists (SELECT * from dbo.CAL_RecCrsSchedule
where ' +Left(DATENAME(weekday,@tryDate),3)+ '<>''''
and RecCrs_id=@recId) then 1 else 0 end'
 
delete #tmp    -- if used in loop, clear previous result
EXEC(@MySQL)
 
if (select result from #tmp) = 1
begin
--....some processing here
end
else
begin
--...some more processing here
end
 
drop table #tmp -- not necessary if in stored proc

Open in new window

Hi lescluster,
Just try this .

IF EXISTS(
SELECT * from dbo.CAL_RecCrsSchedule where CASE Left(DATENAME(weekday,GETDATE()),3)
						 WHEN 'SUN' THEN SUN 
						 WHEN 'MON' THEN MON
						 WHEN 'TUE' THEN SUN 
						 WHEN 'WED' THEN WED
						 WHEN 'THU' THEN THU 
						 WHEN 'FRI' THEN FRI
						 WHEN 'SAT' THEN SAT 
						 END <> ''
						AND  RecCrs_id=@recId )
BEGIN
--....some processing here
END
ELSE 
BEGIN
--...some more processing here
END

Open in new window

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank You all for your help in this matter.
Perhaps you can help me with this little problem as well:  When I run attached code I get errors claiming that
@RecCrs_id is not declared -  why is that - since it is declared as int?  Thanks.

declare @RecCrs_id int
Declare @start_date datetime
declare @course_id varchar(8)
declare @sct_id varchar(6)
declare @seat int
declare @bgn_time datetime
declare @end_time datetime 
declare @room varchar(10)
declare @instr_id varchar(50)
 
DECLARE cr_recId CURSOR FOR
	Select RecCrs_id, course_id, sct_id, seat, start_date, bgn_time, end_time,room, instr_id  from  dbo.CAL_RecCrsSchedule where start_date<>end_date
OPEN cr_recId
FETCH NEXT FROM cr_recId into @RecCrs_id, @course_id, @sct_id, @seat, @start_date, @bgn_time,@end_time, @room,
@instr_id
while @@fetch_status=0
begin
	declare @counter int
	declare @baseCount int
	declare @tryDate datetime
	
	set @tryDate=@start_date
	set @baseCount=1
	set @counter = (select sessions from dbo.CAL_RecCrsSchedule where RecCrs_id=@RecCrs_id)
		while @baseCount <= @counter
		begin
		-----------------------------------------------------------
			DECLARE @MySQL nvarchar(500)
			SET @MySQL = 'SELECT TOP 1 * from dbo.CAL_RecCrsSchedule where ' +Left(DATENAME(weekday,@tryDate),3)+ '<>'''' and RecCrs_id=@RecCrs_id'
			
			EXEC(@MySQL)
			--NOTE: **NO** other statement may come between EXEC and comparing/saving @@ROWCOUNT
			IF @@ROWCOUNT > 0
			begin
				print 'writing date '+ cast(@tryDate as char)
				--write date to scheduler table as class date together with the course and section info
			end	
			else
			begin
				set @tryDate=DATEADD(day,@baseCount,@start_date)
				set @baseCount = @baseCount + 1
			end
		------------------------------------------------------------
		end
	
fetch next from cr_recId into @RecCrs_id, @course_id, @sct_id, @seat, @start_date, @bgn_time,@end_time, @room,
@instr_id
end
 
CLOSE cr_recId
DEALLOCATE cr_recId

Open in new window

Top Expert 2007

Commented:
lescluster: would you like to reopen the question?
Post a Question in the Community support zone if you need to.
The solution you accepted will not work.  Have you looked at http://#20455423 ?

Author

Commented:
I guess I can post another question - solution did work perfectly - problem is associated with the posted question.

Author

Commented:

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.