IF statement help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF statement help

Author

Topic

yellowman
Starting Member

25 Posts

Posted - 2012-12-13 : 13:16:45

I just added an IF statement to a piece of code that I have and something isn't quite right. It doesn't return any results


-- Get schema version (this returns numbers like 4.1, 4.2, 4.3, etc.)

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

--Setup SELECT statement to get the version number

IF @Version LIKE '4.%'AND @Version NOT LIKE '4.3%'
BEGIN

     SELECT @PeerReview4x = some stuff                 
     EXEC sp_executesql @PeerReview4x

END

Now if I pull This out of the IF clause and run it...

SELECT @PeerReview4x = some stuff
EXEC sp_executesql @PeerReview4x

It returns a ton of results. Is that first statement in my IF statement correct or am I missing something? Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 13:40:26

quote:
Originally posted by yellowman

I just added an IF statement to a piece of code that I have and something isn't quite right. It doesn't return any results

-- Get schema version (this returns numbers like 4.1, 4.2, 4.3, etc.)

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

Exec (@Version)

--Setup SELECT statement to get the version number

IF @Version LIKE '4.%'AND @Version NOT LIKE '4.3%'
BEGIN

SELECT @PeerReview4x = some stuff
EXEC sp_executesql @PeerReview4x

END

Now if I pull This out of the IF clause and run it...

SELECT @PeerReview4x = some stuff
EXEC sp_executesql @PeerReview4x

It returns a ton of results. Is that first statement in my IF statement correct or am I missing something? Thanks.

yellowman
Starting Member

25 Posts

Posted - 2012-12-13 : 13:51:53

If I add Exec (@Version) to the script it outputs the version numbers to the screen (stuff like 4.1, 4.1.1, etc.). I don't need to output the values, I just need the values to be referenced in my IF statement. The condition in my IF statement them determines if EXEC sp_executesql @PeerReview4x actually gets executed of not.

I got creative and tried something like this, but it also failed.

 
IF EXEC(@Version) LIKE '4.%' AND EXEC(@Version) NOT LIKE '4.3%'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-13 : 14:18:45

If I understood you correctly, you are trying to get the result of a dynamic query into a scalar variable. You have to use sp_executesql to do that - something like shown below. I don't have the data to test, so please do a "select @version" after this code and examine the result to see if you are getting the correct version you are expecting

DECLARE @sql NVARCHAR(4000) = 'USE ' +@DB_Name+ '
                   SELECT @VER = version_number
                   FROM '+ ''+@DB_Name+ '' +'.CQ_DBO.schema_version';
DECLARE @version NVARCHAR(32) = N''; 
DECLARE @paramlist NVARCHAR(32) = N'@VER NVARCHAR(32) OUT';
EXEC sp_executesql @stmt = @sql, @params = @paramlist,   @VER = @version OUT;

yellowman
Starting Member

25 Posts

Posted - 2012-12-13 : 15:12:02

So I changed it a bit to look something like this...


-- Get schema version
SELECT @Version = 'USE ' +@DB_Name+ '
                   SELECT version_number
                   FROM '+ ''+@DB_Name+ '' +'.CQ_DBO.schema_version'
                   
SET @ParamDefinition = N'@versionOut nvarchar(200) OUTPUT'                  
EXEC sp_executesql @Version, @ParamDefinition, @versionOut=@Ver OUTPUT
                                  
--Setup SELECT statement to get the version number
IF @Ver LIKE '4.%' AND @Ver NOT LIKE '4.3%'
BEGIN
SELECT @PeerReview4x = some query...

EXEC sp_executesql @PeerReview4x
END

The only output I get is my scheme numbers from @Version. It looks like the EXEC sp_executesql @PeerReview4x never gets executed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 02:22:51

are you trying to change db context from within dynamic query using USE command?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources