Results 1 to 2 of 2

Thread: Dyanmic SQL in Stored Proc

  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Dyanmic SQL in Stored Proc

    I am trying to get a return value from dynamically created SQL statement in a stored procedure so that the stored procedure can determine how to proceed. Any ideas on how to do this??

    EXAMPLE CODE:
    --------------------

    CREATE PROCEDURE st_addHistory (@record_id VARCHAR(50))AS
    DECLARE @strSQL VARCHAR(1024)
    SELECT @strSQL = 'SELECT somefield FROM tblSomething WHERE id = ' + @record_id
    EXEC(@strSQL)

    /*I need the Value from [somefield] here so I can see if it has a value*/
    If somefield = 'Test1' DO THIS
    ...do some other stuff
    GO

    --------------------
    END CODE


    Thanks for any help,
    Jake

  2. #2
    Join Date
    Nov 2002
    Posts
    4
    Jake,
    Perhaps you need to approach the problem from a different angle?:

    It looks like the only part of the sql that is dynamic is the record_id. If this is true then maybe make the sp look like:

    ---------- Sample Pseudo Code – Start
    CREATE PROCEDURE st_addHistory (@record_id VARCHAR(50)) AS
    -- Validate @record_id so that sp does not break ...

    -- Declare and Initialise a variable for output of somefield
    declare @ouptutvar as somefieldtype
    set @ouptutvar = ‘’

    -- do ‘dynamic sql’
    select @ouptutvar
    from tblSomeTable
    where id = @record_id

    -- use resultant output value
    if @ouptutvar = <something1>
    begin
    <do something>
    end
    if @ouptutvar = <something2>
    begin
    <do something2>
    end
    ---------- Sample Pseudo Code - End

    Cheers sduggan …

Posting Permissions

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