Result of dynamic sql with parameteres into a variable

  • Edit 25.4.2013:::

    Problem was solved, but I have question about view, my last reply...

    thx for response

    ////////

    Hello, i have problem with dynamic sql in cursor and i want to set to variable

    first I had problem, when I wanted to use variable int, there was problem with assign varchar to int ...I solved with SET @WORK_SEQ1=CAST(@WORK_SEQ as varchar(2))

    then I had problem with error -- Must declare the scalar variable "@RESULT_COUNT".

    and I found something on internet and I tried executed @sqlStr like this

    exec sp_executesql

    @query=@sqlStr,

    @params=N'@RESULT_COUNT INT OUTPUT',

    @RESULT_COUNT=@RESULT_COUNT OUTPUT

    PRINT @RESULT_COUNT

    But now I have problem with this error

    Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    All code is below

    SET @sqlStr = '

    DECLARE myCursor CURSOR FOR

    SELECT LINE_CODE, ORDER_DATE, COMMIT_NO, BODY_NO, STATION_ID, WORK_POS, WORK_QTY, WORK_SEQ, WORK_TYPE, ITEM_CODE, ALC_CODE, OPTION_VALUE, LIMITV_LOW, LIMITV_HIGH

    FROM MCS_MESDB.dbo.TB_MASTER_' + @P_LINE_CODE + ' M (NOLOCK)

    WHERE M.ORDER_DATE = ''' + @P_ORDER_DATE + '''

    AND M.COMMIT_NO = ''' + @P_COMMIT_NO + '''

    AND M.LINE_CODE = ''' + @P_LINE_CODE + '''

    AND M.WORK_TYPE = ''N''

    AND M.STATION_ID= ''' + @P_STATION_ID + '''

    '

    EXEC(@sqlStr);

    OPEN myCursor

    FETCH NEXT FROM myCursor INTO @LINE_CODE, @ORDER_DATE, @COMMIT_NO, @BODY_NO, @STATION_ID, @WORK_POS, @WORK_QTY, @WORK_SEQ, @WORK_TYPE, @ITEM_CODE, @ALC_CODE, @OPTION_VALUE, @LIMITV_LOW, @LIMITV_HIGH

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE @RESULT_COUNT int

    DECLARE @WORK_SEQ1 varchar(2)

    SET @WORK_SEQ1=CAST(@WORK_SEQ as varchar(2))

    PROBLEM IS HERE >> SET @sqlStr = N'SELECT @RESULT_COUNT=(SELECT CASE(COUNT(*)) WHEN 0 THEN 1 ELSE COUNT(*) END

    FROM MCS_MESDB.dbo.TB_RESULT_TOOL_ENG01R WITH(NOLOCK)

    WHERE ORDER_DATE = ''' + @ORDER_DATE + '''

    AND COMMIT_NO = ''' + @COMMIT_NO + '''

    AND STATION_ID = ''' + @STATION_ID + '''

    AND ITEM_CODE = ''' + @ITEM_CODE + '''

    AND WORK_SEQ = ''' +@WORK_SEQ1 + '''

    AND WORK_RESULT = ''OK''

    )'

    exec sp_executesql

    @query=@sqlStr,

    @params=N'@RESULT_COUNT INT OUTPUT',

    @RESULT_COUNT=@RESULT_COUNT OUTPUT

    PRINT @RESULT_COUNT

  • I solved this, but I think that is little dirty way..

    Here is just select inside cursor, where was problem

    I inserted to table variable executed @sqlStr

    and then set to @RESULT_COUNT the select from @t

    if somebody will have another solution , please write here

    THX

    SET @sqlStr = N'DECLARE @RESULT_COUNT int=0;SELECT @RESULT_COUNT=(SELECT CASE(COUNT(*)) WHEN 0 THEN 1 ELSE COUNT(*) END

    FROM MCS_MESDB.dbo.TB_RESULT_TOOL_ENG01R WITH(NOLOCK)

    WHERE ORDER_DATE = ''' + @ORDER_DATE + '''

    AND COMMIT_NO = ''' + @COMMIT_NO + '''

    AND STATION_ID = ''' + @STATION_ID + '''

    AND ITEM_CODE = ''' + @ITEM_CODE + '''

    AND WORK_SEQ = ''' +@WORK_SEQ1 + '''

    AND WORK_RESULT = ''OK''

    )

    SELECT @RESULT_COUNT as RESULT'

    DECLARE @t table (ID int)

    insert into @t

    exec ( @sqlStr)

    SET @RESULT_COUNT=(SELECT ID from @t)

    SELECT @RESULT_COUNT as RESULT

  • I am happy you found a way forward but as a side comment I must say that your code raises several red flags. The dynamic cursor is a close second but this line specifically calls out the number one problem and is the root of why you need a dynamic cursor in the first place:

    FROM MCS_MESDB.dbo.TB_MASTER_' + @P_LINE_CODE + ' M (NOLOCK)

    Anytime you have to build code dynamically because your table names are being passed into a procedure, or being looked up in a table, know that you are dealing with the result of some poor database design decisions. You would not have to do that if you had a proper relational data model in place.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (3/7/2013)


    I am happy you found a way forward but as a side comment I must say that your code raises several red flags. The dynamic cursor is a close second but this line specifically calls out the number one problem and is the root of why you need a dynamic cursor in the first place:

    FROM MCS_MESDB.dbo.TB_MASTER_' + @P_LINE_CODE + ' M (NOLOCK)

    hmm but it is good, when you select the @sqlStr, you will get FROM MCS_MESDB.dbo.TB_MASTER_ENG01 M (NOLOCK)

    for sure If I do dynamic sql, before exec, I still select than I will get right query with parameters.

    Reason why there is dynamic sql is that @LINE_CODE can be different and we want to use one procedure for this.

    and I think is not good way with if/else or something else

    opc.three (3/7/2013)


    Anytime you have to build code dynamically because your table names are being passed into a procedure, or being looked up in a table, know that you are dealing with the result of some poor database design decisions. You would not have to do that if you had a proper relational data model in place.

    Here I dont understand, can you explain me this sentence? do you think another my topic? ... but it is just for reporting in aspx site, where I can choose LINE_CODE and next parameters and see result of this query --

    know that you are dealing with the result of some poor database design decisions

    :w00t: you know, i am learning myself and nobody, even my boss, doesnt know about lot of things sql.

    So I cannot discuss about my work with someone better than I ...it is little difficult

    THX for response

  • tony28 (3/7/2013)


    :w00t: you know, i am learning myself and nobody, even my boss, doesnt know about lot of things sql.

    So I cannot discuss about my work with someone better than I ...it is little difficult

    Then let me warn you about using the NOLOCK hint. It is not a good way to make your queries go faster. It is however a good way to sometimes get duplicate or missing data which creates nearly impossible to reproduce bugs.

    http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

    I have to agree with opc.three that passing table names like this to a procedure is a sign of a design that is going to cause issues in the long run. It is difficult to maintain and even tougher to debug.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • tony28 (3/7/2013)


    opc.three (3/7/2013)


    I am happy you found a way forward but as a side comment I must say that your code raises several red flags. The dynamic cursor is a close second but this line specifically calls out the number one problem and is the root of why you need a dynamic cursor in the first place:

    FROM MCS_MESDB.dbo.TB_MASTER_' + @P_LINE_CODE + ' M (NOLOCK)

    hmm but it is good, when you select the @sqlStr, you will get FROM MCS_MESDB.dbo.TB_MASTER_ENG01 M (NOLOCK)

    for sure If I do dynamic sql, before exec, I still select than I will get right query with parameters.

    Reason why there is dynamic sql is that @LINE_CODE can be different and we want to use one procedure for this.

    and I think is not good way with if/else or something else

    Consider a design with a single table named MCS_MESDB.dbo.TB_MASTER that has a "type" column which contains data like "ENG01", "ENG02", "ENG03", etc. Then when you need data from the table that relates to what you currently store in MCS_MESDB.dbo.TB_MASTER_ENG01 you would simply ask for it in the WHERE-clause, e.g.

    WHERE type_column = 'ENG01'

    See how that eliminates the need for dynamic SQL?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Sean Lange


    Then let me warn you about using the NOLOCK hint. It is not a good way to make your queries go faster. It is however a good way to sometimes get duplicate or missing data which creates nearly impossible to reproduce bugs.

    Hello Sean,

    I know about it, but here is just report for us, when we need to check info about orders . So I need to get data if the table is updated or inserted ( it can be many times per few second, because I work in assemble of car )

    And here is just for one orders, so I dont need to use order by or something else you know.

    I have to agree with opc.three that passing table names like this to a procedure is a sign of a design that is going to cause issues in the long run. It is difficult to maintain and even tougher to debug.

    In the website is combobox just with line code, which we use you know and I think that we will not create new line .

    And I know, but we dont have solution, one is that i will use one procedure for each table or i will try solution by opc, if it will be possible

    opc.three (3/7/2013)


    Consider a design with a single table named MCS_MESDB.dbo.TB_MASTER that has a "type" column which contains data like "ENG01", "ENG02", "ENG03", etc. Then when you need data from the table that relates to what you currently store in MCS_MESDB.dbo.TB_MASTER_ENG01 you would simply ask for it in the WHERE-clause, e.g.

    WHERE type_column = 'ENG01'

    See how that eliminates the need for dynamic SQL?

    I almost understand, what do you think, but i am not sure, how can i get the select of this table to select in cursor, what I wrote.

    MCS_MESDB.dbo.TB_MASTER - one column with data like you wrote -- ENG01, ENG02 --- for example variable will be ENG01 so I will write query

    SELECT * FROM MCS_MESDB.dbo.TB_MASTER WHERE LINE_CODE=ENG01

    What result will I get ?

    THX FOR RESPONSE, it is very helpful for me.

  • Or do you think that I have merge all tables ? and just I will add next column with line code?

    is not possible because in master table are about 1300orders and each of order have about 30rows per day.

  • tony28 (3/7/2013)


    Or do you think that I have merge all tables ? and just I will add next column with line code?

    Yes, you would merge all the tables with an ENG## suffix into one table named MCS_MESDB.dbo.TB_MASTER that contained a column like LINE_CODE (from your example).

    is not possible because in master table are about 1300orders and each of order have about 30rows per day.

    I am not sure what you are getting at here? Are you worried about volume?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (3/7/2013)


    tony28 (3/7/2013)


    Or do you think that I have merge all tables ? and just I will add next column with line code?

    Yes, you would merge all the tables with an ENG## suffix into one table named MCS_MESDB.dbo.TB_MASTER that contained a column like LINE_CODE (from your example).

    is not possible because in master table are about 1300orders and each of order have about 30rows per day.

    I am not sure what you are getting at here? Are you worried about volume?

    yes I think volume, because we have I think about 15MASTER table and depends on the size of line and count of positions, then in table is average about 30rows to one order. Average of orders is 1300. We move data one times per month.

    And lot of procedure use the master tables of each lines you know.

    Or do you think that it doesnt have effect ?

  • tony28 (3/7/2013)


    opc.three (3/7/2013)


    tony28 (3/7/2013)


    Or do you think that I have merge all tables ? and just I will add next column with line code?

    Yes, you would merge all the tables with an ENG## suffix into one table named MCS_MESDB.dbo.TB_MASTER that contained a column like LINE_CODE (from your example).

    is not possible because in master table are about 1300orders and each of order have about 30rows per day.

    I am not sure what you are getting at here? Are you worried about volume?

    yes I think volume, because we have I think about 15MASTER table and depends on the size of line and count of positions, then in table is average about 30rows to one order. Average of orders is 1300. We move data one times per month.

    And lot of procedure use the master tables of each lines you know.

    Or do you think that it doesnt have effect ?

    1300 orders * 30 lines per order * 15 master tables = 585,000 rows

    585,000 rows added per month * 12 months = ~7 million rows added per year

    With proper indexing, on even a low-powered server, SQL Server will be able to handle a table with many times this number of rows with no trouble whatsoever.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (3/7/2013)


    tony28 (3/7/2013)


    opc.three (3/7/2013)


    tony28 (3/7/2013)


    Or do you think that I have merge all tables ? and just I will add next column with line code?

    Yes, you would merge all the tables with an ENG## suffix into one table named MCS_MESDB.dbo.TB_MASTER that contained a column like LINE_CODE (from your example).

    is not possible because in master table are about 1300orders and each of order have about 30rows per day.

    I am not sure what you are getting at here? Are you worried about volume?

    yes I think volume, because we have I think about 15MASTER table and depends on the size of line and count of positions, then in table is average about 30rows to one order. Average of orders is 1300. We move data one times per month.

    And lot of procedure use the master tables of each lines you know.

    Or do you think that it doesnt have effect ?

    1300 orders * 30 lines per order * 15 master tables = 585,000 rows

    585,000 rows added per month * 12 months = ~7 million rows added per year

    With proper indexing, on even a low-powered server, SQL Server will be able to handle a table with many times this number of rows with no trouble whatsoever.

    sorry for misunderstanding

    1300 orders * 30 lines per order * count of day in month(30) = 1 170 000 rows per month in one table

    1 170 000 * 15 * 12 = 210 600 000 rows

  • With proper indexing and query writing that number of rows will not be an issue. I have tables with many billions of rows in them.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • so it can be task for future, because I know that dynamic sql is not good way, but there is korean code you know and there are things which are direct against rules..

    lot of procedure, application ( mainly without procedure but with query in VB6 and very often we dont have direct source code..), tables are used.. I still study about HA and performance ...

    if do you have few good sites, you can post it here, it will be very helpful..

    thx for response

  • Hello,

    I have question what do you think about create view for all tables like I said above, but there is problem that I cannot create index for view with union all, but it can solve my problem with dynamic sql..

    you know because there is column with LINE_CODE

    also I will create VIEW_MASTER for all TB_MASTER_´LINE_CODE´

    ALTER VIEW [dbo].[VIEW_MASTER] WITH SCHEMABINDING AS

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVEN

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF1

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF2

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFA

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFC

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVRC

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM01

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPSB1

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_ENG01

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST1

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST2

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX01

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX02

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FCM01

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FEM01

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_RCM01

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP1

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP2

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRFE3

    UNION ALL

    SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ]

    ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS]

    ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRSB1

    do you know about any problem with performance? it can be any impact for our database ???

    thx a lot for response

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply