SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 IF statement help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yellowman
Starting Member

USA
25 Posts

Posted - 12/13/2012 :  13:16:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/13/2012 :  13:40:26  Show Profile  Reply with Quote
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.

Go to Top of Page

yellowman
Starting Member

USA
25 Posts

Posted - 12/13/2012 :  13:51:53  Show Profile  Reply with Quote
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%' 
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/13/2012 :  14:18:45  Show Profile  Reply with Quote
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;     
Go to Top of Page

yellowman
Starting Member

USA
25 Posts

Posted - 12/13/2012 :  15:12:02  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/14/2012 :  02:22:51  Show Profile  Reply with Quote
are you trying to change db context from within dynamic query using USE command?

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000