Sql Stored Procedure

You can do it but there will be no improvement in the query performance.
It will only reduce your code length :)

aha ok is it fine then too continue with those if blocks with no else?

Hi,
Please use this -

USE [ClientData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@ClientID Int
	
)
AS
BEGIN

SET NOCOUNT ON;

		DECLARE @SQL AS VARCHAR(MAX) =''
		DECLARE @ReplaceTablePK AS VARCHAR(1000) = ''

		;WITH CTE AS
		(
			SELECT 1 ClientID ,'tClient' TableName  , 'abiCode' PKName
			UNION ALL 
			SELECT 2 ,'Cap Data','CAPid_CAPcat'
			UNION ALL
			SELECT 3 , '[Glass Full Table]', 'GLASSid_GLASScat'
		)
		SELECT TOP 1 @ReplaceTablePK =  TableName+'.'+ PKName FROM CTE WHERE ClientID = @ClientId

		SET @SQL = 'SELECT [Glass Full Table].GLASSid_GLASScat, 
			CASE
					WHEN RTrim(LTrim(IsNull([Glass Full Table].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],''''))) THEN ''False''
					ELSE ''True'' 
			END AS Resultdiff, '+
			@ReplaceTablePK + '
		FROM CAPDATA 
			INNER JOIN 
			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].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],'''')))'

		EXEC(@SQL)

END

Select allOpen in new window

Hope it helps!

As I have red squiggles if that makes sense

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClientDifferencesLogic]    Script Date: 03/02/2017 10:25:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@ClientID Int
	
)
AS
BEGIN

SET NOCOUNT ON;

IF @ClientID = 1
     DECLARE @SQL AS VARCHAR(MAX) =''

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

      EXEC(@SQL)
END


IF @ClientID = 2
     DECLARE @SQL AS VARCHAR(MAX) =''

     SET @SQL = 'SELECT CAPDATA.CAPid_CAPcat, 
            CASE
                  WHEN RTrim(LTrim(IsNull([CAPDATA].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([CAPDATA PREV].[' + @FieldName + '],''''))) THEN ''False''
                  ELSE ''True'' 
            END AS Resultdiff, 
            CAPDATA.CAP_cat
      FROM CAPDATA 
            INNER JOIN [CAPDATA PREV] ON CAPDATA.CAPid_CAPcat = [CAPDATA PREV].CAPid_CAPcat
      WHERE RTrim(LTrim(IsNull([CAPDATA].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([CAPDATA PREV].[' + @FieldName + '],'''')))'

      EXEC(@SQL)
END

IF @ClientID = 3
    BEGIN
	     DECLARE @SQL AS VARCHAR(MAX) =''

     SET @SQL = 'SELECT [Glass Full Table].GLASSid_GLASScat, 
            CASE
                  WHEN RTrim(LTrim(IsNull([Glass Full Table].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],''''))) THEN ''False''
                  ELSE ''True'' 
            END AS Resultdiff, 
            [Glass Full Table].GLASSid_GLASScat
      FROM CAPDATA 
            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].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],'''')))'

      EXEC(@SQL)
	END
END

Select allOpen in new window

Hi Peter,

Please refer the approach I gave in last comment. If does not look good if you use N number of Ifs.

My approach is very clear and extremely good. If you 100 tables, then you dont have repeat the code 100 times. Repeating same code again and again is not good.


With my code no repetition of same code again and again.

Hope it helps!

Peter, like this it should work (check the differences from your original code):

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@ClientID Int
	
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

IF @ClientID = 1    
     SET @SQL = 'SELECT tClient.abiCode, 
            CASE
                  WHEN RTrim(LTrim(IsNull([tClient].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([tClient PREV].[' + @FieldName + '],''''))) THEN ''False''
                  ELSE ''True'' 
            END AS Resultdiff, 
            tClient.abiCode
      FROM tClient 
            INNER JOIN [tClient PREV] ON tClient.abiCode = [tClient PREV].abiCode
      WHERE RTrim(LTrim(IsNull([tClient].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([tClient PREV].[' + @FieldName + '],'''')))'



IF @ClientID = 2
     SET @SQL = 'SELECT CAPDATA.CAPid_CAPcat, 
            CASE
                  WHEN RTrim(LTrim(IsNull([CAPDATA].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([CAPDATA PREV].[' + @FieldName + '],''''))) THEN ''False''
                  ELSE ''True'' 
            END AS Resultdiff, 
            CAPDATA.CAP_cat
      FROM CAPDATA 
            INNER JOIN [CAPDATA PREV] ON CAPDATA.CAPid_CAPcat = [CAPDATA PREV].CAPid_CAPcat
      WHERE RTrim(LTrim(IsNull([CAPDATA].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([CAPDATA PREV].[' + @FieldName + '],'''')))'



IF @ClientID = 3
     SET @SQL = 'SELECT [Glass Full Table].GLASSid_GLASScat, 
            CASE
                  WHEN RTrim(LTrim(IsNull([Glass Full Table].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],''''))) THEN ''False''
                  ELSE ''True'' 
            END AS Resultdiff, 
            [Glass Full Table].GLASSid_GLASScat
      FROM CAPDATA 
            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].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],'''')))'

EXEC(@SQL)

GO

Select allOpen in new window

I just saw Pawan's solution now ive put the substitutions in but the tableName variable is underlined in red so I may have done something wrong in my understanding

USE [ClientData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@ClientID Int
	
)
AS
BEGIN

SET NOCOUNT ON;

		DECLARE @SQL AS VARCHAR(MAX) =''
		DECLARE @ReplaceTablePK AS VARCHAR(1000) = ''

		;WITH CTE AS
		(
			SELECT 1 ClientID ,'tClient' TableName  , 'abiCode' PKName
			UNION ALL 
			SELECT 2 ,'Cap Data','CAPid_CAPcat'
			UNION ALL
			SELECT 3 , '[Glass Full Table]', 'GLASSid_GLASScat'
		)
		SELECT TOP 1 @ReplaceTablePK =  TableName+'.'+ PKName FROM CTE WHERE ClientID = @ClientId

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

		EXEC(@SQL)

END

Select allOpen in new window

Use this -

USE [ClientData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@ClientID Int
	
)
AS
BEGIN

SET NOCOUNT ON;

		DECLARE @SQL AS VARCHAR(MAX) =''
		DECLARE @ReplaceTablePK AS VARCHAR(1000) = ''

		;WITH CTE AS
		(
			SELECT 1 ClientID ,'tClient' TableName  , 'abiCode' PKName
			UNION ALL 
			SELECT 2 ,'Cap Data','CAPid_CAPcat'
			UNION ALL
			SELECT 3 , '[Glass Full Table]', 'GLASSid_GLASScat'
		)
		SELECT TOP 1 @ReplaceTablePK =  TableName+'.'+ PKName FROM CTE WHERE ClientID = @ClientId

		SET @SQL = 'SELECT [Glass Full Table].GLASSid_GLASScat, 
			CASE
					WHEN RTrim(LTrim(IsNull([Glass Full Table].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],''''))) THEN ''False''
					ELSE ''True'' 
			END AS Resultdiff, '+
			@ReplaceTablePK + '
		FROM CAPDATA 
			INNER JOIN 
			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].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([Glass Full Table PREV].[' + @FieldName + '],'''')))'

		EXEC(@SQL)

END

Select allOpen in new window

You can use Pawan's code but you'll need to alter the SP every time you add a new table so I'm not sure what are you gain with that.

You can use If's code but you'll need to alter the SP every time you add a new table because you have to add another if.
so I'm sure that is not good programming.

how do i call the procedure with the extra variable?

I have EXEC [dbo].[usp_ClientDifferencesLogic] 'CC', 1

I prefer the non if version as it allows for future, I am trying both here as I want to get accustomed to this

Hi,
Yes this is correct - EXEC [dbo].[usp_ClientDifferencesLogic] 'CC', 1

I shall change the code for you in a moment.

The only way to have your SP ready and do not alter more is to pass the table and column names instead of the client id. If that's ok for you, then use this version:

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClientDifferencesLogic]    Script Date: 03/02/2017 10:25:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName system,
	@TableName system,
	@ColumnName system
	
)
AS
BEGIN

DECLARE @TableNamePREV AS system

SET @TableNamePREV = @TableName + ' PREV'

SET NOCOUNT ON;

DECLARE @SQL AS VARCHAR(MAX) =''

SET @SQL = 'SELECT ' + @TableName + '.' + @ColumnName + ', 
    CASE
            WHEN RTrim(LTrim(IsNull(' + @TableName + '.[' + @FieldName + '],''''))) <> RTrim(LTrim(IsNull(' + @TableNamePREV + '.[' + @FieldName + '],''''))) THEN ''False''
            ELSE ''True'' 
    END AS Resultdiff, 
    ' + @TableName + '.' + @ColumnName + '
FROM ' + @TableName + ' 
    INNER JOIN ' + @TableNamePREV + ' ON ' + @TableName + '.abiCode = ' + @TableNamePREV + '.abiCode
WHERE RTrim(LTrim(IsNull(' + @TableName + '.[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull(' + @TableNamePREV + '.[' + @FieldName + '],'''')))'

EXEC(@SQL)

GO

Select allOpen in new window

I wonder if its worth sending the Clientname, the PKFieldName and cliename to the procedure so that it always works..I will have a play around and see how I progress.

both have merits

Hi,
Please use this final code for you- EDITED

USE [ClientData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@ClientID Int	
)
AS
BEGIN

SET NOCOUNT ON;

		DECLARE @SQL AS VARCHAR(MAX) =''
		DECLARE @tableName AS VARCHAR(1000) = ''
		DECLARE @Pk AS VARCHAR(1000) = ''

		;WITH CTE AS
		(
			SELECT 1 ClientID ,'tClient' TableName  , 'abiCode' PKName
			UNION ALL 
			SELECT 2 ,'Cap Data','CAPid_CAPcat'
			UNION ALL
			SELECT 3 , '[Glass Full Table]', 'GLASSid_GLASScat'
		)
		SELECT TOP 1 @tableName =  TableName , @Pk =  PKName FROM CTE WHERE ClientID = @ClientId		

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

		EXEC(@SQL)

END

Select allOpen in new window

Hope it helps!

I have done this now I know its wrong somewhere in syntax but I dont think I am far off.

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(25),
	@TableName varchar(25),
	@PKName varchar(25)
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

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


EXEC(@SQL)

GO

Select allOpen in new window

Looks great..the bit I dont understand, if i am sending the PK and the tablename now  why it now needs the case within the procedure? my intent is already known now .

Welcome Peter. Glad to help.

youve both been of great assistance in my understanding of this are you both happy to share the points?

how do I call it the syntax? with multiple parameters?

EXEC [dbo].[usp_ClientDifferencesLogic] 'CC' 1 -  something missing here

Peter, which solution are you going to use?

Comma missing. Use below.

EXEC [dbo].[usp_ClientDifferencesLogic] 'CC' , 1

as always deeply in your debt thank you

one minor hiccup when i tried to modify

Ah, saw it (it's always a missing single quote):

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

Select allOpen in new window

the +@pkname before the from is underlined

worked a treat

I called now: EXEC [dbo].[usp_ClientDifferencesLogic] 'CC' , 'tClient', 'AbiCode'

says Msg 1087, Level 15, State 2, Line 10
Must declare the table variable "@TableName".

You need to provide the full SP code.
Also, don't use varchar(25). That's too short. You should always use system data type when dealing with object names. I've used this in my examples.

it looks ok

do I change it from varchar to a different type?

Oopps, sorry. I meant sysname and not system.
And post your code and not the SSMS snapshot.

at present they now say varchar(100)

USE [ClientData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName varchar(100),
	@TableName varchar(100),
	@PKName varchar(100)
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

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


EXEC(@SQL)

GO

Select allOpen in new window

You didn't use my last comment code. Check the FROM clause. It should be:
FROM ' + @TableName + '

Sorry, you used it but it wasn't there :)

at present they now say varchar(100)

Maximum length for a SQL Server object is 128. Using sysname you can avoid future changes by the SQL engine because it's always mapped with the current version limits.

the EXEC [dbo].[usp_ClientDifferencesLogic] 'CC' , 'tClient', 'AbiCode' itself says too many parameters yet the procedure itself has the 3

USE [ClientData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

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


EXEC(@SQL)

GO

Select allOpen in new window

Are you running on the correct database context?

not sure what that means.. context

I have this
use [ClientData]


go

then the exec and the usp is there

I mean, are you running in the correct database?
Imagine that you have any other database with the same SP name (for tests purposes, for example) and the SP there has only 2 parameters. Then you'll get that error.

I am using the correct db and after the succesful alter I disconnected and reconnected

I tried the wizard that generated this:

USE [ClientData]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[usp_ClientDifferencesLogic]
            @FieldName = 'CC',
            @TableName = 'tClient',
            @PKName = 'Abicode'

SELECT      'Return Value' = @return_value

GO


but it seems its unhappy with @TableName

Msg 1087, Level 15, State 2, Line 8
Must declare the table variable "@TableName".

Must declare the table variable "@TableName".

You didn't solve yet this issue? You might missed my comment: https://www.experts-exchange.com/questions/29000011/Sql-Stored-Procedure.html?anchorAnswerId=41992474#a41992474

If you want the full code with the fix:

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

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

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


EXEC(@SQL)

GO

Select allOpen in new window

could it be this as the var was in red ive changed it too:

FROM' + @TableName

i did miss that i think, ive run the alter now again just to be sure

it stiil gives

Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@TableName".

Yes, variables should be black, meaning that they will be replaced by their values.
Be careful with spaces, though. 'FROM' + @TableName will give you error and 'FROM ' + @TableName not because it has an space separating the keyword FROM from the variable value.

Must declare the scalar variable "@TableName".

Did you add the + ' after the variable name?

all the vars are in black and statements to build the string in red which looks good

in the call? ID: 41992536

no i left the code as in ID: 41992519 as it says that has the fix

the current code is:

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

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

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


EXEC(@SQL)

Select allOpen in new window

Try to PRINT the variable instead of EXEC it (comment this line so it won't execute the code):

--EXEC(@SQL)
PRNT @SQL

Select allOpen in new window

Post here what it printed to screen.

it returned this so looks like its the syntax after the ON:

SELECT tClient.AbiCode,
    CASE
            WHEN RTrim(LTrim(IsNull([tClient].[CC],''))) <> RTrim(LTrim(IsNull([tClient PREV].[CC],''))) THEN 'False'
            ELSE 'True' 
    END AS Resultdiff, tClient.AbiCode
FROM tClient
    INNER JOIN [tClient PREV] ON  @TableName .AbiCode = [tClient.AbiCode
WHERE RTrim(LTrim(IsNull([tClient].[CC],''))) <> RTrim(LTrim(IsNull([tClient PREV].[CC],'')))

Select allOpen in new window

ive corrected to this:

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

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

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


--EXEC(@SQL)
PRINT @SQL

Select allOpen in new window

and it printed:

SELECT tClient.AbiCode,
    CASE
            WHEN RTrim(LTrim(IsNull([tClient].[CC],''))) <> RTrim(LTrim(IsNull([tClient PREV].[CC],''))) THEN 'False'
            ELSE 'True' 
    END AS Resultdiff, tClient.AbiCode
FROM tClient
    INNER JOIN [tClient PREV] ON tClient.AbiCode = [tClient.AbiCode
WHERE RTrim(LTrim(IsNull([tClient].[CC],''))) <> RTrim(LTrim(IsNull([tClient PREV].[CC],'')))

Select allOpen in new window

Spot on.
Good catch.

You forgot to close the last bracket. Fixed now:

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

Select allOpen in new window

yes i spotted it then got caught on the phone

hey presto its working, thank you so so much

Your welcome.
Btw, you should only close a question after testing all the available proposed solutions. Hopefully you realized the split of points isn't really fair.
Cheers