Results 1 to 4 of 4

Thread: Dyanamic Sql

  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Dyanamic Sql

    Hi I was building a dynamic sql with output please tell me where I am wrong.
    Or Tell me right way to do it

    My table name is variable (Stock name)
    I want close price for given Stock

    CREATE PROCEDURE SP_GET_CLOSE
    @TableName as varchar(50),
    @close_price as varchar(50) output

    AS
    Declare @SQL NVarChar(1000)
    Declare @high varchar(50)
    SET @SQL = 'Select top 1 @close_price = [close] from '+ @TableName+ ' order by trade_date desc'

    print @SQL
    Exec sp_executesql @SQL, N'@close_price varchar(50) output' , @close_price
    GO

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Waht abotu this idea?

    CREATE PROCEDURE SP_GET_CLOSE
    @TableName as varchar(50),
    @close_price as varchar(50) output

    AS
    Declare @SQL NVarChar(1000)
    Declare @high varchar(50)
    SET @SQL = 'Select top 1 [close] from '+ @TableName+ ' order by trade_date desc'

    create table #tmp(ret varchar(450))

    insert #tmp
    exec(@SQL)

    select @close_price=cast(ret as money) from #tmp

  3. #3
    Join Date
    Dec 2003
    Posts
    3

    RE

    Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
    Besides I am having lot of similar quiries in mind for my project


    Thanks

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: RE

    Originally posted by shriya
    Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
    Besides I am having lot of similar quiries in mind for my project


    Thanks
    Temporary table will dropped after procedure execution. BTW, sql server is using temporary objects during any operations (even like select from with order by) so do not worry about this at all.

Posting Permissions

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