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.
The sql this produces
the error is Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value '219584B201607Horse_power'
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
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],'')))
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 + '],'''')))'
Hi,
Please try this. I have changed all your code. Hopefully after this you will not face any issue.
Hope it helps!
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
Hope it helps!
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
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
ASKER
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_power3 2".... this is unique for that row
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_power3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If you want a unique numeric value as well, you can always add a separate, unique identity column.
ASKER
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.
ASKER
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!
Open in new window