Professional Web Applications Themes

Need help about stored procedures - Microsoft SQL / MS SQL Server

Hi all. I want to send table name to stored procedure and use this tablename in select into query to be able to select some values into this table.But this table isnt created yet and gonna be created by procedure.I tried this proc code but not worked: --------- CREATE PROCEDURE testproc type varchar(14),table_name varchar(50) AS print table_name execute('select * from temp_table into '+table_name) ---------- but this gives error when calling procedure: ---------------- temp_test Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'into'. -------------------- Call Syntax: ----------------------- EXECUTE oneoffproc type='TEST',table_name='temp_test' ----------------------- Thanks for your help....

  1. #1

    Default Need help about stored procedures

    Hi all.
    I want to send table name to stored procedure and use this tablename in
    select into query to be able to select some values into this table.But this
    table isnt created yet and gonna be created by procedure.I tried this proc
    code but not worked:


    ---------
    CREATE PROCEDURE testproc type varchar(14),table_name varchar(50) AS

    print table_name
    execute('select * from temp_table into '+table_name)
    ----------
    but this gives error when calling procedure:

    ----------------
    temp_test
    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'into'.
    --------------------

    Call Syntax:
    -----------------------
    EXECUTE oneoffproc type='TEST',table_name='temp_test'
    -----------------------

    Thanks for your help.


    Tahir Guest

  2. #2

    Default Re: Need help about stored procedures

    The correct syntax should be

    execute('select * into ' +table_name + ' from temp_table')

    --
    -Vishal

    "Tahir Emre KALAYCI" <net> wrote in message
    news:phx.gbl... 
    this 
    proc 


    Vishal Guest

  3. #3

    Default Re: Need help about stored procedures

    Tahir,

    INTO <table_name> comes right before the FROM clause, e.g.,

    SELECT * INTO T2 FROM T1

    --
    BG, SQL Server MVP
    Solid Quality Learning
    www.solidqualitylearning.com


    "Tahir Emre KALAYCI" <net> wrote in message news:phx.gbl... 
    Itzik Guest

  4. #4

    Default Re: Need help about stored procedures

    The correct syntax for SELECT INTO is:
    SELECT <column list> INTO <new table> FROM <old table>

    So you code should be:
    CREATE PROCEDURE testproc type varchar(14),table_name varchar(50) AS

    print table_name
    execute('select * into' + table_name + ' from temp_table')

    Table names btw are of datatype sysname, which is equivalent to
    nvarchar(128). Use this instead of varchar(50) and you won't get nasty
    surprises when someone tries to create a table with a perfectly legal,
    although somewhat long name.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Tahir Emre KALAYCI" <net> wrote in message
    news:phx.gbl... 
    this 
    proc 


    Jacco Guest

  5. #5

    Default Re: Need help about stored procedures

    Oh thanks.i didnt see this syntax error.thanks.
    "Itzik Ben-Gan" <solidqualitylearning.com> wrote in message
    news:phx.gbl...
    Tahir,

    INTO <table_name> comes right before the FROM clause, e.g.,

    SELECT * INTO T2 FROM T1

    --
    BG, SQL Server MVP
    Solid Quality Learning
    www.solidqualitylearning.com


    "Tahir Emre KALAYCI" <net> wrote in message
    news:phx.gbl... 
    this 
    proc 


    Tahir Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. Stored Procedures
    By Vladi in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: August 13th, 01:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139