Results 1 to 5 of 5

Thread: Use @var

  1. #1
    Join Date
    Mar 2003
    Posts
    6

    Lightbulb Unanswered: Use @var

    Hi all,

    I just post here the script I try to use and the error I get.
    Is anyone has an idea, I would greatly appreciate.

    Thanks

    Gauthier

    ------------------------------------------
    declare @name sysname

    DECLARE rowcountintables CURSOR
    for
    select [name] from sysdatabases

    OPEN rowcountintables
    fetch next from rowcountintables into @name
    while @@fetch_status = 0
    begin

    Use @name
    SELECT * from sysusers

    fetch next from rowcountintables into @name
    end

    DEALLOCATE rowcountintables
    GO
    ----------------------

    Error:
    Server: Msg 170, Level 15, State 1, Line 11
    Line 11: Incorrect syntax near '@name'.

    Is it possible to use a Variable in the USE command?

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    declare @name sysname
    declare @sql nvarchar(200)
    DECLARE rowcountintables CURSOR
    for
    select [name] from sysdatabases

    OPEN rowcountintables
    fetch next from rowcountintables into @name
    while @@fetch_status = 0
    begin
    print @name
    set @sql=N'use '+@name
    execute sp_executesql @sql
    SELECT * from sysusers

    fetch next from rowcountintables into @name
    end

    DEALLOCATE rowcountintables
    GO

  3. #3
    Join Date
    Mar 2003
    Posts
    6
    Thanks but it's still not working, it always returns me the Master infos.


    Originally posted by harshal_in
    declare @name sysname
    declare @sql nvarchar(200)
    DECLARE rowcountintables CURSOR
    for
    select [name] from sysdatabases

    OPEN rowcountintables
    fetch next from rowcountintables into @name
    while @@fetch_status = 0
    begin
    print @name
    set @sql=N'use '+@name
    execute sp_executesql @sql
    SELECT * from sysusers

    fetch next from rowcountintables into @name
    end

    DEALLOCATE rowcountintables
    GO

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    try:

    Code:
    declare @name sysname
    declare @sql nvarchar(100)
    
    DECLARE rowcountintables CURSOR for select [name] from sysdatabases 
    
    OPEN rowcountintables
    fetch next from rowcountintables into @name
    
    while @@fetch_status = 0 begin
      raiserror('DB: %s',0,1,@name)
    
      execute( N'SELECT * from ' + @name + '..sysusers')
    
      fetch next from rowcountintables into @name
    end
    
    DEALLOCATE rowcountintables
    GO
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Mar 2003
    Posts
    6
    It worked, well done!
    Thanks


    Originally posted by Paul Young
    try:

    Code:
    declare @name sysname
    declare @sql nvarchar(100)
    
    DECLARE rowcountintables CURSOR for select [name] from sysdatabases 
    
    OPEN rowcountintables
    fetch next from rowcountintables into @name
    
    while @@fetch_status = 0 begin
      raiserror('DB: %s',0,1,@name)
    
      execute( N'SELECT * from ' + @name + '..sysusers')
    
      fetch next from rowcountintables into @name
    end
    
    DEALLOCATE rowcountintables
    GO

Posting Permissions

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