is possible to do a dynamic query to a var table or temporal table ??
-
is possible to do a dynamic query to a var table or temporal table ??
i try that and it doent work
declare @query varchar(255)
declare @table table ( data varchar (255))
insert into @table values( 'hello')
set @query = ' select * from @tabla'
exec (@query)
--------------------------------
(1 row(s) affected)
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tabla'.
-
What you are seeing has to do with the scope of your table variable. This will work for sure if you use a global temporary table (##tempTable).
-
Yes i know but is for multiple sessions and if i use a global table, it will crash with the others sessions,and i dont want to create table into tempbd to avoid the blocks
i have sp that returns parameters , well those parameters I insert them into a variable table and I compare them with a dynamic query which is built with parameters from a physic table. several session use this sp because the global tables are not useful for that.
-
Then you must declare and populate your table variable within the scope of the dynamic sql call using sp_executeSQL and parameters. Look up sp_ExecuteSQL in BOL.
-
i will take a look to sp_ if you may give me a example i will be very owe, thank you
-
It is kind of difficult to give you a good example of this when I really do not know what you are trying to accomplish. Can you give us more detail about what you are trying to do from a 'big picture' standpoint? Maybe include the code you are working with, the table DDL for the tables you are working with (temporary and static), some sample data, and your expected results?
-
i am seeing the sp_executequery and i can to pass it a declared parameter but i dont know if i can to pass a parameter with values like a variable table with registers, is that possible?
declare @table table ( data varchar (50) )
insert into @table values( 'hello')
well now the @table has a value 'hello'
well how can i pass the var table @table to sp_executequery ???
-
You can't pass the table variable, you must include the entire DECLARE @table table....INSERT INTO @table.... code into sp_executeSQL so that the scope of the table variable declaration is withing the scope of the sp_executeSQL run.
-
if you can give me a example i will be grateful meanwhile i will see it thank you very much
-
Again, can you give a little more info on what you are trying to accomplish? Why do you think you need to use Dynamic SQL?
-
because is better than write code to evaluate the output variable of my system
example
sp_responsesclient @icode1 out put,
@icode2 output
well then i need to evaluate that parameters
if @icode1 in (2344,7888r8) and @icode not in (3443535,776)
begin
..
if @icode1 in (2344,7888r8) and @icode not in (3443535,776)
begin
..
so and so
well how i dont want write every condition into my sp because it will begin grow so much, i want to puts those conditions into a physic table and then i build a dynamic query
well the outputs parameters i put them into a variable table and that variable table i use it into dynamic query
insert into @Myvariabletable vaules (@iCode1,@iCode2)
set @query ' select @ok = 'true' from @Myvariabletable where icode1 ' + @physicparameter1 + ' icode2 ' + @physicparameter1
subsequently i must to evaluate the variable @ok
that is all
-
From what you've shown, there is still no reason why this needs to be dynamic. Why can't you simply use the query SELECT @ok = 'true' FROM @MyVariableTable WHERE .....?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply