Very dynamic sp to return single value 2

[kiwiCoder]

Hi there, I have been working on a stored procedure to return a value based on a number of variables, including table name. This was posted earlier. The advice recieved was great but as I only need a single value returned noy a recordset I thought I might ask more advice. heres the sp

CREATE PROCEDURE spGetThisTableField @ReturnVal as Integer output,
@strTableName varchar(50) , --Recipes
@strCompareFld as varchar(50), --RecipeKey
@strSearchValue as char(50), --9
@strFieldToReturn as varchar(50) output--PackCodeKey
--Select Recipes.packCodeKey from Recipes WHERE Recipes.RecipeKey = 9
AS
SET NOCOUNT ON
declare @SQLCommand nVarChar(200)
begin
Set @SQLCommand =('Select ' + @strFieldToReturn + ' from ' + @strTableName + ' WHERE ' + @strCompareFld + ' = ' + @strSearchValue)
end

--execute sp_executesql @SQLCommand
return @SQLCommand
GO
Rather than Return @SQLCommand I need to have @ReturnVal populated with the integer value from column @strFieldToReturn.

Regards

 

[nigelrivett]

declare @returnval int

Set @SQLCommand =('Select @returnval = ' + @strFieldToReturn + ' from ' + @strTableName + ' WHERE ' + @strCompareFld + ' = ' + @strSearchValue)

execute sp_executesql @SQLCommand, N'@returnval int out', @returnval out

return @returnval

see

http://www.nigelrivett.net/sp_executeSQL.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[kiwiCoder]

Thanks for that, I'll try it tonight when I get home. The only problem I see is not being able to use the "Return" parameter for error checking. If I could put the result into an output paramter(vairiable it would be perfect

 

[bertrandkis]

Hi kwikkiwi2,
I think a brief discussion about Dynamic execution and the usage of the return keyword is in order so as to help you code better.
#Firstly,the return keyword cannot return a value of datatype other than int, so writting return @SQLCommand is illegal and won't work.
#Secondly the return keyword is intended to Exits unconditionally from a query or procedure.It can a return nonzero value indicates success or failure of the procedure or query
#Thirdly if you want to catch the value returned by the return keyword from a client application, you will have to return it in the form of a recordset. This is an example of how you call a procedure that returns a value through the return keyword and catch the value:
Declare @foo int
Exec @foo=spGetThisTableField
select @foo as 'is the returned value'

Regarding dynamic execution, we have to understand that there are 2 batches involed when a dynamic sql query is executed using EXECUTE or sp_executesql.
The first batch is the batch in which the Execute or sp_executesql is called, the second is the batch that the Execute and sp_executesql creates to run the dynamic statement, and these 2 batches cannot see each other; that means the sp_executesql batch cannot reference variables declared in the batch calling it and the calling batch cannot reference variables declared in the sp_executesql batch. So no matter how you try sp_executesql cannot pass back the field value you want into your output variable @ReturnVal. But this is how the masters get around this problem:
Create a temporary table and send the value you want in the temp table and then read the value of the temp table into a variable. You can then return that value as an output parameter or using the return keyword(provided that it is an integer). Here is the code:In your stored procedure create a temp table:
Create table #temp(col1 int)
--Now capture the field value
Insert into #temp execute sp_executesql @SQLCommand
--Read the field value in a variable.
select @ReturnVal=col1 from #temp
--If you still want to use the return keyword do this
return @ReturnVal
Et Voila.
I hope I was clear enough.You are welcome should you need more help.
Enjoy.

 

[kiwiCoder]

Thankyou for such a clear and concise answer, I am awaiting some books on SQL Server so I hopefully wont have to pester you chaps too often. As a sidenote will creating a temp table then dropping be much of a ersource use, or is it adviseable to create a temp table in all databases I create just for this purpose. Then just delete the new value from temp after @ReturnVal has been populated. Although the db is not large I'm new to sql server and believe that its best to learn the right way first time.

Many regards

 

[bertrandkis]

Thanks friend,
It's always a pleasure to help, don't feel like you are posting crap. you see there are other things that you not learn from books,so I will encourage you to keep on posting your problems here. With regard to temp tables, this is the theory about them:
- If you create a temp table inside a stored procedure, you don't have to drop it because it's scope is limited to the stored procedure, so sql server will drop it when the execution of the stored procedure is complete.
-Temporary tables has a temporary existance, when created outside of a stored procedure,they last as long as the connection that created them is active, once the connection ends, they are automatically dropped. However you can explicitly drop it when you like.
So use then like variables.You see, in a programme you create a variables when you need it, the same goes for temp tables.
Enjoy

 

[nigelrivett]

What are you trying to do?
If you are trying to return a single value then using a temp table is a large overhead (and a complication). You would only use this if you wished to return a result set.
See my previous post which retuns a value from dynamic sql - it can be used for returning multiple parameters of any non-blob datatype.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[icrf]

[bumping old thread]

Just wanted to say thanks to nigelrivett, that was exactly what I was after. Just wanted one field back from one row, but I don't know which field until runtime.

Thanks, was the answer to a frustrating two days.

________________________________________
Andrew

 

[icrf]

And if you want a result set from a dynamic SQL statement but don't want temp tables, the same works for returning a cursor.

declare @ColName as varchar(20), @ColVal as varchar(50), @Zip as int, @sql as nvarchar(500)
set @ColName = 'Name'
set @Zip = 38505

set @sql = 'declare curCust cursor local for SELECT ' + @ColName + ' FROM tblCustomer '
set @sql = @sql + 'WHERE Zip = @Zip; open curCust; set @ret = curCust'

declare @cur as cursor
execute sp_executesql @sql, N'@Zip int, @ret cursor out', @Zip, @cur out

fetch next from @cur into @ColVal
while @@fetch_status = 0
begin
	print @ColVal
	fetch next from @cur into @ColVal
end
close @cur
deallocate @cur

________________________________________
Andrew