SQL Server Performance

How to change oracle plsql code to sql server

Discussion in 'SQL Server 2008 General Developer Questions' started by carillpower, Jun 12, 2009.

  1. carillpower New Member

    Hi all guru's i got doubt here on how to change my code to sqlserver...could anyone teach me...i really2 appreciate it. This is my code in oracle ::
    create or replace procedure proc_A is
    cursor c1_cur is
    select to_char(sysdate,'yyyy') s_year,
    to_char(add_months(sysdate,-12),'yyyy') prev_year from dual;
    c1 c1_cur%rowtype;
    begin
    open c1_cur;
    fetch c1_cur into c1;
    execute immediate ' create table tblA (empid varchar2(30), hiredate'| s_year |' date, hiredate'| prev_year |' date) ';
    close c1_cur;
    end proc_A;
    carillpower, Jun 12, 2009
    #2

  2. FrankKalis Moderator

    Welcome to the forum!
    I'm not really familiar with PLSQL, so maybe it would help if you could describe briefly, what the code is doing. I guess I know, but just to make sure. [:)]
    FrankKalis, Jun 12, 2009
    #3

  3. carillpower New Member

    First of all...thanks for helping me :)
    Actually i want to create a procedure which will create a table that the column of the table changing each year....so no need for me to create a new table for upcoming year....example like now the table column would be HIREDATE2009...for next year it would be HIREDATE2010....for the next other year would be HIREDATE2011... :)
    carillpower, Jun 15, 2009
    #4

  4. FrankKalis Moderator

    [quote user="carillpower"]
    Actually i want to create a procedure which will create a table that the column of the table changing each year....so no need for me to create a new table for upcoming year....example like now the table column would be HIREDATE2009...for next year it would be HIREDATE2010....for the next other year would be HIREDATE2011... :)
    [/quote]
    Okay, I'm a little bit confused that you subtract 12 month from the current date and talk about next year, but that should be fairly easy to change in the code. [:)]
    Something like this should get you started:
    IF OBJECT_ID ('dbo.proc_A') IS NOT NULL
    DROP PROCEDURE dbo.proc_A;
    GO

    CREATE PROCEDURE dbo.proc_A
    AS
    SET NOCOUNT ON;

    DECLARE @CurrentYear int;
    DECLARE @ThisYear nchar(4);
    DECLARE @PreviousYear nchar(4);
    DECLARE @TheSQLString nvarchar(2000);

    SET @CurrentYear = YEAR (GETDATE());
    SET @ThisYear = CAST (@CurrentYear AS nchar(4));
    SET @PreviousYear = CAST (@CurrentYear - 1 AS nchar(4));
    SET @TheSQLString = 'CREATE TABLE dbo.tblA (empid varchar(30), hiredate' + @ThisYear + ' date, hiredate' + @PreviousYear + ' date)';

    --PRINT @TheSQLString;
    EXEC sp_executeSQL @TheSQLString

    GO
    EXEC dbo.proc_a;
    DROP PROCEDURE dbo.proc_A;

    For testing I would uncomment the PRINT line and comment out the EXEC sp_ExecuteSQL line. That way you just see what would be executed and you can check if everything in the string is just as you want it.
    FrankKalis, Jun 15, 2009
    #5

  5. carillpower New Member

    Thanks Frank, u really are MVP :D
    Now i got i little doubt here coz im using this code to execute a process which quite long...from what i've know is nvarchar only can store 4000 value of limit rite...the problem is my other code here got little more longer than that. Could u giv me some idea on how to solve this problem :S
    Thanks a lot!!
    carillpower, Jun 16, 2009
    #6

  6. FrankKalis Moderator

    That's just an "old habits die hard". [:)]
    It used to be a restriction in SQL Server 2000 with sp_ExecuteSQL. Starting with SQL Server 2005 you can use nvarchar(MAX) which gives you roughly 2 GB. Should be more than enough. [:)]
    FrankKalis, Jun 17, 2009
    #7

  7. carillpower New Member

    Thanks a lot for the info...really appreciate it..
    Yes we can use nvarchar(max) to make it happen but sadly i am using sql server 2000. So is there any other ways that could solve this :S
    carillpower, Jun 18, 2009
    #8

  8. FrankKalis Moderator

    Okay, in that case you're stuck with nvarchar(4000). However, then instead of using sp_ExecuteSQL you can use EXEC and concatenate multiple nvarchar(4000) variables. Have a look here: http://www.sommarskog.se/dynamic_sql.html on how to do that.
    FrankKalis, Jun 19, 2009
    #9

  9. carillpower New Member

    Thanks for the info...actually the code that i try to run is creating a view...when i try to run it...it shows no error at all...but when i execute the procedure its shows this error :
    Server: Msg 111, Level 15, State 1, Line 24
    'CREATE VIEW' must be the first statement in a query batch.
    May i know why this error occurs??
    Then i try print the code see is it shows all the code but its not...it only show some of the code...may i know is there any limitation bout this?? :S
    carillpower, Jun 21, 2009
    #10

  10. FrankKalis Moderator

    Chances are that a GO right in front of the CREATE VIEW statement will help you. Unless you're declaring und use variables in your Dynamic SQL string as well.
    FrankKalis, Jun 22, 2009
    #11

  11. carillpower New Member

    Thanks for sharing the info...i've tried it before but it's still shows the error...this is what i've did..plz correct me if im wrong ::
    .... SELECT @TheSQLString1 =
    'go CREATE VIEW DBO.UPDT2009_test
    AS
    SELECT i.ID, i.LASTNAME, i.FIRSTNAME, iMgr.ID AS MANAGERID, iMgr.LASTNAME AS MGRLASTNAME, iMgr.FIRSTNAME AS MGRFIRSTNAME, iHRMgr.ID AS HRID,
    iHRMgr.LASTNAME AS HRLASTNAME, iHRMgr.FIRSTNAME AS HRFIRSTNAME, sAsOf.GRADE AS BAND, ifte.FTE, ISNULL(e.PERFORMANCE, '+'NR'+')
    AS PERFRATING, e.POTENTIAL, ....
    Instead of that...i thought on another idea which juz altering the view column
    so i use this code ::
    exec sp_rename ' UPDT2009_test.hiredate2008','hiredate(should take @Thisyear)', 'column';
    but seems it can't be done...i tried to put it into declare part same like previous process but i got confius on ' symbol...
    do u have any suggestion for me...i would be appreciate it a lot...thanks !!
    carillpower, Jun 22, 2009
    #12

  12. FrankKalis Moderator

    Aah, I think I misunderstood your last question.
    GO is no valid T-SQL command. It's used by client tools such as Query Analyzer or Management Studio and basically means that the current batch should be send to the server. So it has no meaning inside a Dynamic SQL string and thus causes an error. As far as I know there is no way around it. Sorry if my answer has led to this confusion.
    Regarding your sp_rename issue: Are you now trying to rename a column in a view or in a base table?
    FrankKalis, Jun 22, 2009
    #13

Share This Page