Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IF statement with subquery problem Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 6:39 AM
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.
Post #1397431
Posted Monday, December 17, 2012 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 13,116, Visits: 11,952
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)
Post #1397436
Posted Monday, December 17, 2012 3:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 6:39 AM
Points: 5, Visits: 13
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
Post #1397447
Posted Monday, December 17, 2012 3:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 13,116, Visits: 11,952
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)
Post #1397450
Posted Monday, December 17, 2012 3:45 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:49 AM
Points: 556, Visits: 2,581
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1397454
Posted Monday, December 17, 2012 3:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 1,970, Visits: 2,913
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1397455
Posted Tuesday, December 18, 2012 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 13,116, Visits: 11,952
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)
Post #1397786
Posted Tuesday, December 18, 2012 11:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 6:39 AM
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....
Post #1397963
Posted Tuesday, December 18, 2012 12:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 13,116, Visits: 11,952
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)
Post #1397976
Posted Tuesday, December 18, 2012 12:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 6:39 AM
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
Post #1397980
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse