June 11, 2016 at 8:10 pm
I've table and User-Defined Table Types as following,
CREATE TABLE [dbo].[tmpaccthist_2013_2014_report_2](
[disatu_code] [nvarchar](50) NULL,
[disatu_desc] [nvarchar](50) NULL,
[cmpy_code] [nvarchar](50) NULL,
[kod] [nvarchar](5) NULL,
[state_code] [nvarchar](50) NULL,
[state_desc] [nvarchar](500) NULL,
[campus_cd] [nvarchar](30) NULL,
[year_num] [int] NULL,
[period_num] [int] NULL,
[trnxDte] [date] NULL,
[acct_code] [nvarchar](50) NULL,
[group_1Digit] [char](5) NULL,
[group_2Digit] [char](5) NULL,
[group_3Digit] [char](5) NULL,
[chart_code] [char](5) NULL,
[pusat_kos] [nvarchar](10) NULL,
[close_amt] [decimal](18, 2) NULL
) ON [PRIMARY];
CREATE TYPE [dbo].[SearchTableType] AS TABLE(
[batch_Id] [uniqueidentifier] NULL,
[kod] [nvarchar](5) NULL,
[year_num] [int] NULL,
[chart_code] [char](5) NULL,
[close_amt] [decimal](18, 2) NULL
);
I execute below T-SQL,
Declare @campus_cd nvarchar(300);
Declare @cmpy_code nvarchar(500);
Declare @year1 int;
Declare @year2 int;
Declare @period int;
Declare @batch_Id uniqueidentifier
Declare @crpt_NotaPenyataKewangan_12_Vertical_1 table(
batch_Id uniqueidentifier NULL,
kod nvarchar (5) NULL,
year_num int NULL,
chart_code char (5) NULL,
close_amt decimal (18, 2) NULL
);
insert into @crpt_NotaPenyataKewangan_12_Vertical_1
select @batch_Id,
[kod], [year_num], [chart_code], sum([close_amt]) as close_amt
from tmpaccthist_2013_2014_report_2
where 1=1
AND campus_cd in (
SELECT Value FROM fn_Split(@campus_cd, ',')
)
AND kod in (
SELECT Value FROM fn_Split(@cmpy_code, ',')
)
AND year_num = @year1 and period_num=@period
Group by [kod], [year_num], [chart_code];
Declare @params nvarchar(500);
Declare @get_amt_Year1 decimal(18,2);
Declare @get_amt_Year2 decimal(18,2);
DECLARE @sqlCommand nvarchar(4000);
Declare @get_SQLExpr_String nvarchar(4000);
Set @get_SQLExpr_String=' AND chart_code in (81106,81118,81202)'
SET @sqlCommand = 'select
@ret_amt_Year1 = SUM(CASE WHEN year_num = ' + cast(@year1 as nvarchar(10)) + ' THEN close_amt ELSE 0 END),
@ret_amt_Year2 = SUM(CASE WHEN year_num = ' + cast(@year2 as nvarchar(10)) + ' THEN close_amt ELSE 0 END)
from
@what_crpt_NotaPenyataKewangan_12_Vertical_1
where 1=1 AND batch_Id = @what_batch_Id '
SET @sqlCommand = @sqlCommand + @get_SQLExpr_String
set @params='@what_crpt_NotaPenyataKewangan_12_Vertical_1 As SearchTableType READONLY, @what_batch_Id uniqueidentifier, @ret_amt_Year1 decimal(18,2) OUTPUT,
@ret_amt_Year2 decimal(18,2) OUTPUT'
exec sp_executesql @sqlCommand, @params, @what_crpt_NotaPenyataKewangan_12_Vertical_1 = @crpt_NotaPenyataKewangan_12_Vertical_1, @what_batch_Id = @batch_Id,
@ret_amt_Year1=@get_amt_Year1 OUTPUT, @ret_amt_Year2 = @get_amt_Year2 OUTPUT;
Unfortunately, got this error,
(0 row(s) affected)
Msg 206, Level 16, State 2, Line 0
Operand type clash: table is incompatible with SearchTableType
Please help. As a result, I know - How to use Table variable in a dynamic sql query
June 12, 2016 at 11:19 am
I can't test because I don't have your tmpaccthist_2013_2014_report_2 table or fn_split
BUT don't you have to declare @crpt_NotaPenyataKewangan_12_Vertical_1 as being a SearchTableType e.g.
Declare @crpt_NotaPenyataKewangan_12_Vertical_1 SearchTableType
You've declared it with the same underlying structure as SearchTableType but SQL Server thinks there's a type mismatch.
June 13, 2016 at 12:06 pm
Hello,
This is fn_split
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
GO
Please help
June 13, 2016 at 12:23 pm
Douglas is right. Even if the definitions are the same, you need to use the defined table-type.
On a different topic, you might want to change your splitter function to prevent performance problems. Here's a much better option:
June 13, 2016 at 4:35 pm
Use temp table instead.
#Table created in outer query is available within the scope of your dynamic SQL.
You may simply remove the table parameter then.
It's much better way to pass tables to procedures/dynamic SQL.
_____________
Code for TallyGenerator
June 14, 2016 at 2:05 am
Qira (6/11/2016)
...Please help. As a result, I know - How to use Table variable in a dynamic sql query
This batch looks horrendously overcomplicated for what it's doing. Why can't you make the whole lot a single query?
SELECT
@get_amt_Year1 = SUM(CASE WHEN year_num = @year1 THEN close_amt ELSE 0 END),
@get_amt_Year2 = SUM(CASE WHEN year_num = @year2 THEN close_amt ELSE 0 END)
FROM tmpaccthist_2013_2014_report_2
WHERE year_num IN (@year1,@year2)
AND period_num = @period
AND chart_code in (81106,81118,81202)
AND campus_cd IN (SELECT [Value] FROM fn_Split(@campus_cd, ','))
AND kod IN (SELECT [Value] FROM fn_Split(@cmpy_code, ','))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply