Post reply

T-SQL selecting into variable

  • October 18, 2005 at 8:49 am

    #91833

    I'm having the hardest time trying to take a select statement and dumping the result into a variable.  The only way I can get this to work is to dump the result into a temp table.  This is what I have working:

    DECLARE @SQLStatement varchar(255)

    create table #t1 (bytesperpage int, pagesperMB int)

    select @SQLStatement = 'select low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

    insert into #t1 (bytesperpage) EXEC(@SQLStatement)

     

    But this is what I'd like to use:

    DECLARE @SQLStatement varchar(255)

    DECLARE @myvar int

    select @SQLStatement = 'select low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

    set @myvar = EXEC(@SQLStatement)

    When I do this I get the error Incorrect syntax near the keyword 'EXEC'

    Any help would be much appreciated.

    Thanks!

  • October 18, 2005 at 9:03 am

    #597957

    you cannot put a recordset into a parameter.

    try sp_execute_Sql

    select @SQLStatement = 'select @Myvar = low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

    exec sp_executeSql @SqlStatement, N'@Myvar int output', @Myvar OUTPUT

  • October 18, 2005 at 9:03 am

    #597956

    The statement you are trying to run returns a recordset and... you are trying to save a bunch of records in an integer variable?


    * Noel

  • October 18, 2005 at 9:26 am

    #597971

    Thanks for your help.  I've never used sp_executeSql.  I no longer receive errors, but my variable comes back null.  This is what I have:

    DECLARE @SQLStatement2 NVARCHAR(255)

    DECLARE @Myvar int

    select @SQLStatement2 = 'select low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

    exec sp_executeSql @SQLStatement2, N'@Myvar int output', @Myvar OUTPUT

    select @Myvar as MYVAR

    It does display the recordset, which is just one row with one field...but won't put that into @Myvar.

    Thanks again!

     

  • October 18, 2005 at 9:42 am

    #597979

    DECLARE @SQLStatement2 NVARCHAR(255)

    DECLARE @Myvar int

    select @SQLStatement2 = 'select @Myvar = low FROM [' + @servername + '].master.dbo.spt_values WHERE number = 1 and type = ''E'''

    exec sp_executeSql @SQLStatement2, N'@Myvar int output', @Myvar OUTPUT

    select @Myvar as MYVAR

     


    * Noel

  • October 18, 2005 at 9:52 am

    #597984

    That did it.  Thanks so much for your help!

    -Chris

Viewing 6 posts - 1 through 5 (of 5 total)

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