empty results? - 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

empty results?

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-19 : 00:59:04

Okay, so i have a procedure where i'm trying to build a left join, and for some
reason, if i store it into a procedure, it doesnt work, if i run it in query analyzer
i have no problem.

Manual Code:


create     PROCEDURE search_orders_11              

		 @SPSir		varchar(3)	= 	null,
                 @debug     bit          = 0 AS                    

DECLARE @sql        nvarchar(4000),                         
        @paramlist  nvarchar(4000)                          
  
if @spsir='1'
   select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

select @sql=@sql+' WHERE  1 = 1'

SELECT @sql = @sql  + ' group by o.state_abbrv ORDER BY o.state_abbrv '

here is the procedure:


create     PROCEDURE search_orders_11              

		 @SPSir		varchar(3)	= 	null,
                 @debug     bit          = 0 AS                    

DECLARE @sql        nvarchar(4000),                         
        @paramlist  nvarchar(4000)                          
  
if @spsir='1'
   select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

select @sql=@sql+' WHERE  1 = 1'

SELECT @sql = @sql  + ' group by o.state_abbrv ORDER BY o.state_abbrv '

here is the execution:


exec search_orders_11 @spsir=1

i get the whole command completed sucessfully, but no result. am i missing something here...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-19 : 01:05:02

(1) I dont see why you need dynamic SQL here
(2) You are building the SQL but you are not EXECuting it anywhere..

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:06:09

You have only built the dynamic SQL. You have to execute it too.
Put an EXEC (@SQL) last in the stored procedure.

But why are you using dynamic SQL? There is absolutely no need for that!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:06:34

Peter Larsson
Helsingborg, Sweden

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-19 : 01:12:05

i'm sorry, i just trimmed a bunch of crapola that have no baring on this problem. would it be easier if i posted the ENTIRE procedure? it's about 200 lines :o( i just dummied down the procedure to the problem area.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:34:26

The problem is that what happens when @spsir is not equal to '1'.
Also put EXEC (@SQL) last.

Peter Larsson
Helsingborg, Sweden

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-19 : 01:43:44

perhaps i should just post the entire procedure:


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER      PROCEDURE search_orders_1
                 
                 @lowage	int	=	null,
                 @highage	int	=	null,
                 @highwealth	int	=	null,
                 @lowwealth	int	=	null,
                 @highpurprice	int	=	null,
                 @lowpurprice	int	=	null,
                 @lowmtgamt	int	=	null,
                 @highmtgamt	int	=	null,
                 @lowincome	varchar(3)	=	null,
                 @highincome	varchar(3)	=	null,
                 @marriedstat	varchar(3)	=	null, 
                 @lowyearbuild	varchar(4)	=	null,
                 @highyearbuild	varchar(4)	=	null,
                 @mtgpresstat	varchar(3)	=	null,
                 @lenderpresstat	varchar(3)	=	null,
                 @ratets	varchar(3)	=	null,
                 @loants	varchar(10)	=	null,
                 @childstat	varchar(3)	=	null,
                 @homeownerstat	varchar(10)	=	null,
                 @lowpurdate	varchar(8)	=	null,
                 @highpurdate	varchar(8)	=	null,
		 @addresstype   varchar(3)	= 	null,
		 @SPSir		varchar(3)	= 	null,
		 @debttype	varchar(3)	= 	null,
		 @dwelltype	varchar(3)	= 	null,
                 @zipselect	nvarchar(4000)	=	null,
                 @acselect	nvarchar(4000)	=	null,
                 @stselect	nvarchar(4000)	=	null,
                 @cityselect	nvarchar(4000)	=	null,
                 @counselect	nvarchar(4000)	=	null,-- as
                 @debug     bit          = 0 AS                    

DECLARE @sql        nvarchar(4000),                         
        @paramlist  nvarchar(4000)                          
  
if @spsir='1'
   select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

if @spsir='2'
   select @sql = @sql+ 'SELECT distinct o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o inner join dbo.Spanish s on o.per1_lname = s.lname'

if @spsir='ALL' or @spsir is null
   SELECT @sql = 'SELECT o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o'

select @sql=@sql+' WHERE  1 = 1'



--if @spsir='1'
--   select @sql = @sql+ ' and dbo.spanish.lname is null'
                                                            
IF @lowage<>0 and @lowage is not null
   SELECT @sql = @sql + ' AND o.per1_age >= @xlowage'          
                                                            
IF @highage<>0 and @highage IS NOT null
   SELECT @sql = @sql + ' AND o.per1_age <= @xhighage'        
                                                            
IF @lowincome<>'DF' and @lowincome IS NOT NULL                                   
   SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome'    
                                                            
IF @highincome<>'DF' and @highincome IS NOT NULL                                  
   SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome'  
                                                                                                                          
IF @highwealth<>0 and @highwealth IS NOT NULL                                      
   SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth'      
                                                                
IF @lowwealth<>0 and @lowwealth IS NOT NULL                                       
   SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth' 
                                                         
IF @highpurprice<>0 and @highpurprice IS NOT NULL                             
   SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice'                     
                                                              
IF @lowpurprice<>0 and @lowpurprice IS NOT NULL                                   
   SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice'  
                                                              
IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL                                  
   SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild'        
                                                                   
IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL                                      
   SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild'
                                                                   
IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL                                          
   SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'

IF @highmtgamt<>0 and @highmtgamt IS NOT NULL                                         
   SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt'         
   
IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL
   SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate'      
   
IF @highpurdate<>'DF' and @highpurdate IS NOT NULL
   SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate'       

IF @marriedstat in ('M','S')
   SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat'    

IF @ratets in ('V','F')
   SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets'   

IF @homeownerstat in ('H','R')
   SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'

IF @childstat in ('Y','N')
   SELECT @sql = @sql + ' AND o.child_pres = @xchildstat'  

IF @lenderpresstat ='Y'
   SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>'' '''

IF @lenderpresstat ='N'
   SELECT @sql = @sql + ' AND o.oo_mtg_lender_name='' '''
  
IF @mtgpresstat='Y'
   SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'

IF @mtgpresstat='N'
   SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'

IF @loants<>'DF' and @loants IS NOT NULL                                           
   SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'

if @dwelltype='1'
   SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''

if @dwelltype='2'
   SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''

if @DebtType='1'
   select @sql = @sql + ' and o.pres_cc=''y'''

if @DebtType='2'
   select @sql = @sql + ' and o.pres_perm_cc=''y'''

if @addresstype='3'
   select @sql = @sql + ' and o.address1<>'' '''





if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
   SELECT @sql = @sql + ' and (1=2'


IF @acselect<>'DF' and @acselect IS NOT NULL
   SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )'
   
