Results 1 to 4 of 4

Thread: create proc

  1. #1
    Join Date
    Nov 2003
    Posts
    42

    Unanswered: create proc

    1>create proc top @tablename varchar(30),@pm int
    2>as
    3>set rowcount @pm
    4>select * from @tablename
    5>set rowcount 0
    wrong message:line 4:incorrect syntax near '@tablename'
    what's the reason??????????

  2. #2
    Join Date
    Sep 2003
    Posts
    17
    Hi,

    That doesn't work, because you can't use variables for tablenames in T-SQL. If you need this sort of query, you need to use dynamic sql.

    1> create proc top @tablename varchar(30),@pm int
    2> as
    3> declare @statement varchar(255)
    4>
    5> set rowcount @pm
    6> select @statement = "select * from " + @tablename
    7> exec (@statement)
    8>
    9> set rowcount 0
    10>
    11> go

    1> exec top sysobjects, 1
    2> go
    name id uid type userstat sysstat indexdel schemacnt sysstat2 crdate expdate deltrig instrig updtrig
    seltrig ckfirst cache audflags objspare versionts loginame
    ------------------------------ ----------- ------ ---- -------- ------- -------- --------- ----------- -------------------------- -------------------------- ----------- ----------- -----------
    ----------- ----------- ------ ----------- ----------- -------------------------- ------------------------------
    sysobjects 1 1 S 0 113 0 3 0 Jan 1 1900 12:00AM Jan 1 1900 12:00AM 0 0 0
    0 0 0 NULL 0 NULL NULL

    (1 row affected)
    (return status = 0)

    Regards,
    Ulrike

  3. #3
    Join Date
    Nov 2003
    Posts
    42
    1>create proc top @tablename varchar(30),@pm int
    2>as
    3>declare @statement varchar(200)
    4>set rowcount @pm
    5>select @statement="select * from"+@tablename
    6>exec(@statement)
    7>set rowcount 0
    8>go
    1> top test,1
    wrong message£ºline 1 :incorrect syntax near 'fromtest'
    why£¿£¿£¿£¿

  4. #4
    Join Date
    Nov 2003
    Posts
    42
    Originally posted by sybase_user2003
    Hi,

    That doesn't work, because you can't use variables for tablenames in T-SQL. If you need this sort of query, you need to use dynamic sql.

    1> create proc top @tablename varchar(30),@pm int
    2> as
    3> declare @statement varchar(255)
    4>
    5> set rowcount @pm
    6> select @statement = "select * from " + @tablename
    7> exec (@statement)
    8>
    9> set rowcount 0
    10>
    11> go

    1> exec top sysobjects, 1
    2> go
    name id uid type userstat sysstat indexdel schemacnt sysstat2 crdate expdate deltrig instrig updtrig
    seltrig ckfirst cache audflags objspare versionts loginame
    ------------------------------ ----------- ------ ---- -------- ------- -------- --------- ----------- -------------------------- -------------------------- ----------- ----------- -----------
    ----------- ----------- ------ ----------- ----------- -------------------------- ------------------------------
    sysobjects 1 1 S 0 113 0 3 0 Jan 1 1900 12:00AM Jan 1 1900 12:00AM 0 0 0
    0 0 0 NULL 0 NULL NULL

    (1 row affected)
    (return status = 0)

    Regards,
    Ulrike
    oh,i get it,there is a space behind "from",yes,it does work,thanks a lot,

Posting Permissions

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