• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Creating another column however need it as a filter

Hi

Instead of hard coding I'm adding the username from the parameter and its creating another column.

before this query worked if I set the @p_user='prodaa3'

I'm getting this error

"Invalid column name'prodaa3'. [Database Vendor Code 207]"

declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20)

set @p_user ={?@p_user}
SET @p_business_segment_id ={?@p_business_segment_id}
set @p_year ={?@p_year}
set @p_business_segment_id = dbo.ufnParamOrNull(@p_business_segment_id)



select ba_id, bu_id, bg_id, lob_id, pc_id
into #userbgs
from user_bgs
where award_year = @p_year and cycle = 12 and [user_name] = @p_user
 and ba_id = (Case When @p_business_segment_id is null Then ba_id Else  @p_business_segment_id  End)
      

SELECT  B_AREA_DESC , B_AREA, B_UNIT_DESC, [EXEC] Executive, RL Reporting_line, MICRO as Micro,MNGR_DESC, eiv.EMPLOYEE_ID, eiv.LAST_NAME, eiv.GIVEN_NAMES,
[dbo].[fn_getEmployeeIncentivePlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as CASH_PLAN,      
[dbo].[fn_getEmployeeDeferralPlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as DEF_PLAN,
EXPAT_COUNTRY, EXPAT_TYPE, PS_START_DT,      
r.NAME AS REGULATOR, rr.NAME AS REGULATOR_RULE,  cs.CUSTOM_DEFERRAL_PCT, rr.DEFERRAL_PCT, cs.EFFECTIVE_DT, cs.COMMENTS as REGULATOR_COMMENTS
                     
FROM         dbo.CODE_STAFF AS cs INNER JOIN
                      dbo.REGULATOR AS r ON cs.REGULATOR_ID = r.REGULATOR_ID INNER JOIN
                      dbo.REGULATORY_RULE AS rr ON cs.REGULATORY_RULE_ID = rr.REGULATORY_RULE_ID inner join
            EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID=cs.EMPLOYEE_PROFILE_ID and eiv.AWARD_YEAR=@p_year and eiv.CYCLE = 12
            inner join #userbgs ubgs on ubgs.pc_id = eiv.PRODUCT_ID
left join
            ( select rh.EMPLOYEE_PROFILE_ID, c.DESCRIPTION as EXPAT_COUNTRY, et.NAME as EXPAT_TYPE, PS_START_DT
                  from  (select EMPLOYEE_PROFILE_ID,  max(PS_START_DT) PS_START_DT1 from
                                          RESIDENCE_HISTORY rh inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID      
                                    --where et.NAME not in ('Short-Term','Home')-- and PS_START_DT<'2011-04-01'
                                    group by EMPLOYEE_PROFILE_ID
                        )rhm
                  inner join RESIDENCE_HISTORY rh on rhm.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID and PS_START_DT=PS_START_DT1
                  inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID      
                  inner join dbo.COUNTRY c on c.COUNTRY_ID = rh.COUNTRY_ID
                  
            )rh on eiv.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID

DROP TABLE #userbgs
0
FAH_
Asked:
FAH_
  • 3
  • 2
1 Solution
 
FAH_Author Commented:
I have looked into the issue and i have to add ' in the parameter values.so in the paramter i have to enter 'prodaa3'.

Is there a way to combine /concatenate a string and a parameter value?

SET @p_user = " ' " + {?@p_user} + " ' "
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>set @p_user ={?@p_user}
What's the purpose of the {? } ?  

I'm not familiar with this, and normally @p_user would be passed into the Stored Proc as a parameter.  Also, T-SQL does not have any abilities to prompt the user for a value such as SET @p_user = 'Enter a user id here'

>Is there a way to combine /concatenate a string and a parameter value?
Yes. Like...

SET @some_string = @some_string + @some_other_string
or
SET @some_string = @some_string + ''' + @some_other_string + '''
0
 
FAH_Author Commented:
set@p_user ={?@p_user} is coming from crystal reports

?@p_user = paramter

if enter in the parameter  prodaa3 it looks for a column and throws an error however if I enter 'prodaa3' in the parameter it works

So I was wondering if there is a way to add ' to the beginning of the parameter value and the end as well so the user should only enter

prodaa3 and then the system does the concatenate and puts this result into @p_user

so @p_user would be equal to 'prodaa3'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>So I was wondering if there is a way to add ' to the beginning of the parameter value and the end as well
Yes, it would be four single-quote characters ( I had three in the previous example .. my bad)
SET @some_string = @some_string + '''' + @some_other_string + ''''

SELECT 'WHERE fruit_type = ' + '''' + 'banana' + ''''

Open in new window

0
 
FAH_Author Commented:
I'm getting this error

Msg 207, Level 16, State 1, Line 9
Invalid column name 'prodaa3'.

declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20),
@p_user_temp varchar(20)


set @p_user_temp =prodaa3
SET @p_user = ''''+@p_user_temp + ''''


SET @p_business_segment_id = '2'
set @p_year = 2011

Select @p_user
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now