how to convert the data type

• Charmer

  SSChampion

  Points: 14903

  More actions

  September 28, 2011 at 7:19 am

  #248788

  Hi frndz,

  i have a stored procedure with a select query....i left outer join a table and when i match two column, one column is integer and another column is varchar...i get result by casting it only when i don't declare the select query as stored procedure... but when it is under stored procedure and, i am not able to get the result as due to getting the table name and column name dynamically through passing the parameters.... how can i fix it? here is my query....

  SET @MySql = 'SELECT Distinct JurisMap.PnxCodeValue as PnxJurisID , ' + Cast(@CodeID as Varchar(4)) + ' as CodedID, ' + @TableName+ '.' + @ColumnName + ' as SrcCodeValueRef, ValidationSetEntry.EntryValue as SrcCodeValue, ValidationSetEntry.Description as SrcCodeDescr ' +

  ' From dbo.' + @TableName + ' left outer join SrcCodeMap on ' +convert(varchar,@TableName + '.' + @ColumnName) + ' = SrcCodeMap.SrcCodeDescr and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(4)) +

  ' Left outer join SrcCodeMap JurisMap on JurisMap.SrcCodeDescr = ' + @TableName +'.ORI and JurisMap.CodeID = 100 ' +

  ' left outer join ValidationSetEntry on ValidationSetEntry.EntryID = '+ @TableName+ '.' + @ColumnName + ' and ValidationSetEntry.SetID = ' + Cast( @SetID as Varchar(8)) +

  ' WHERE SrcCodeMap.JurisID is Null AND ' + @TableName+ '.' + @ColumnName + ' Is Not Null '

  Thanks,
  Charmer

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 28, 2011 at 7:47 am

  #1387777

  Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ronald H

  SSCarpal Tunnel

  Points: 4053

  More actions

  September 28, 2011 at 8:21 am

  #1387800

  Indeed, please post table definition, sample data and desired output next time. However, I think I might have found something what you're looking for. I also changed some of the query, to make it better readable. The biggest problem was that some quotes had to be moved a bit.

  SET @MySql = '

  SELECT Distinct JurisMap.PnxCodeValue as PnxJurisID , ' + Cast(@CodeID as Varchar(4)) + ' as CodedID,

  x.' + @ColumnName + ' as SrcCodeValueRef, ValidationSetEntry.EntryValue as SrcCodeValue,

  ValidationSetEntry.Description as SrcCodeDescr

  From dbo.' + @TableName + 'x

  left outer join SrcCodeMap on convert(varchar,x.' + @ColumnName + ') = SrcCodeMap.SrcCodeDescr

  and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(4)) + '

  Left outer join SrcCodeMap JurisMap on JurisMap.SrcCodeDescr = x.ORI and JurisMap.CodeID = 100 ' + '

  left outer join ValidationSetEntry on ValidationSetEntry.EntryID = x.' + @ColumnName + '

  and ValidationSetEntry.SetID = ' + Cast( @SetID as Varchar(8)) + '

  WHERE SrcCodeMap.JurisID is Null AND x.' + @ColumnName + ' Is Not Null '

  Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2