December 17, 2012 at 2:26 pm
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.
December 17, 2012 at 2:43 pm
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/
December 17, 2012 at 3:31 pm
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
December 17, 2012 at 3:39 pm
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/
December 17, 2012 at 3:45 pm
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.
-- Itzik Ben-Gan 2001
December 17, 2012 at 3:50 pm
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.
December 18, 2012 at 7:25 am
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/
December 18, 2012 at 11:38 am
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....
December 18, 2012 at 12:10 pm
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/
December 18, 2012 at 12:20 pm
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
December 18, 2012 at 12:21 pm
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
December 18, 2012 at 12:28 pm
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
December 18, 2012 at 1:53 pm
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. 😀
December 19, 2012 at 6:08 am
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