different datatypes in two tables

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

different datatypes in two tables

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-12-01 : 09:08:40

Hi,

I am trying to create a storedproc for the following,
my issue is that the jobid field in d_jobattributes is
varchar(12), whilst the gbmcu field in F0902 is char(12)?

I presume within my storedproc I can only have one variable with
either a char(12) or varchar(12) ?

create procedure jobinfo @JobID varchar(12) as

select * from d_jobattributes where jobid = @jobid

select * from openquery(symprod_etldev,'select * from proddta.f0902 where
gbmcu = ''@jobid'' ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 09:25:49

why not make @JobID as varchar itself and do like below


create procedure jobinfo @JobID varchar(12) as

select * from d_jobattributes where jobid = @jobid

select * from openquery(symprod_etldev,'select * from proddta.f0902') where 
gbmcu LIKE @jobid + '%'

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

tariq2
Posting Yak Master

125 Posts

Posted - 2011-12-01 : 09:48:42

Thanks for this Visakh :)

As a test
I have just tried to run the following
select * from openquery(symprod_etldev,'select * from proddta.f0902') where
gbmcu like '1105742201' + '%'

and it has processed for over 3 minutes, without yielding a result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 09:51:46

is proddta.f0902 a large table?

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

tariq2
Posting Yak Master

125 Posts

Posted - 2011-12-01 : 10:20:45

Yes, huge, a few houndred thousand rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:25:18

why are you using openquery? is it in different server?

anyways try this too


....
 DECLARE @TSQL varchar(8000)
  SELECT  @TSQL = 'select * from openquery(symprod_etldev,''select * from proddta.f0902 WHERE gbmcu LIKE  ''''' + @jobid + '%'''''')'
      EXEC (@TSQL)

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

tariq2
Posting Yak Master

125 Posts

Posted - 2011-12-01 : 10:53:40

Yes , I am using openquery as this is within a different server..
I tried the @TSQL query but without success , I substituted the variable with a hard-coded JobID (see below)
1) works but 2) does not

1)
DECLARE @TSQL varchar(8000)
SELECT @TSQL = 'select * from openquery(symprod_etldev,''select * from proddta.f0902 WHERE gbmcu LIKE ''''' + '1105742201' + '%'''''')'
EXEC (@TSQL)

2)
select * from openquery (symprod_etldev,'select * from proddta.f0902
where gbmcu = '' 1105742201'' ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:54:58

2 wont work as you're using =. since its char field it will have spaces after numeric value which is why i used LIKE and %

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

tariq2
Posting Yak Master

125 Posts

Posted - 2011-12-01 : 11:46:04

Sorry Visakh, what I meant was 1) does not work (provides no rows in the resultset) and 2) does work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 11:55:33

this is working for me


DECLARE @TSQL varchar(8000)
SELECT @TSQL = 'select * from openquery(symprod_etldev,''select * from proddta.f0902 WHERE gbmcu LIKE ''''' + '1105742201' + '%'''''')'
EXEC (@TSQL)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources