Result of dynamic query in variable
-
How can i get the result of any dynamic Query created in the varibale
Example:
Declare @Temp int
Set @Temp=Exec('Select 1')
Rohit -
use sp_executesql with an output parameter, like this:
declare @sql nvarchar(4000)
declare @params nvarchar(4000)
declare @name varchar(255)
select @sql = 'Select @name = name from sysobjects where id = 1'
select @params = '@name varchar(255) output'
EXECUTE sp_executesql @sql, @params, @name output
select @name
-
quote:
use sp_executesql with an output parameter, like this:declare @sql nvarchar(4000)
declare @params nvarchar(4000)
declare @name varchar(255)
select @sql = 'Select @name = name from sysobjects where id = 1'
select @params = '@name varchar(255) output'
EXECUTE sp_executesql @sql, @params, @name output
select @name
Thanks jxflagg this sorted out my problem
Rohit -
But Somehow i am again where i was before
when i create the function using same statements
Example:
Create Function fnTest123()
Returns varchar(255)
As
Begin
declare @sql nvarchar(4000)
declare @params nvarchar(4000)
declare @name varchar(255)
select @sql = 'Select @nm = name from sysobjects where id = 1'
select @params = '@nm varchar(255) output'
EXECUTE sp_executesql @sql, @params, @name output
return @name
End
When i Execute the function as
Select dbo.fnTest123() then this error messages comes
Server: Msg 557, Level 16, State 2, Procedure fnTest123, Line 10
Only functions and extended stored procedures can be executed from within a function.
what should be the resolution ????
Rohit -
That's correct, you can't use sp_executesql inside a UDF. Why do you need a function? You could wrape this code inside a stored procedure.
-
quote:
That's correct, you can't use sp_executesql inside a UDF. Why do you need a function? You could wrape this code inside a stored procedure.I need the function cause in need the results to be intergrated in the query itself
like
Select *,fnTest123(SomeParameter) Result From SomeTable
Rohit -
It seems like you could accomplish that without a function simply by using two queries. First execute your dynamic query, then execute your second query, using the local variable.
-
Let me correct myself
i need the result like this
Select *,fnTest123(SomeTable.SomeField) Result From SomeTable
Rohit -
anayone can help me to get that its urget
Rohit -
would something like this help?...
select * from OpenRowset('SQLOLEDB',
'Server=(local);Trusted_Connection=yes',
'Exec Master.dbo.sp_who')You can do this within a UDF.
Two Problems:
1. Not the fastest thing, so do not do it in a loop. You can JOIN however.
2. Parameters of OPENROWSET must be constants.
Also make sure you use the database name in qualifing the objects.
You can also use this as a workaraound for getting the current time within a UDF.
select * from OpenRowset('SQLOLEDB',
'Server=(local);Trusted_Connection=yes',
'Select GetDate() as Now')
Once you understand the BITs, all the pieces come together
-
Hello Thomas,
Thanks for your valued comments.
this solves my problem to some extent
agin i am not able to fix the problem completly because when i create function like i have given in the below code i get error
Server: Msg 170, Level 15, State 1, Procedure Test123, Line 11
Line 11: Incorrect syntax near '@SQL'.
by this i come to know this thing that "openrowset" doest not accept the parameters through varibales i dont know i am right or wrong but am not able to fix the problem again!
Example:
Create Function Test123()
Returns varchar(255)
As
Begin
declare @sql nvarchar(4000)
declare @params nvarchar(4000)
declare @name varchar(255)
select @sql = 'Select name from sysobjects where id = 1'
Set @name=(select * from
OpenRowset('SQLOLEDB','Server=(local);Trusted_Connection=yes',
@sql))
return @name
End
Rohit -
rohitkgupta, Did you ever get a solution?
Once you understand the BITs, all the pieces come together -
just i need dynamic exectution of query that i didnt get
Rohit
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply