March 24, 2010 at 11:31 am
Hi,
I have a t-sql condition that i want to evaluate and if it returns 0 then i want to execute a instruction.
This is the condition to be evaluated:
exec ('if (select count(*) from '+@nome+'sys.objects where name = ''sp_actualizaaut''=0')
begin
select 'ola'
end
the @nome is a variable, that's why it as to be inside '++'
When i execute the procedure i always receive an error like this:
"Incorrect syntax near '0'."
March 24, 2010 at 11:46 am
How does this work?
exec ('declare @cnt int;select @cnt=count(*) from ' + @nome + 'sys.objects where name = ''sp_actualizaaut'';if @cnt > 0 select ''ola'' else select ''no ola''')
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2010 at 7:21 am
Thank you very much
April 6, 2010 at 1:24 pm
WayneS (3/24/2010)
How does this work?
exec ('declare @cnt int;select @cnt=count(*) from ' + @nome + 'sys.objects where name = ''sp_actualizaaut'';if @cnt > 0 select ''ola'' else select ''no ola''')
That will work (except that the test in the original was "=", not ">") but there's no need to introduce the variable @cnt or split the thing into two separate statements; the following will also work and seems to me to be a bit closer to the original:
exec ('if (select COUNT(*) from '+@nome+'sys.objects where name = ''sp_actualizaaut'') = 0
begin
print ''ola''
end ')
Tom
April 7, 2010 at 1:21 am
Thanks TOM
April 7, 2010 at 7:19 am
Wayne/Tom, why are we not using exists()?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 7, 2010 at 7:37 am
because i asked with count(*) instead of exists
April 7, 2010 at 4:59 pm
river1 (4/7/2010)
because i asked with count(*) instead of exists
That's right, your question was about the syntax of dynamic SQL involving an"if", so I wanted to keep it as close as possible to the original so that it could be clear what changes were needed to fix the syntax problem and not introduce any extra changes that were not needed for that purpose in case they would be thought to be needed for that. I think Wayne probably kept count for much the same reason.
Tom
April 7, 2010 at 11:07 pm
I would like to suggest:
DECLARE @Database SYSNAME;
SET @Database = 'master'
IF -- See http://msdn.microsoft.com/en-us/library/ms190328.aspx
OBJECT_ID(@Database + N'.sys.sp_help', N'P')
IS NOT NULL
PRINT 'Ola!';
April 8, 2010 at 5:59 am
Paul White NZ (4/7/2010)
I would like to suggest:
DECLARE @Database SYSNAME;
SET @Database = 'master'
IF -- See http://msdn.microsoft.com/en-us/library/ms190328.aspx
OBJECT_ID(@Database + N'.sys.sp_help', N'P')
IS NOT NULL
PRINT 'Ola!';
:w00t:
Would work for me except that that NOT gets in the way;-). And you've copied my slip of the pen (PRINT instead of SELECT):blush:. And I'm not sure that sys.sp_actualizaaut is in every database that has sys.sp_help (:ermm:I've never heard of sp_actualizaaut before).
😀
Tom
April 8, 2010 at 6:44 am
Tom.Thomson (4/8/2010)
:w00t:Would work for me except that that NOT gets in the way;-). And you've copied my slip of the pen (PRINT instead of SELECT):blush:. And I'm not sure that sys.sp_actualizaaut is in every database that has sys.sp_help (:ermm:I've never heard of sp_actualizaaut before).
😀
:blink: Not really, Tom, no :doze:
Just illustrating the technique, as an alternative to dynamic SQL.
The details do not particularly concern me.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply