| Author |
Topic |
|
deepakl_2000
Starting Member
8 Posts |
 Posted - 2009-11-11 : 10:55:20
|
Hi Forum Memebers,
I have the below issue.
@Group_Name=ABC
SET @ComplexitySQL ='SELECT Complexity FROM LookUp_Table WHERE Group_Name='''+@Group_Name+''''
EXEC @ComplexitySQL --- WORKS FINE AND DISPLAYS THE SQL.
--REQUIREMENT --> I need the return VALUE OF THIS EXECUTED SQL
----------------------------
Requirement:
----------------------------
1 Issue > It displays the @ComplexitySQL whiich is fine but i need to
Extract the Complexity from Above table --> FOR EXAMPLE:'Average'
I dont know how to Extract the result of EXEC()....Please help
2 Issue > Once I get the return Value from above executed Sql statement.i,e
Assuming i get the @RETURN_VALUE =AVERAGE
I need to do a
SELECT @RETURN_VALUE from SOMEOTHERTABLE WHERE CONDITION=@CONDITION which should be equivalent to
SELECT AVERAGE from SOMEOTHERTABLE WHERE CONDITION=@CONDITION
--->How to Accomplish this....Please help me Please.....
Hope you got what im asking.....
Please help me to achieve this in MY SQL SERVER 2000
Thanks and Regards
Deepak Lal |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-11 : 11:42:26
|
not sure why you need dynamic sql for this? Why not:
select complexity from lookup_table where group_name = @group_Name
or assuming one value is returned from that statement:
declare @return_val <complexity's datatype>
select @return_val = complexity from lookup_table where group_name = @group_Name
Be One with the Optimizer
TG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-11 : 12:25:54
|
| and if you really want to use dynamic sql due to any complex scenario which you not specified, you need to use sp_executesql rather than EXEC to return variable values through dynamic sql. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-11 : 12:27:43
|
| http://msdn.microsoft.com/en-us/library/ms188001.aspx |
|
|
|
deepakl_2000
Starting Member
8 Posts |
Posted - 2009-11-11 : 13:25:26
|
Hi All,
declare @return_val <complexity's datatype>
select @return_val = complexity from lookup_table where group_name = @group_Name
PRINT @return_val --prints the SQL STATEMENT BUT NOT THE RETURN VALUE
I have done this before as i have told you in my original post but it is printing the SQL STATEMENT QUERY instead of the return value.
Can you tell me how to achieve it.??
sp_executesql will not work either because i do not want to hardcode the value in my program.
I need the value to be passed as a dynamic parameter to my select statement which will return a Value.
Further i need te below to happen
2 Issue > Once I get the return Value from above executed Sql statement.i,e
Assuming i get the @RETURN_VALUE =AVERAGE
I need to do a
SELECT @RETURN_VALUE from SOMEOTHERTABLE WHERE CONDITION=@CONDITION
which should be equivalent to
SELECT AVERAGE from SOMEOTHERTABLE WHERE CONDITION=@CONDITION
How should i achieve this issue?
please help me guysss....
When the Going gets Tougher,The Tougher gets Going. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-11 : 13:45:21
|
So your [Complexity] column (for that row) contains the value 'AVERAGE' and the table [SOMEOTHERTABLE] has a column called: [AVERAGE] ??
EDIT:
Well, whatever your requirements are take a look at this example and see if it helps:
create table #lookup_table (group_name varchar(10), complexity varchar(10))
create table #someothertable (average int, condition int)
insert #lookup_table values ('group1', 'AVERAGE')
insert #someothertable values (10, 1)
declare @group varchar(10)
,@condition int
,@sql nvarchar(200)
,@return_val int
select @group = 'group1'
,@condition = 1
select @sql = N'select @rv = ' + l.complexity + ' from #someothertable where condition = ' + convert(varchar(1), @condition)
from #lookup_table l
where group_name = @group
--print @sql
exec sp_executesql @sql, N'@rv int output', @rv = @return_val output
select @return_val [@return_val]
drop table #lookup_table
drop table #someothertable
OUTPUT:
@return_val
-----------
10
Be One with the Optimizer
TG |
|
|
|
deepakl_2000
Starting Member
8 Posts |
Posted - 2009-11-12 : 02:08:47
|
Hi TG,
Thanks for your valuable inputs i did as you have instructed but still im stuck with this issue....
Please need help on this..
im getting the below error.
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'
When the Going gets Tougher,The Tougher gets Going. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-12 : 10:19:02
|
Please notice the datatype of the parameters I declared. sp_exectutesql requires Nvarchar not varchar.
,@sql nvarchar(200)
Be One with the Optimizer
TG |
|
|
|
deepakl_2000
Starting Member
8 Posts |
Posted - 2009-11-14 : 05:07:04
|
Thanks a ton TG .,,,,,it worked....
 
When the Going gets Tougher,The Tougher gets Going. |
|
|
|
|
Need help on Return Value from EXEC()