Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Stored Procedure "IN" variable quotes problem

Hey folks...making me crazy here.

I have a stored procedure that I've had to expand and include
blah...blah...blah..
Where tab1.status  in (@afsDealStatus)

From vb I am passing in a string as follows
@afsDealStatus = 'Hold','Closed'

How do I modify that in SQL so that the following works?
exec sp_cfa_AfsGet @afsDealStatus

I'm getting nothing

Avatar of mattibutt
mattibutt
Flag of United States of America image

hi
you need to provide parameters as well when you are executing

DECLARE      @return_value int
EXEC      @return_value = [dbo].[sp_cfa_AfsGet]
SELECT      'Return Value' = @return_value

try the above
Avatar of s_chilkury
Avatar of _bmendoza
_bmendoza

declare @sql varchar(4000)
declare @afsDealStatus varchar(4000)
set @afsDealStatus = '''asdf'',''dfgd'''
set @sql='select blah1, blah2 from blah where status in ('+@afsDealStatus+')'
print @sql

Open in new window


Avatar of Larry Brister

ASKER

mattibutt:
Huh?  If I comment out the "IN" part and do exec sp_cfa_AfsGet
This is returned

212      LT-8009-20120122      Funded      0      F
25      LT-8010-20111207      Hold      78      M
26      LT-8010-20121207      Court Order      78      M


If I Hard Code the IN portion
Where tab1.status  in ('Hold','Funded')

I get back
212      LT-8009-20120122      Funded      0      F
25      LT-8010-20111207      Hold      78      M

So I need to know how to pass in the 'Hol','Funded' in the variable so it works.
correction.
declare @sql varchar(4000)
declare @afsDealStatus varchar(4000)
set @afsDealStatus = '''asdf'',''dfgd'''
set @sql='select blah1, blah2 from blah where status in ('+@afsDealStatus+')'
--print @sql
exec @sql

Open in new window

That will not work directly with a variable.  To SQL the variable is *one* value *only*, *not* a list of values.

The best thing to do is to split the parameter into a table (either a table variable or a temp table, typically), and then INNER JOIN to that table to limit the query to only matches.  [You could also use IN (SELECT value FROM #table), but that will usually be less efficient than a JOIN.]
That is, this:

SET @afsDealStatus = 'Hold','Closed'

Where tab1.status  in (@afsDealStatus)

Is functionally equivalent to this:

Where tabl1.status = ''''Hold'',''Closed'''

as SQL interprets it.
hi
can you post your stored procedure as well your expected result
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi,

in vb raplace all 1 qoute with 2 qoutes like below

param = replace(param,"'","''")

Open in new window


then in your store procedure

create proc sp_cfa_AfsGet @afsDealStatus varchar(4000)
as begin
declare @sql varchar(4000)
set @sql='select * from  tab1 where status in ('+@afsDealStatus+')'
exec @sql
end

Open in new window

If you are passing the string as below to SP from your VB code.

declare @afsDealStatus nvarchar(100)
set @afsDealStatus = 'Hold,Closed'
exec sp_cfa_AfsGet @afsDealStatus

Then, change the WHERE clause inside your SP like this.

WHERE tab1.status IN (
SELECT ltrim(SUBSTRING(afsDealStatus, n, CHARINDEX(',', afsDealStatus + ',',n) - n)) AS afsDealStatus
 FROM (select @afsDealStatus afsDealStatus) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + afsDealStatus, n, 1) = ','
  AND n < LEN(afsDealStatus) + 1)

Hey guys,
  This stored procedure is HUGE and I really can't put it into dynamic SQL

I also have to allow for returning EVRYTHING when no variable is passed in

angelll, I used your parmstolist and created a #temp table to use

Blah...blah...blah...
Join #temp z on tab1.status = z.Status
Order By        tab1.afsSource,tab1.afsSourceDetail, tab1.afsid

That works fine...
If the following question requires me to submit abnoter question please let me know

How can I bypass the join statement completly if the LEN(Variable) = 0 so I get all records??
>> I also have to allow for returning EVRYTHING when no variable is passed in

Try the WHERE clause like this if you are trying my previous post.

WHERE tab1.status IN (
SELECT ltrim(SUBSTRING(afsDealStatus, n, CHARINDEX(',', afsDealStatus + ',',n) - n)) AS afsDealStatus
 FROM (select @afsDealStatus afsDealStatus) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + afsDealStatus, n, 1) = ','
  AND n < LEN(afsDealStatus) + 1)
  or @afsDealStatus is null
...
LEFT OUTER JOIN #temp z on tab1.status
...
WHERE (len(@afsDealStatus) = 0 OR z.status IS NOT NULL)
AND (...existing_conditions...)
My previous comment:

RE: How can I bypass the join statement completly if the LEN(Variable) = 0 so I get all records??
ScottPletcher:
That gives me everything no matter what.

I need to bypass the join itself completly if len = 0
Did you try my post?
Sharath...how would I apply that in my SP below?

With angelll...I just want to return all records from tab1 when
If @afsDealStatus = ''
Set @afsDealStatus = Null

See the 3rd line from the bottom


blah...blah...blah...
Join #temp z on tab1.status = z.status
        Order By        tab1.afsSource,tab1.afsSourceDetail, tab1.afsid
