query help - 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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-09-04 : 06:19:46

in the below code the select stm is returning 1 then also why I am getting the else part answer and not if part


 BEGIN
 declare @i int
 declare @isql varchar(max)
 declare @msg varchar(max)
  set  @isql= ' select  1 from NormEtc_Hosts_Equiv WHERE left(lump,1) not in (''#'') AND isnull(lump,'''')<>'''' '
  exec (@isql)
  if @isql='1'
   SET @msg=  'File  is empty.'
  ELSE
    SET @msg=  'File  does not exist.'
  END
  print @msg

out put

result
1

--(1 row(s) affected)
File does not exist.

-Neil

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-09-04 : 06:31:11

[code]
BEGIN
declare @i int
declare @isql varchar(max)
declare @msg varchar(max)
set @isql= ' select 1 from NormEtc_Hosts_Equiv WHERE left(lump,1) not in (''#'') AND isnull(lump,'''')<>'''' '
exec (@isql)
if @isql='1'
BEGIN
SET @msg= 'File is empty.'
END
ELSE
BEGIN
SET @msg= 'File does not exist.'
END
END
print @msg
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 06:36:33

Why do you use dynamic sql?
if exists(select 1 from NormEtc_Hosts_Equiv WHERE lump is not null and left(lump,1) <> '#')
begin
...
end

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 06:38:22

quote:

Originally posted by RickD


BEGIN
	declare @i int
	declare @isql varchar(max)
	declare @msg varchar(max)
	set  @isql= ' select  1 from NormEtc_Hosts_Equiv WHERE left(lump,1) not in (''#'') AND isnull(lump,'''')<>'''' '
	exec (@isql)
	if @isql='1'
	BEGIN
		SET @msg=  'File  is empty.'
	END
	ELSE
	BEGIN
		SET @msg=  'File  does not exist.'
	END
END
print @msg

How should @isql get the value of '1'?

Too old to Rock'n'Roll too young to die.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-09-04 : 07:06:55

[code]
' select 1 from NormEtc_Hosts_Equiv WHERE left(lump,1) not in (''#'') AND isnull(lump,'''')<>'''' '
[/code]

The above query will return 1 based on condition, I am using dynamic because for the table will be created with different columns based on certain conditions ( file exists and file exists not) hence if i do not use dynamic it will throw err.. and at run time it will be clear

-Neil

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-09-04 : 07:53:40

quote:

Originally posted by webfred

quote:

Originally posted by RickD


BEGIN
	declare @i int
	declare @isql varchar(max)
	declare @msg varchar(max)
	set  @isql= ' select  1 from NormEtc_Hosts_Equiv WHERE left(lump,1) not in (''#'') AND isnull(lump,'''')<>'''' '
	exec (@isql)
	if @isql='1'
	BEGIN
		SET @msg=  'File  is empty.'
	END
	ELSE
	BEGIN
		SET @msg=  'File  does not exist.'
	END
END
print @msg

How should @isql get the value of '1'?

Too old to Rock'n'Roll too young to die.

Why did you quote me? I just answered the question asked, but 1 will be the return value if the SQL is true (SELECT 1 FROM...)

The reason it was failing is because the OP had missed out BEGIN and END.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 08:12:33

[code]
DECLARE @SQLString nvarchar(max);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @result int;

declare @msg varchar(max)

SET @SQLString = N'select @resultOUT = 1 from NormEtc_Hosts_Equiv WHERE left(lump,1) not in (''#'') AND isnull(lump,'''')<>''''
SET @ParmDefinition = N'@resultOUT int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @resultOUT=@result OUTPUT;
if @result=1
begin
set @msg = 'File is empty'
end
else
begin
set @msg = 'Filed does not exist'
end
print @msg
[/code]

Too old to Rock'n'Roll too young to die.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-09-04 : 08:24:16

Thanks webfred & RickD

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources