Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

sql server concatenate fields

everything upto and including the as fieldname creates a working statement now ive added as PK its failing as I dont quite yet see how to get the syntax right.

the error is Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value '219584B201607Horse_power' to data type int.

its building the key but not combining the last 2 fields of it so I guess it needs a cstr as I know it.

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClientDifferencesLogic]    Script Date: 06/02/2017 15:59:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName sysname,
	@TableName sysname,
	@PKName sysname,
	@VehCat sysname
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

SET @SQL = 'SELECT [' +@TableName + '].[' +@PKName +']' + ', + [' +@TableName + '].[BATCH] , + [' +@TableName + ' PREV].['  +@FieldName + '] AS PREV,[' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName   +''' AS FieldName, '+ '[' +@TableName + '].[' +@PKName +'] + [' +@TableName + '].[BATCH] +'''  +@FieldName   +''' + [' +@TableName + ' PREV].['  +@FieldName + '] + [' +@TableName + '].['+@FieldName +'] AS PK'+ ',
    CASE
            WHEN RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],''''))) THEN ''False''
            ELSE ''True'' 
    END AS Resultdiff, '
    +'[' + @TableName + '].[' +@VehCat + ']' +'
FROM [' + @TableName+ ']'+'
    INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'


--EXEC(@SQL)--
PRINT @SQL

Open in new window


The sql this produces

SELECT [GLASS FULL TABLE].[GLASSid_GLASScat], + [GLASS FULL TABLE].[BATCH] , + [GLASS FULL TABLE PREV].[Horse_power] AS PREV,[GLASS FULL TABLE].[Horse_power] AS CHANGE, 'Horse_power' AS FieldName, [GLASS FULL TABLE].[GLASSid_GLASScat] + [GLASS FULL TABLE].[BATCH] + 'Horse_power' + [GLASS FULL TABLE PREV].[Horse_power] + [GLASS FULL TABLE].[Horse_power] AS PK,
    CASE
            WHEN RTrim(LTrim(IsNull([GLASS FULL TABLE].[Horse_power],''))) <> RTrim(LTrim(IsNull([GLASS FULL TABLE PREV].[Horse_power],''))) THEN 'False'
            ELSE 'True' 
    END AS Resultdiff, [GLASS FULL TABLE].[GLASS_cat]
FROM [GLASS FULL TABLE]
    INNER JOIN [GLASS FULL TABLE PREV] ON [GLASS FULL TABLE].[GLASSid_GLASScat] = [GLASS FULL TABLE PREV].GLASSid_GLASScat
WHERE RTrim(LTrim(IsNull([GLASS FULL TABLE].[Horse_power],''))) <> RTrim(LTrim(IsNull([GLASS FULL TABLE PREV].[Horse_power],'')))

Open in new window

Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

I can see that you're playing with the @SQL string. Remove the '+' operator from the inside of single quotes:
SET @SQL = 'SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].[BATCH] , [' +@TableName + ' PREV].['  +@FieldName + '] AS PREV,[' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName   +''' AS FieldName, '+ '[' +@TableName + '].[' +@PKName +'] + [' +@TableName + '].[BATCH] +'''  +@FieldName   +''' + [' +@TableName + ' PREV].['  +@FieldName + '] + [' +@TableName + '].['+@FieldName +'] AS PK'+ ',
    CASE
            WHEN RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],''''))) THEN ''False''
            ELSE ''True'' 
    END AS Resultdiff, '
    +'[' + @TableName + '].[' +@VehCat + ']' +'
FROM [' + @TableName+ ']'+'
    INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'

Open in new window

Found another one:
SET @SQL = 'SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].[BATCH] , [' +@TableName + ' PREV].['  +@FieldName + '] AS PREV,[' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName   +''' AS FieldName, '+ '[' +@TableName + '].[' +@PKName +'] + [' +@TableName + '].[BATCH] + [' +@TableName + ' PREV].['  +@FieldName + '] + [' +@TableName + '].['+@FieldName +'] AS PK'+ ',
    CASE
            WHEN RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],''''))) THEN ''False''
            ELSE ''True'' 
    END AS Resultdiff, '
    +'[' + @TableName + '].[' +@VehCat + ']' +'
FROM [' + @TableName+ ']'+'
    INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'

Open in new window

Hi,
Please try this. I have changed all your code. Hopefully after this you will not face any issue.

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClientDifferencesLogic]    Script Date: 06/02/2017 15:59:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName sysname,
	@TableName sysname,
	@PKName sysname,
	@VehCat sysname
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

SET @SQL = 'SELECT [' +@TableName + '].[' +@PKName +']' + ', [' + @TableName + '].[BATCH] , [' +@TableName + ' PREV].['  +@FieldName + '] AS PREV
,[' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName   +''' AS FieldName, '+ '[' +@TableName + '].[' +@PKName +'] , [' +@TableName 
+ '].[BATCH] '''  +@FieldName   +''' , [' +@TableName + ' PREV].['  +@FieldName + '] , [' +@TableName + '].['+@FieldName +'] AS PK'+ ',
    CASE
            WHEN RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],''''))) 
			THEN ''False''
            ELSE ''True'' 
    END AS Resultdiff, '
    +'[' + @TableName + '].[' +@VehCat + ']' +'
FROM [' + @TableName+ ']'+'
    INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'

--EXEC(@SQL)--
PRINT @SQL

Open in new window


Hope it helps!
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

ID: 41996189 gives much the same response as mine when trying to join together (as in create string)  the AS PK.:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value '219584B201607' to data type int.

Pawans didnt concatenate the as PK field
if i take that last as PK field out i get:

GLASSid_GLASScat      BATCH      PREV      CHANGE      FieldName      Resultdiff      GLASS_cat
219584B                       201607        3                    3           Horse_power      False                B
219591B                       201607        6                    8           Horse_power      False                B
160451C                       201611       268            242           Horse_power      False                C
219571B                       201607       10                    9            Horse_power      False                B
236230B                       201611       21                    14            Horse_power      False                B
209050B                       201607       0                     9             Horse_power      False                B


I am trying to create a field as PK for first row = "219584B201607Horse_power32".... this is unique for that row
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Don't concatenate all the values.  If you need a PK that's 4 columns, just define the PK itself as 4 separate columns.

If you want a unique numeric value as well, you can always add a separate, unique identity column.
I defined the pk as scott described in what we will call here and now the results table not the table I am using here. so do i not need to create the pk by concatenation for the new table itself to send it a value.
Ive got to test and do some inserts into the results table across a range of values but on the first run looks great thank you.
Glad to help!