End
-- =========================================================================
-- Author:		lrbrister
-- Create date: 11.09.2010
-- Description:	Get available for sale items
--				exec sp_cfa_AfsGet '1900', '2099', '0', '1000000','', 'Hold, Court Order'
-- =========================================================================
ALTER PROCEDURE [dbo].[sp_cfa_AfsGet] (
				@startYear int, @endYear int , 
				@minAmount money , @maxAmount money , 
				@dealStatus NVarchar(20) = Null,
				@afsDealStatus NVarchar(1000))
AS

If @dealStatus = ''
Set @dealStatus = Null
If @afsDealStatus = ''
Set @afsDealStatus = Null
Select [value] as [status] into #temp from ParmsToListComma(@afsDealStatus,',')
Begin  
		SELECT tab1.afsid, 
		tab1.afsSource,
  		tab1.status,
		tab1.afsDate,
		tab1.afsAmount,
		tab1.afsSourceDetail,
		tab1.afsStatus,
		tab1.afsTransaction,
		tab1.pctHeld,
		ISNull(tabLT.DaysToFunding,tabSS.DaysToFunding) DaysToFunding,
		IsNull(tabLT.UsedDate,tabSS.UsedDate) UsedDate

   FROM
(
		Select  a.afsid,  
                a.afsSource,  
                case when Left(a.afsSource,2) = 'LT' then   
                                lt.status else   
                                ss.status end as [status],  
                a.afsDate,  
                a.afsAmount,                      
                a.afsSourceDetail,  
                a.afsStatus,  
                IsNull(a.afsTransaction,0) as afsTransaction,   
                z.percentHeld as pctHeld,
				SubString(a.afsSource,4,4) as DealID 
        From    proc_cfa.dbo.P_AvailableForSale a  
                Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID 
                Left Join crmprod_01.dbo.P_Deals ss ON SubString(a.afsSource,4,4) = ss.DealID  
                Left Join proc_cfa.dbo.P_AvailableForSale x on a.afsSource = x.afsSource  
                Left Join (select left(afssource,7) src,  
                                 (1 -sum(case when (afsstatus = 1) then afsamount else 0 end)/sum(afsamount)) *100 as percentHeld  
                            from proc_cfa.dbo.P_AvailableForSale    
							group by left(afssource, 7)  
							)z on Left(a.afsSource,7) = z.src  
       Where   a.afsTransaction = 0  
                        and  
                        (  
                                (a.afsAMount > @minAmount - 1 and a.afsAMount < @maxAmount + 1)  
                                        and  
                                (YEAR(a.afsDate) > @startYear - 1 and YEAR(a.afsDate) < @endYear + 1)  
                                        and  
                                case when Left(a.afsSource,2) = 'LT' then   
                                        lt.status else   
                                        ss.status end =   
                                                IsNull(@dealStatus,case when Left(a.afsSource,2) = 'LT' then   
                                                                        lt.status else   
                                                                        ss.status end)  
                        )  
        group by        a.afsid, a.afsSource, a.afsSourceDetail, a.afsDate, a.afsAMount,   
                                a.afsTransaction, a.afsStatus,ss.status, lt.status, z.percentHeld  ) tab1
		LEFT JOIN 
				(
				SELECT  p.DealID,
						e.Funded,e.ManProjFunding,e.AutoProjFunding,   
						Coalesce(e.Funded,e.ManProjFunding,e.AutoProjFunding) as CoalesceDate,   
						case when e.funded is not null then 0 else
						DATEDIFF (d ,getdate(),Case when e.ManProjFunding is null then e.AutoProjFunding else e.ManProjFunding end ) end DaysToFunding,
						case when e.funded is not null then 'F' when e.ManProjFunding is not null then 'M' when e.AutoProjFunding is not null then 'A' else '-' end UsedDate   
				FROM    PROC_LT.dbo.P_Deals p  
						Left Join PROC_LT.dbo.v_Events e ON p.DealID = e.dealID
				 ) tabLT ON tab1.DealId = tabLT.DealId

                         
		LEFT JOIN 
				(
				SELECT  p.DealID,
						e.Funded,e.ManProjFunding,e.AutoProjFunding,   
						Coalesce(e.Funded,e.ManProjFunding,e.AutoProjFunding) as CoalesceDate,   
						case when e.funded is not null then 0 else
						DATEDIFF (d ,getdate(),Case when e.ManProjFunding is null then e.AutoProjFunding else e.ManProjFunding end ) end DaysToFunding, 
						case when e.funded is not null then 'F' when e.ManProjFunding is not null then 'M' when e.AutoProjFunding is not null then 'A' else '-' end UsedDate 
				FROM    crmprod_01.dbo.P_Deals p  
						Left Join crmprod_01.dbo.v_Events e ON p.DealID = e.dealID
				 ) tabSS ON tab1.DealId = tabSS.DealId

		Join #temp z on tab1.status = z.status
        Order By        tab1.afsSource,tab1.afsSourceDetail, tab1.afsid 
End

Open in new window

But if #temp is not empty...do the join
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sharath_123:
That's the darnd'st thing I ever saw.  It works.

Somehow I wish I could use that parmstolist function of angelll's to shorten the (already) long code on this page.

But your solution definately works...barring any arguments, I'll post your points shortly.
Sharath_123:
Both worked great.  Thanks.

I'd like to give a small nod to angelll if you have no objection.
Sure thing. Not an issue. I have just fixed your query but the function was provided by Angel!!! only :)
Ok folks...with no objections I would like a 400/100 split with Sharath_123: and angelll

You guys are true professionals.
ABsolutely fantastic!

Remind me to buy you a sody-pop sometimes.