November 17, 2016 at 12:18 pm
DECLARE @TABNAME NVARCHAR(255)='dd',
@LAST_CHECKED_DATE_TIME VARCHAR(100)=CONVERT(VARCHAR(20),getdate()-20,101),
@SQL NVARCHAR (1000 ),
@COUNT INT ,
@TABLEHTML NVARCHAR (1000 ),
@SUBJ NVARCHAR (1000 ),
@ParmDefinition nvarchar(500),
@IntVariable1 char,
@IntVariable2 char,
@IntVariable3 char,
@IntVariable4 char
CREATE TABLE #FINAL_RESULT
(
TABLE_NAME VARCHAR (100),
LAST_CHECKED_DATE_TIME DATETIME ,
ROW_COUNT INT ,
INSERTED INT,
UPDATED INT,
DELETED INT
)
SET @SQL = 'SELECT '''+@TABNAME+''' AS TABLE_NAME,'''+@LAST_CHECKED_DATE_TIME+''' AS LAST_CHECKED_DATE_TIME, COUNT(*) AS ROW_COUNT FROM dd..[' + @TABNAME + ']
,COUNT(*) AS INSERTED FROM dd..[' + @TABNAME + '] where operation_cd=@inserted
,COUNT(*) AS UPDATED FROM dd..[' + @TABNAME + '] where operation_cd=@update and operation_cd=@old
,COUNT(*) AS DELETED FROM dd..[' + @TABNAME + '] where operation_cd=@delete
(NOLOCK) WHERE OPERATION_DATE >='''+@LAST_CHECKED_DATE_TIME+''''
PRINT @SQL
SET @IntVariable1='i'
SET @IntVariable2='u'
SET @IntVariable3='o'
SET @IntVariable4='d'
INSERT INTO #FINAL_RESULT
EXEC SP_EXECUTESQL @SQL , @ParmDefinition ,@inserted=@IntVariable1,@update=@IntVariable2,@old=@IntVariable3,@delete=@IntVariable4
Hi,
Iam trying to insert records into #temptable but im getting error as incorrect syntax .Can any one please suggest.
November 17, 2016 at 12:26 pm
You do not appear to have populated @ParmDefinition.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2016 at 12:34 pm
sorry i didn't get u
November 17, 2016 at 12:36 pm
Also, print out the value of @SQL instead of trying to execute it.
You'll see there are several syntax errors.
One of the main ones is that you're trying to use COUNT(*) FROM...WHERE as expressions for the returned columns, which won't work.
Since it's all from the same table, I'd suggest just using COUNT(CASE...) for those columns instead, e.g., COUNT(CASE WHEN operation_cd=something THEN 1 END).
If you're still not sure where to go after looking at the results of PRINT @SQL, then let us know what seems confusing.
Cheers!
November 17, 2016 at 12:40 pm
@Patrick123 (11/17/2016)
sorry i didn't get u
Before you call sp_ExecuteSQL, you need to populate @ParmDefinition with, somewhat unsurprisingly, given its name, the definitions of all the parameters.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2016 at 1:02 pm
You are looking for something like this:
SET QUOTED_IDENTIFIER OFF;
SET NOCOUNT ON;
DECLARE @SQLCmd nvarchar(max),
@SQLParm nvarchar(max),
@LastCheckedDateTime datetime = dateadd(day,-20,getdate()),
@IntVariable1 char(1) = 'i',
@IntVariable2 char(1) = 'u',
@IntVariable3 char(1) = 'o',
@IntVariable4 char(1) = 'd',
@TABNAME NVARCHAR(255) = 'dd';
CREATE TABLE #FINAL_RESULT(
TABLE_NAME VARCHAR (100),
LAST_CHECKED_DATE_TIME DATETIME ,
ROW_COUNT INT ,
INSERTED INT,
UPDATED INT,
DELETED INT
);
set @SQLParm = N'@inserted char(1),@update char(1),@old char(1),@delete char(1),@LastDateCheck datetime,@TableName nvarchar(255)';
set @SQLCmd = "
SELECT
@TableName as TABLE_NAME,
@LastDateCheck as LAST_CHECKED_DATE_TIME,
COUNT(*) as ROW_COUNT,
SUM(CASE WHEN operation_cd = @inserted THEN 1 ELSE 0 END) as INSERTED,
SUM(CASE WHEN operation_cd = @update or operation_cd = @old THEN 1 ELSE 0 END) as UPDATED,
SUM(CASE WHEN operation_cd = @delete THEN 1 ELSE 0 END) as DELETED,
FROM
dd.." + @TABNAME + "
WHERE
OPERATON_DATE >= @LastDateCheck;
";
select CAST(@SQLCmd as xml);
INSERT INTO #FINAL_RESULT
exec sys.sp_executesql @SQLCmd,
@SQLParm,
@inserted = @IntVariable1,
@update = @IntVariable2,
@old = @IntVariable3,
@delete = @IntVariable4,
@LastDateCheck = @LastCheckedDateTime,
@TableName = @TABNAME;
SET NOCOUNT OFF;
SET QUOTED_IDENTIFIER ON;
go
--cleanup my sandbox
drop table #FINAL_RESULT;
go
Any questions, please ask.
EDIT: Not tested, no promises.
November 17, 2016 at 1:06 pm
Jacob Wilkins (11/17/2016)
Also, print out the value of @SQL instead of trying to execute it.You'll see there are several syntax errors.
One of the main ones is that you're trying to use COUNT(*) FROM...WHERE as expressions for the returned columns, which won't work.
Since it's all from the same table, I'd suggest just using COUNT(CASE...) for those columns instead, e.g., COUNT(CASE WHEN operation_cd=something THEN 1 END).
If you're still not sure where to go after looking at the results of PRINT @SQL, then let us know what seems confusing.
Cheers!
Actually, you don't want a COUNT for the subset values.
November 17, 2016 at 1:08 pm
Slight mod to what I posted above:
SET QUOTED_IDENTIFIER OFF;
SET NOCOUNT ON;
DECLARE @SQLCmd nvarchar(max),
@SQLParm nvarchar(max),
@LastCheckedDateTime datetime = dateadd(day,-20,getdate()),
@IntVariable1 char(1) = 'i',
@IntVariable2 char(1) = 'u',
@IntVariable3 char(1) = 'o',
@IntVariable4 char(1) = 'd',
@TABNAME NVARCHAR(255) = 'dd';
CREATE TABLE #FINAL_RESULT(
TABLE_NAME VARCHAR (100),
LAST_CHECKED_DATE_TIME DATETIME ,
ROW_COUNT INT ,
INSERTED INT,
UPDATED INT,
DELETED INT
);
set @SQLParm = N'@inserted char(1),@update char(1),@old char(1),@delete char(1),@LastDateCheck datetime,@TableName nvarchar(255)';
set @SQLCmd = "
SELECT
@TableName as TABLE_NAME,
@LastDateCheck as LAST_CHECKED_DATE_TIME,
COUNT(*) as ROW_COUNT,
SUM(CASE WHEN operation_cd = @inserted THEN 1 ELSE 0 END) as INSERTED,
SUM(CASE WHEN operation_cd = @update or operation_cd = @old THEN 1 ELSE 0 END) as UPDATED,
SUM(CASE WHEN operation_cd = @delete THEN 1 ELSE 0 END) as DELETED,
FROM
dd.." + QUOTENAME(@TABNAME) + "
WHERE
OPERATON_DATE >= @LastDateCheck;
";
select CAST(@SQLCmd as xml);
INSERT INTO #FINAL_RESULT
exec sys.sp_executesql @SQLCmd,
@SQLParm,
@inserted = @IntVariable1,
@update = @IntVariable2,
@old = @IntVariable3,
@delete = @IntVariable4,
@LastDateCheck = @LastCheckedDateTime,
@TableName = @TABNAME;
SET NOCOUNT OFF;
SET QUOTED_IDENTIFIER ON;
go
--cleanup my sandbox
drop table #FINAL_RESULT;
go
November 17, 2016 at 1:09 pm
Lynn Pettis (11/17/2016)
Jacob Wilkins (11/17/2016)
Also, print out the value of @SQL instead of trying to execute it.You'll see there are several syntax errors.
One of the main ones is that you're trying to use COUNT(*) FROM...WHERE as expressions for the returned columns, which won't work.
Since it's all from the same table, I'd suggest just using COUNT(CASE...) for those columns instead, e.g., COUNT(CASE WHEN operation_cd=something THEN 1 END).
If you're still not sure where to go after looking at the results of PRINT @SQL, then let us know what seems confusing.
Cheers!
Actually, you don't want a COUNT for the subset values.
They're functionally equivalent as far as I know. Have you run into some performance differences with these patterns in the past?
Cheers!
November 17, 2016 at 1:17 pm
Jacob Wilkins (11/17/2016)
Lynn Pettis (11/17/2016)
Jacob Wilkins (11/17/2016)
Also, print out the value of @SQL instead of trying to execute it.You'll see there are several syntax errors.
One of the main ones is that you're trying to use COUNT(*) FROM...WHERE as expressions for the returned columns, which won't work.
Since it's all from the same table, I'd suggest just using COUNT(CASE...) for those columns instead, e.g., COUNT(CASE WHEN operation_cd=something THEN 1 END).
If you're still not sure where to go after looking at the results of PRINT @SQL, then let us know what seems confusing.
Cheers!
Actually, you don't want a COUNT for the subset values.
They're functionally equivalent as far as I know. Have you run into some performance differences with these patterns in the past?
Cheers!
Yes they are equivalent, but with the SUM method you eliminate the warning messages about the ignored nulls. Makes the query cleaner when run.
November 17, 2016 at 1:39 pm
Lynn Pettis (11/17/2016)
Jacob Wilkins (11/17/2016)
Lynn Pettis (11/17/2016)
Jacob Wilkins (11/17/2016)
Also, print out the value of @SQL instead of trying to execute it.You'll see there are several syntax errors.
One of the main ones is that you're trying to use COUNT(*) FROM...WHERE as expressions for the returned columns, which won't work.
Since it's all from the same table, I'd suggest just using COUNT(CASE...) for those columns instead, e.g., COUNT(CASE WHEN operation_cd=something THEN 1 END).
If you're still not sure where to go after looking at the results of PRINT @SQL, then let us know what seems confusing.
Cheers!
Actually, you don't want a COUNT for the subset values.
They're functionally equivalent as far as I know. Have you run into some performance differences with these patterns in the past?
Cheers!
Yes they are equivalent, but with the SUM method you eliminate the warning messages about the ignored nulls. Makes the query cleaner when run.
Ok, good. I was hoping I hadn't managed to miss some groundbreaking work on a big performance difference 🙂
So long as it's just that old thing, for me it's that vs making it abundantly clear you're doing a count. Pretty much a wash to my mind; I wouldn't object to either approach.
Cheers!
EDIT: Somehow I got carried away and cheered twice. I removed the overly-jubilant second cheer.
November 17, 2016 at 1:43 pm
EDIT: Somehow I got carried away and cheered twice. I removed the overly-jubilant second cheer.
Please make a note to put it back again tomorrow evening :smooooth:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2016 at 1:56 pm
Jacob Wilkins (11/17/2016)
Lynn Pettis (11/17/2016)
Jacob Wilkins (11/17/2016)
Lynn Pettis (11/17/2016)
Jacob Wilkins (11/17/2016)
Also, print out the value of @SQL instead of trying to execute it.You'll see there are several syntax errors.
One of the main ones is that you're trying to use COUNT(*) FROM...WHERE as expressions for the returned columns, which won't work.
Since it's all from the same table, I'd suggest just using COUNT(CASE...) for those columns instead, e.g., COUNT(CASE WHEN operation_cd=something THEN 1 END).
If you're still not sure where to go after looking at the results of PRINT @SQL, then let us know what seems confusing.
Cheers!
Actually, you don't want a COUNT for the subset values.
They're functionally equivalent as far as I know. Have you run into some performance differences with these patterns in the past?
Cheers!
Yes they are equivalent, but with the SUM method you eliminate the warning messages about the ignored nulls. Makes the query cleaner when run.
Ok, good. I was hoping I hadn't managed to miss some groundbreaking work on a big performance difference 🙂
So long as it's just that old thing, for me it's that vs making it abundantly clear you're doing a count. Pretty much a wash to my mind; I wouldn't object to either approach.
Cheers!
EDIT: Somehow I got carried away and cheered twice. I removed the overly-jubilant second cheer.
If I can avoid those annoying warning messages, I do. Especially if it is going to be run by end users, I don't want to take the chance of them seeing them and thinking something is wrong. Some times perception can be a problem.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply