Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: Placing sp_executesql data into a var

    I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :

    lookup_table1
    table1_id
    description

    lookup_table2
    table2_id
    description

    This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.

    Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.

    This procedure as it stands right now

    Code:
    CREATE PROCEDURE aida_lookup
    	@lookup_table varchar(255),
    	@lookup_id_name varchar(255),
    	@lookup_description_col varchar(255),
    	@lookup_value varchar(255)
    AS 
    
    declare @sql nvarchar(2048)
    
    --
    -- Check if the given lookup value exists in the give table / column
    --
    set @sql ='SELECT ' + @lookup_id_name + ' FROM ' + @lookup_table + ' WHERE ' + @lookup_description_col + ' = ''' + @lookup_value + ''''
    EXECUTE sp_executesql @sql
    if (@@rowcount = 0) goto new_value
    if (@@error <> 0)  goto on_error
    return(1)
    
    --
    -- Insert new lookup value into the given table / column
    --
    new_value:
    -- NEED TO CLEAR PREVIOUS NULL SELECT
    begin transaction
    set @sql = 'INSERT INTO ' + @lookup_table + ' (' + @lookup_description_col + ') VALUES (''' + @lookup_value + '''); SELECT SCOPE_IDENTITY()'
    execute sp_executesql @sql
    if (@@error <> 0)  goto on_error
    commit transaction
    return(1)
    
    --
    -- Error handler
    --
    on_error:
      rollback transaction
      return (0)
    GO
    The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.

    My attempted solution was to declare a @temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.

    Code:
    declare @temp int
    ...
    SET @temp = EXECUTE sp_executesql @SQL
    if (@@temp = null) goto new_value
    SELECT @temp
    return(1)
    Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciated
    Last edited by MrEyes; 01-27-04 at 13:04.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about


    CREATE TABLE #temp(Result vachar(8000))

    INSERT INTO #temp(Result) EXEC(@SQL)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    22
    If it is at all possible I would like to avoid using temporary tables, as if I can do this with SQL memory objects the performance hit would (I assume be much lower)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your worried about performance, yet your using dynamic sql...hmmmm
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    22
    Exactly, using dynamic sql is a necessary evil in this case (long story), so i dont want to hit the system even more.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure exactly what your looking for...but here are three versions...

    Code:
    DECLARE @sql nvarchar(4000), @x int, @y int, @z int
    
    PRINT 'Something that will work'
    SELECT @sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES'
    EXECUTE @y = sp_EXECuteSQL @sql
    SELECT @z = @@ERROR, @x = @@ROWCOUNT
    SELECT @x, @y, @z
    
    PRINT 'Something that will return 0 rows'
    SELECT @sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' + '''' + 'Brett' + ''''
    EXECUTE @y = sp_EXECuteSQL @sql
    SELECT @z = @@ERROR, @x = @@ROWCOUNT
    SELECT @x, @y, @z
    
    PRINT 'Something that will explode'
    SELECT @sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
    EXECUTE @y = sp_EXECuteSQL @sql
    SELECT @z = @@ERROR, @x = @@ROWCOUNT
    SELECT @x, @y, @z
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Brett Kaiser
    PRINT 'Something that will explode'
    SELECT @sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
    EXECUTE @y = sp_EXECuteSQL @sql
    SELECT @z = @@ERROR, @x = @@ROWCOUNT
    SELECT @x, @y, @z
    [/code]

    Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one
    I'll see if I can dig up an 8086 for you...I'm sure it'll be an improvement....

    Did you want 1 or 2 5 1/4 drives?

    And I think there's a huge 250k chip in there...


    Black and green are you ok, right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Brett .. no need to .... i ve already got a 8086 with 1 MB RAM ....
    dont bother yourself with the 256 K .. right now I am trying to get Windows 3.11 loaded on it .. help me out there if you can
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is that vanilla or windows for workgroups?

    and a meg...wow....

    HUGE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2004
    Posts
    22
    Just incase anybody is searching the forums for the same answer I was looking for the following stored procedure works :

    Code:
    CREATE PROCEDURE aida_lookup
                @lookup_table varchar(255),
                @lookup_id_name varchar(255),
                @lookup_description_col varchar(255),
                @lookup_value varchar(255)
    AS
    
    declare @procedure_sql nvarchar(2048)
    
    set @procedure_sql = '
    
    declare @sql nvarchar(2048)
    
    set @sql = (SELECT ' + @lookup_id_name + ' FROM ' + @lookup_table + ' WHERE ' + @lookup_description_col + ' = ''' + @lookup_value +''')
    
    if (@sql = null)
    begin
    	begin transaction
    	INSERT INTO ' + @lookup_table + ' (' + @lookup_description_col + ') VALUES (''' + @lookup_value + ''')
    	SELECT SCOPE_IDENTITY()
    	if (@@error <> 0)  goto on_error
    	commit transaction
    	return
    
    	on_error:
    	rollback transaction
    	return
    end
    else
    begin
    	SELECT @sql
    end
    return'
    print @procedure_sql;
    
    EXEC( @procedure_sql )
    return(1)
    GO

  12. #12
    Join Date
    Nov 2003
    Posts
    94
    You can return values from executesql by OUTPUT semantics on fully declared parameters:

    declare @sql nvarchar(4000)
    declare @params nvarchar(4000)

    set @sql = N'select @OUT=@IN * 2'

    set @params = N'@in int, @out int OUTPUT'

    declare @in int
    declare @out int

    set @in = 4

    exec sp_executesql @sql, @params, @in, @out OUTPUT

    select @out

    =8

Posting Permissions

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