March 30, 2010 at 5:19 am
Hi
I have a procedure which contains the code below. I have created a report in reporting services which runs ok when you select 'ALL' companies or one company, however when I select more than one company I get an error message ‘Must declare the scalar variable @company_Name_Code’
Could you please advise 🙂
Thanks
SELECT
Data_Type_Code
, Department_Group_Code
, Company_Name_Code
, Master
, Insured
, Number
, Claim_Title
, Date_of_Loss
, Currency_Type
FROM vw_Table_Summary
WHERE Period_End=@Period_End
AND Data_Type_Code=@Data_Type_Code
AND Branch_Name_Code=@Branch_Name_Code
AND (@Department_Group_Code = '*ALL' OR Department_Group_Code = @Department_Group_Code)
AND (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (@Company_Name_Code))
AND Currency_Type = @Currency_Type
March 30, 2010 at 5:45 am
Comment withdrawn. My guess would be that you are trying to use the same parameter as both a scalar and a table variable at the same time. What would the sample contents of the variable be if you selected more than one state? Would it be a a varchar string, such as "1,2,3" or would it be a set of rows with discrete values like this?
company_code_value
1
2
3
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2010 at 5:49 am
Here is the code as requested
Thanks
Declare @Period_End smalldatetime
, @Branch_Name_Code varchar (10)
, @Department_Group_Code varchar(10)
, @Data_Type_Code varchar(3)
, @Currency_Type varchar(4)
, @Company_Name_Code varchar (4)
SELECT
@Period_End AS Period_End
, Data_Type_Code
, Department_Group_Code
, Company_Name_Code
, Master
, Insured
, Number
, Claim_Title
, Date_of_Loss
, Currency_Type
, '' as Accounting_Currency
, Gross_PD
, Net_INC
FROM vw_Table_Summary
WHERE Period_End=@Period_End
AND Data_Type_Code=@Data_Type_Code
AND Branch_Name_Code=@Branch_Name_Code
AND (@Department_Group_Code = '*ALL' OR Department_Group_Code = @Department_Group_Code)
AND (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (@Company_Name_Code))
AND Currency_Type = @Currency_Type
March 30, 2010 at 5:57 am
declare @Company_Name_Code varchar (4)
declare @test-2 table (company_name_code char(1))
insert into @test-2
select '1' union all
select '2' union all
select '3'
set @Company_Name_Code = '2' -- will work
-- set @Company_Name_Code = '1,2' -- will return no rows
select * from @test-2
where (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (@Company_Name_Code))
There is nothing wrong with the syntax you're using in your WHERE clause, but it isn't going to work when you put multiple values in @company_name_code. So I have to go back to the question of: What is the value of @company_name_code when you are trying to select more than one company?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2010 at 6:07 am
It will be a set of rows as shown below which is a string, so in practise when HCB and RBC are selected it should pass through the query as
AND (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (HCB,RBC))
company_code_value
HCB
TSB
RBC
March 30, 2010 at 6:17 am
A set of rows is NOT a string. It's a table-valued parameter. You seem to be confusing a passed parameter with building a string dynamically. Unless something is going on that you haven't shown me. You can't possibly produce
AND (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (HCB,RBC))
It is either going to come out:
-- if you are building the query dynamically and building a string from the rows
AND (HCB,RBC = '*ALL' OR Company_Name_Code IN (HCB,RBC))
or
-- if you are just trying to pass the values
AND (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (@Company_Name_Code ))
The IN code will ultimately fail on (HCB,RCB) because that designates two columns, not two values.
IN ("HCB","RBC") would designate a list of constant values.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2010 at 6:24 am
To make this work, you are either going to have to pass your list of rows as a table-valued parameter OR use the CHARINDEX or PATINDEX function to see if the company name is in the string you are building. If you pass it as a table-valued parameter, change your line to read
AND (EXISTS (Select 1 from @company_name_code where company_code_value = '*ALL')
OR (company_code_value IN (SELECT company_code_value from @Company_name_code)))
THEN, it will work. Play around with the sample code below. Note that @company_name_code is a table variable, not a scalar variable.
declare @Company_Name_Code table (company_code_value varchar (4))
declare @test-2 table (company_code_value char(1))
insert into @test-2
select '1' union all
select '2' union all
select '3'
insert into @Company_Name_Code
-- select '*ALL'
select '1'
union all select '2'
select * from @test-2
WHERE (EXISTS (Select 1 from @company_name_code where company_code_value = '*ALL')
OR (company_code_value IN (SELECT company_code_value from @Company_name_code)))
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2010 at 6:41 am
I am not using dynamic sql I'm using static sql and unfortunately I quite baffled as to how to approach this in my code.
Thanks for your help, I will play around with your example
April 1, 2010 at 7:39 am
I hope you found your way to a satisfactory solution.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 1, 2010 at 8:47 am
Some more ideas:
How to share data between stored procedures
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 1, 2010 at 8:54 am
Thanks guys!
April 1, 2010 at 9:02 am
Oooooo.... Erland Sommarskog,
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2010 at 5:49 am
1. Remove the 'all' part from your stored proc. Ssrs does not pass a value of ALL on a multiselect. It will send a single dimension array of ALl possible values.
2. Read this. It explains EXACTLY what you want to do and how to do it:
http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/
April 2, 2010 at 6:10 am
David-Leibowitz (4/2/2010)
Read this. It explains EXACTLY what you want to do and how to do it: http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/
Hmmm. Interesting enough for its SSRS focus, but it only covers one possible method - and the least-well-performing method at that! Mutli-statement TVFs (and WHILE-loop string splitting) are not at all close to being optimal.
Please review the techniques shown in Erland Sommarskog's article that I referenced earlier for additional techniques, and a thorough performance comparison.
If you do ever need to split delimited strings, please use a tally table (for very small strings) or a streaming CLR TVF (in all cases). See http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html for the reasons why.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 7:13 am
Optimal is relative to what you're trying to accomplish. And there are always 20 ways to skin a cat.
I find it's always a balance between user needs and dev support goals.
The method works without any over engineering.
And the overhead on the type of data you pass into it...Let's be honest, if you're passing in a string of 20k items..you have report/query design issues you need to figure out separately.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply