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

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.

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 appreciatedHow about

CREATE TABLE #temp(Result vachar(8000))

INSERT INTO #temp(Result) EXEC(@.SQL)|||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)|||Your worried about performance, yet your using dynamic sql...hmmmm|||Exactly, using dynamic sql is a necessary evil in this case (long story), so i dont want to hit the system even more.|||I'm not sure exactly what your looking for...but here are three versions...

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|||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 :)|||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 .. 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 :)|||Is that vanilla or windows for workgroups?

and a meg...wow...

HUGE|||Just incase anybody is searching the forums for the same answer I was looking for the following stored procedure works :

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|||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

PIVOT ISSUE

I have the following table structure and would like to pivot the data
below. The table was comprised of raw data grouped to find the count.
count question choice question2 choice2
-- -- -- --
--
7 are you happy no are you yes
14 are you happy yes are you yes
6 are you happy yes are you no
15 are you happy no are you no
When I Pivot
Select * from TableAbove
Pivot
(
Sum(count)
FOR Choice2 IN
('YES', 'NO')
)
I get
2 lines
Question choice yes no
-- -- -- --
are you happy yes 14 null
are you happy yes null 6
are you happy no 7 null
are you happy no null 15
Any Ideas would be appreciatedOn 30 May 2006 14:02:31 -0700, Troutbum wrote:

>I have the following table structure and would like to pivot the data
>below. The table was comprised of raw data grouped to find the count.
>count question choice question2 choice2
>-- -- -- --
> --
>7 are you happy no are you yes
>14 are you happy yes are you yes
>6 are you happy yes are you no
>15 are you happy no are you no
>When I Pivot
>Select * from TableAbove
>Pivot
> (
> Sum(count)
> FOR Choice2 IN
> ('YES', 'NO')
> )
>I get
>2 lines
>Question choice yes no
>-- -- -- --
>are you happy yes 14 null
>are you happy yes null 6
>are you happy no 7 null
>are you happy no null 15
>
>Any Ideas would be appreciated
Hi Troutbum,
I don't really understand yoour question. Is the output above what you
get or what you want? If it is what you currently get, then how do you
want it? And if it's what you want, then please explain how you get at
this output from the input - to me, a two-row output would make much
more sense.
I tried to reproduce your problem - after correcting some errors in the
query, I didn't get the result you quote above:
CREATE TABLE TableAbove
(cnt int NOT NULL,
question varchar(15),
choice char(3),
question2 varchar(15),
choice2 char(3))
go
INSERT INTO TableAbove (cnt, question, choice, question2, choice2)
SELECT 7, 'are you happy', 'no', 'are you ', 'yes'
UNION ALL
SELECT 14, 'are you happy', 'yes', 'are you ', 'yes'
UNION ALL
SELECT 6, 'are you happy', 'yes', 'are you ', 'no'
UNION ALL
SELECT 15, 'are you happy', 'no', 'are you ', 'no'
go
Select * from TableAbove
Pivot
(
Sum(cnt)
FOR choice2 IN
([yes], [no])
) AS p
go
DROP TABLE TableAbove
go
Result:
question choice question2 yes no
-- -- -- -- --
are you happy no are you 7 15
are you happy yes are you 14 6
Hugo Kornelis, SQL Server MVP