Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IF statement with subquery problem


IF statement with subquery problem

Author
Message
zilla
zilla
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
I have a sub query within my IF statement that evaluates to one single number that I am using for a comparison operator. I am thinking there is something wrong with my syntax in my IF statement because I am not getting any results.

Here is the code snippet.


IF('USE ' +@DB_Name+ '
SELECT version_number
FROM '+ ''+@DB_Name+ '' +'.dbo.schema_version') LIKE '4.%'
BEGIN
...stuff to do
END



If I change my IF to PRINT it says: Incorrect syntax near the keyword 'LIKE'.

Any ideas why this might be failing?

Thanks.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
zilla (12/17/2012)
I have a sub query within my IF statement that evaluates to one single number that I am using for a comparison operator. I am thinking there is something wrong with my syntax in my IF statement because I am not getting any results.

Here is the code snippet.


IF('USE ' +@DB_Name+ '
SELECT version_number
FROM '+ ''+@DB_Name+ '' +'.dbo.schema_version') LIKE '4.%'
BEGIN
...stuff to do
END



If I change my IF to PRINT it says: Incorrect syntax near the keyword 'LIKE'.

Any ideas why this might be failing?

Thanks.


There are so many things wrong here it is hard to know where to start.

First you are using an IF statement with no condition. Almost like you are thinking that somehow a string will automagically get executed if it in a IF. All you have is a string and you are trying to somehow add the LIKE condition to a string or something. Also, there is no reason to add the Use @DBName because you are fully qualifying the database in the query. ;-)

What you need to do is use the EXISTS condition. I am not quite sure why you have to dynamically check the database here. Is this part of an update script and you want to do something to each database if the version is like 4. or something along those lines?

If you can explain what you are trying to do this should be pretty straight forward.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
zilla
zilla
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
What can I say, I make a lousy DB guy Smile This is one of those projects were nobody wanted to touch it, but somehow I became to owner of it (and I haven't touched SQL stuff in years). We have 700 databases that I need to suck the data out of and give the exported files to someone else so that they can run some kind of statistical analysis on the data.

So to do this I have a stored procedure that opens a cursor and loops through every database and (via the variable @DB_Name and runs a simple SELECT statement on the database(s). There are 3 different SELECT statements that can be run and I am trying to use IF statements to set up the conditions.

At first I had something like this:


SELECT @Version = 'USE ' +@DB_Name+ '
SELECT version_number
FROM '+ ''+@DB_Name+ '' +'.CQ_DBO.schema_version'


IF @Version LIKE '4.%' AND @Version NOT LIKE '4.3%'
BEGIN
...some stuff to do
END


Then I was told that you can't use dynamic SQL variables like scalar variables. I read somewhere you could use sp_executesql to create an output parameter, but I couldn't get the syntax to work.

Soooo, that is what lead me to try a subquery in the IF statement. @version should resolve to a number (4.3, 4.1, etc.) each iteration through the cursor. My thinking was something along the lines of this:


IF('The number from the SELECT statement' = '4.%' AND != '4.3%'
BEGIN
...do something
END

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
aha!!! I had a feeling this was a loop through all the databases on my server and do something kind of situation. In general cursors are to be absolutely avoided. There are however cases where they are the only tool for the job and this happens to be one of those cases. You might take a look at the undocumented sp_msforeachdb stored proc. It builds a cursors under the hood and provides some ability for doing this type of thing a bit easier. If you get hung up in trying to implement that give me a shout back and I will put something together that should help. ;-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 7425
zilla (12/17/2012)
I have a sub query within my IF statement that evaluates to one single number that I am using for a comparison operator. I am thinking there is something wrong with my syntax in my IF statement because I am not getting any results.

Here is the code snippet.


IF('USE ' +@DB_Name+ '
SELECT version_number
FROM '+ ''+@DB_Name+ '' +'.dbo.schema_version') LIKE '4.%'
BEGIN
...stuff to do
END



If I change my IF to PRINT it says: Incorrect syntax near the keyword 'LIKE'.

Any ideas why this might be failing?

Thanks.


You are looking for something like this (i'm using a random sys table for this sample so you can copy/paste & test):


EXEC sp_msforeachdb '
USE [?];
IF EXISTS (SELECT * FROM sys.schemas WHERE schema_id=1)
BEGIN
PRINT ''yay!''
END';



I was going mention that sp_msforeachdb is not documented but Sean beat me to it. There are better ways to do this - I am showing one.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3944 Visits: 6678
sp_MSforeachdb is convenient, but it has huge overhead and is probably overkill for what you need.

You can use sp_executesql to return a value from dynamic SQL, albeit that it's not straightforward. Still, it's a good technique to know, so here it is:


DECLARE @sql nvarchar(4000)
DECLARE @version varchar(100) --or whatever

SET @sql = N'SELECT TOP (1) @version = version_number ' +
'FROM ['+ @DB_Name+ N'].dbo.schema_version '

EXEC sp_executesql @sql, N'@version varchar(100) OUTPUT', @version OUTPUT

IF @version LIKE ...

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
As Scott said learning the dynamic sql technique is a good one. And he is correct that sp_msforeachdb has a bit of overhead. I would however say that with 700 databases it isn't overkill. You are going to have to write a cursor to traverse that many databases. I doubt that a roll your own variety will have that much less impact than the built in one. You could certainly try both and test performance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
zilla
zilla
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
I finally got around to trying some of this stuff this morning. I figured using the IF EXISTS should be pretty easy to put in place.

Here is the new code.


IF EXISTS ('SELECT version_number
FROM '+ ''+@DB_Name+'' +'.dbo.schema_version
WHERE version_number LIKE ''4.%'' AND version_number NOT LIKE ''4.3%''')
BEGIN

....stuff
END


It kicks off his error though: Incorrect syntax near 'SELECT version_number
FROM '.

I am sure I probably have a quote out of place somewhere....
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
zilla (12/18/2012)
I finally got around to trying some of this stuff this morning. I figured using the IF EXISTS should be pretty easy to put in place.

Here is the new code.


IF EXISTS ('SELECT version_number
FROM '+ ''+@DB_Name+'' +'.dbo.schema_version
WHERE version_number LIKE ''4.%'' AND version_number NOT LIKE ''4.3%''')
BEGIN

....stuff
END


It kicks off his error though: Incorrect syntax near 'SELECT version_number
FROM '.

I am sure I probably have a quote out of place somewhere....


I guess you didn't understand the point we are all making about this. Just because you put together a string it is NOT going to automagically execute that string. Nowhere in your code do you execute this string. All you are looking at is a string, it does not pull any data from the database. Look closely at the two examples posted. The first is using the sp_msforeachdb and the second is using pure dynamic sql. In both cases there is an EXEC. That is what executes the query you built. You just can't do it the way you are trying.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
zilla
zilla
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
I left a bunch of the code out on purpose to keep the script short. I have an EXEC in my code for what I want executed, I just need the IF condition to be correct (both in syntax and logic).

Here is the code complete script.


-- Stored Procedure to retrieve PeerReview data from schema version 4.x, 4.3, and 5.x

DECLARE @DB_Name nvarchar(200)
DECLARE @Version nvarchar(200)
DECLARE @PeerReview4x nvarchar(2000)
DECLARE @PeerReview43 nvarchar(2000)
DECLARE @PeerReview5x nvarchar(2000)

DECLARE database_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE UPPER(name) LIKE 'CQ%PROD'

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN

IF EXISTS ('USE ' +@DB_Name+ '
SELECT version_number
FROM '+ ''+@DB_Name+'' +'.CQ_DBO.schema_version
WHERE version_number LIKE ''4.%'' AND version_number NOT LIKE ''4.3%''')
BEGIN
SELECT @PeerReview4x = 'USE ' +@DB_Name+ '
SELECT '+ ''''+@DB_Name+ '''' +' as DB_Name, ''PeerReview'' AS record_type, T1.peerreviewid, T1.attached_script, T1.author, T1.comments, T1.corrective_time, T1.creation_date, T1.description,
T1.electronic_path, T1.email_subject_start, T1.emails_of_participants, T1.eng_discipline, T1.facilitator, T1.measure_name1, T1.measure_name2,
T1.measure_name3, T1.measure_value1, T1.measure_value2, T1.measure_value3, T1.meeting_date, T1.okay_no_actionitems, T1.program_phase,
T1.programspecificdata1, T1.programspecificdata10, T1.programspecificdata11, T1.programspecificdata2, T1.programspecificdata3,
T1.programspecificdata4, T1.programspecificdata5, T1.programspecificdata6, T1.programspecificdata7, T1.programspecificdata8,
T1.programspecificdata9, T1.fldcolumn AS ''references'', T1.review_method, T1.sdt_states, T2.name as state, T1.status, T1.total_action_items,
T1.total_attendees, T1.total_avg_time, T1.total_defect_ai, T1.total_mtg_time, T1.total_prep_time, T1.total_time, T1.fldcolumn_1 as type, T1.reviewtype,
T1.work_product_available
FROM '+ ''+@DB_Name+ '' +'.CQ_DBO.schema_version T0,
'+ ''+@DB_Name+ '' +'.CQ_DBO.peerreview T1 INNER JOIN
'+ ''+@DB_Name+ '' +'.CQ_DBO.statedef T2 ON T1.state = T2.id INNER JOIN
'+ ''+@DB_Name+ '' +'.CQ_DBO.enttable_1 T3 ON T1.security_context = T3.dbid
WHERE(T1.dbid <> 0)'

EXEC sp_executesql @PeerReview4x
END

--Get the next database in the cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
END

--Cleanup
CLOSE database_cursor
DEALLOCATE database_cursor

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search