IF statement with subquery problem

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • What can I say, I make a lousy DB guy 🙂 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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

    "I cant 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."

    -- Itzik Ben-Gan 2001

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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....

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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....

    It's more than an out-of-place quote. Exists() can't execute your dynamic query.

    Here's a sample of what you're trying to do:

    DECLARE DBs CURSOR LOCAL FAST_FORWARD FOR

    SELECT name

    FROM sys.databases;

    DECLARE @DB NVARCHAR(MAX), @SQL NVARCHAR(MAX), @Column NVARCHAR(255);

    OPEN DBs;

    FETCH NEXT FROM DBs INTO @DB;

    WHILE @@fetch_status = 0

    BEGIN;

    SET @SQL = N'SELECT @ColName = name FROM ' + QUOTENAME(@DB) + N'.sys.columns;';

    EXEC sp_executeSQL @SQL, N'@ColName NVARCHAR(255) OUTPUT', @Column OUTPUT;

    PRINT @Column;

    FETCH NEXT FROM DBs INTO @DB;

    END;

    CLOSE DBs;

    DEALLOCATE DBs;

    What you'll need to do is modify the dynamic query so it uses your custom table. The above will run correctly on any SQL Server installation. The below will only do anything useful on a server with your table in at least one database.

    SET NOCOUNT ON;

    GO

    -- Set up some databases with the values. You won't need to do these steps.

    USE ProofOfConcept;

    GO

    CREATE TABLE dbo.schema_version

    (version_number VARCHAR(100));

    GO

    INSERT INTO dbo.schema_version

    (version_number)

    VALUES ('4.0');

    GO

    CREATE DATABASE DropMe;

    GO

    USE DropMe;

    GO

    CREATE TABLE dbo.schema_version

    (version_number VARCHAR(100));

    GO

    INSERT INTO dbo.schema_version

    (version_number)

    VALUES ('4.3');

    GO

    CREATE DATABASE DropMe2;

    GO

    USE DropMe2;

    GO

    CREATE TABLE dbo.schema_version

    (version_number VARCHAR(100));

    GO

    INSERT INTO dbo.schema_version

    (version_number)

    VALUES ('3.4');

    GO

    -- Start here. This is the part that's useful to you.

    USE master;

    GO

    DECLARE DBs CURSOR LOCAL FAST_FORWARD

    FOR

    SELECT name

    FROM sys.databases;

    DECLARE @DB NVARCHAR(MAX),

    @SQL NVARCHAR(MAX),

    @Version VARCHAR(100);

    OPEN DBs;

    FETCH NEXT FROM DBs INTO @DB;

    WHILE @@fetch_status = 0

    BEGIN;

    SET @SQL = N'USE ' + QUOTENAME(@DB)

    + N';IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = N''schema_version'' AND schema_id = 1) RETURN; SELECT @Version = version_number FROM '

    + QUOTENAME(@DB) + N'.dbo.schema_version;';

    EXEC sp_executeSQL @SQL, N'@Version VARCHAR(100) OUTPUT', @Version OUTPUT;

    PRINT @DB;

    IF @Version LIKE '4.%'

    AND @Version NOT LIKE '4.3%'

    PRINT 'Yes';

    ELSE

    IF @Version IS NOT NULL

    PRINT 'No';

    ELSE

    PRINT 'Version Number Not Available';

    SET @Version = NULL;

    FETCH NEXT FROM DBs INTO @DB;

    END;

    CLOSE DBs;

    DEALLOCATE DBs;

    GO

    -- Stop here. Following is just clean-up, and you don't need to do it.

    USE ProofOfConcept;

    GO

    DROP TABLE dbo.schema_version;

    GO

    DROP DATABASE DropMe;

    DROP DATABASE DropMe2;

    Edit: Fixed layout.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Here's the output when I run that:

    master

    Version Number Not Available

    tempdb

    Version Number Not Available

    model

    Version Number Not Available

    msdb

    Version Number Not Available

    ReportServer

    Version Number Not Available

    ReportServerTempDB

    Version Number Not Available

    ProofOfConcept

    Yes

    ProofOfConcept2000

    Version Number Not Available

    DropMe

    No

    DropMe2

    No

    As you can see, the ProofOfConcept database, which we added the table to, and put in a value that has 4.<not 3>, gets a "Yes".

    It's important to set the @Version variable to NULL after each execution, because otherwise ProofOfConcept2000, the next one on the list, would also end up "Yes", when it should be "Version Number Not Available". This is because of how SQL Server handles Output parameters. It already has a value of "4.0" because of what it found in ProofOfConcept, and the query for ProofOfConcept2000 doesn't end up resetting it. There are other ways to handle this, but setting it to NULL is safest.

    Then DropMe gets "No" because it's version 4.3.

    Then DropMe2 gets "No" because it's version 3.0.

    Does all that help?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • It works! I was finally able to make some sense of all of this and get everything to work by using an output variable. I'm just sorry you guys had to tell me same thing over and over again. Thanks for your help. 😀

  • Sometimes it takes seeing it all put together in order to understand the pieces.

    Glad we could help.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply