asked on

Sql server get data from a usp to use in a usp

I have this

EXEC [dbo].[usp_MaxBatchDate] 'tClient', 'BATCH'

Open in new window

it gives me an important piece of data that I would like to use and insert in the changeyearmonth field of the insert of another usp but I dont now how to call it and use the data from within a usp itself currently its set to the batchfieldnamedata but i need that to be the result from the maxbatchdate usp. I only need to know how to call it and use it i can do the sql change myself once I know.

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClientDifferencesLogicInsert]    Script Date: 13/02/2017 10:41:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogicInsert]
(
	@FieldName sysname,
	@TableName sysname,
	@PKName sysname,
	@VehCatName sysname,		
	@ClientName sysname,
	@TableNamePrev sysname,
	@BatchFieldName sysname
)
AS
DECLARE @MaxBatch AS VARCHAR(MAX) =''
DECLARE @SQL AS VARCHAR(MAX) =''

SET @SQL = '
INSERT INTO TblCompareEvents(ClientCode, ChangeYearMonth, Prev, Change, FieldName, VehicleCategory, PK_ID, ClientName )
SELECT [' +@TableName + '].[' +@PKName +']' + ', [' +@TableName + '].['+@BatchFieldName +'], [' +@TableNamePrev + '].['  +@FieldName + '] AS PREV, [' +@TableName + '].['+@FieldName +'] AS CHANGE, ''' +@FieldName  +''' AS FieldName, [' + @TableName + '].[' +@VehCatName + '],' +
'ISNULL(CAST(['+@TableName + '].[' +@PKName +'] AS VARCHAR(1000)),'''')
   + ISNULL(CAST(['+@TableName + '].['+@BatchFieldName +'] AS VARCHAR(1000)),'''')
   + ISNULL(CAST('''+@FieldName +''' AS VARCHAR(1000)),'''')
   + ISNULL(CAST(['+@TableNamePrev + '].['+@FieldName +'] AS VARCHAR(1000)),'''')
   + ISNULL(CAST(['+@TableName + '].['+@FieldName +'] AS VARCHAR(1000)),'''') AS PK,
   ''' + @ClientName +''' AS ClientName'   + '
FROM ['+ @TableName+']'+'
    INNER JOIN ['+@TableNamePrev +'] ON ['+ @TableName +'].['+@PKName +'] = ['+@TableNamePrev + '].['+ @PKName+']' +'
WHERE RTrim(LTrim(IsNull(['+@TableName +'].['+@FieldName +'],''''))) <> RTrim(LTrim(IsNull(['+@TableNamePrev +'].['+ @FieldName +'],'''')))' + ' 
AND NOT EXISTS (SELECT 1'+
	' FROM TblCompareEvents'+
	' WHERE  [ClientCode] =[' +@TableName + '].[' +@PKName +']'+
		' AND [ChangeYearMonth] = ['+@TableName + '].['+@BatchFieldName +']'+
		' AND [FieldName] = ''' +@FieldName  +'''' +
		' AND [Change] =[' +@TableName + '].['+@FieldName +']'+
		' AND [Prev] = [' +@TableNamePrev + '].['  +@FieldName + '])'
EXEC(@SQL)
PRINT @SQL

Open in new window

Vitor Montalvão

Can you post the code for usp_MaxBatchDate sp?

PeterBaileyUk

ASKER

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_MaxBatchDate]    Script Date: 13/02/2017 12:24:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_MaxBatchDate]
(

	@TableName sysname,
	@BatchFieldName sysname
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET @SQL = '
SELECT Max([' + @TableName +'].['+@BatchFieldName +']) AS MaxOf'+@BatchFieldName 
+' FROM ['+ @TableName +'];'

EXEC(@SQL)
PRINT @SQL

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

thank you Vitor. I have a question on bulk insert open but no one assisted so far maybe you can take a peak you will know if its possible to do

Vitor Montalvão

Your welcome.
I will follow up on the opened questions from weekend to find yours.