how to convert the data type

Question

Post reply

how to convert the data type

Charmer

SSChampion

Points: 14887
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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

http://qa.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: 4023 More actions · Answer 2

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