IF @stselect<>'DF' and @stselect IS NOT NULL
   SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )'
   
IF @cityselect<>'DF' and @cityselect IS NOT NULL
   SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )'
   
IF @counselect<>'DF' and @counselect IS NOT NULL
   SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'

IF @zipselect<>'DF' and @zipselect IS NOT NULL
   SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'


if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
   SELECT @sql = @sql + ')'

SELECT @sql = @sql  + ' group by o.state_abbrv ORDER BY o.state_abbrv '   

IF @debug = 1
   PRINT @sql   
                     
SELECT @paramlist = '@xlowage	int	,
			@xhighage	int	,
			@xlowincome	nvarchar(4000)	,
			@xhighincome	nvarchar(4000)	,
			@xmarriedstat	nvarchar(4000)	,
			@xhighwealth	int	,
			@xlowwealth	int	,
			@xhighpurprice	int	,
			@xlowpurprice	int	,
			@xlowyearbuild	nvarchar(4000)	,
			@xhighyearbuild	nvarchar(4000)	,
			@xlowmtgamt	int	,
			@xhighmtgamt	int	,
			@xmtgpresstat	nvarchar(4000)	,
			@xlenderpresstat	nvarchar(4000)	,
			@xratets	nvarchar(4000)	,
			@xloants	nvarchar(4000)	,
			@xchildstat	nvarchar(4000)	,
			@xhomeownerstat	nvarchar(4000)	,
			@xlowpurdate	nvarchar(4000)	,
			@xhighpurdate	nvarchar(4000)	,
			@xzipselect	nvarchar(4000)	,
			@xacselect	nvarchar(4000)	,
			@xstselect	nvarchar(4000)	,
			@xcityselect	nvarchar(4000)	,
			@xcounselect	nvarchar(4000)'
                                                                   
EXEC sp_executesql @sql, @paramlist,                               
                   @lowage	,
                   @highage	,
                   @lowincome	,
                   @highincome	,
                   @marriedstat	,
                   @highwealth	,
                   @lowwealth	,
                   @highpurprice	,
                   @lowpurprice	,
                   @lowyearbuild	,
                   @highyearbuild	,
                   @lowmtgamt	,
                   @highmtgamt	,
                   @mtgpresstat	,
                   @lenderpresstat	,
                   @ratets	,
                   @loants	,
                   @childstat	,
                   @homeownerstat	,
                   @lowpurdate	,
                   @highpurdate	,
                   @zipselect	,
                   @acselect	,
                   @stselect	,
                   @cityselect	,
                   @counselect




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:45:48

You are concatenating a NULL string.
Right after declaring @sql, add a line set @sql = ''

Also read this http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-19 : 01:56:52

NIIIIICE! that did it, Thanks Peso so much! i knew it was something dumb!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 02:05:16

What's the lesson learned here?
Always post FULL and COMPLETE problem description with all code you recently have.

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-04-19 : 02:44:29

that's peso. a genuine here

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-19 : 12:03:46

quote:
Originally posted by Peso

What's the lesson learned here?
Always post FULL and COMPLETE problem description with all code you recently have.

Peter Larsson
Helsingborg, Sweden

Besides the fact that when you are concatenating strings becareful to check for NULL's?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources