Hi To all
This is nice article , i worked lot on Table Variable and know all it's + and -
there is a restriction on Table Variable that we cannot use EXEC and string query on table variable.
Can any one give me the right Alternative how i insert Dynamic query result into Table Variable . I don't Want to Use # Table or ## table.
my problem is like that.
DECLARE @SHASHI(PRIMARY_KEY INT NOT NULL, NAME VARCHAR(50))
DECLARE @SSQL AS STRING, @CHECK AS INT
SET @CHECK = 1
SET @SSQL = 'SELECT ID , NAME FROM MY_TABLE'
IF @CHECK = 1
SET @SSQL = @SSQL + 'WHERE ID >25'
SET @SSQL = @SSQL + 'WHERE ID <25'
INSERT INTO @SHASHI
----------------------HOW I USE THE DYNAMIC QUERY TO INSERT VALUES INTO MY @SHASHI TABLE. CAN ANY ONE EXPERT SOLVE MY PROBLEM WITHOUT USING TEMP TABLE........
THANKS IN ADVANCE........HOPPING URGENT REPLY.........
Hello All. First of all thank you Jambu for your article. Certainly got the yeah's and nay's going. A little experience I have had in the past on not so unrelated. Without going into too much detail, I had an issue where a stored procedure was being excessively recompiled. Did the usual analysis and ended up logging a call to MS. A order number and revision were passed to the SP and then updates took place. Because we are a very high OLTP site, MS got me to modify the sp, to DECLARE local variables in the sp and assign the order number and revision passed to the SP to these variables. The variables took the place of the parameters thruout the SP. And guess what, the excessive recompilations stopped. So just a little experience I had with local variables. Hope this might help one of you someday. Derek.
Nice article covering some basic points, but here are 2 important points to hightlight:
A lot of my work day-to-day is fining previously written procedures that are slow and re-writing them more to be more efficient. I often end up converting all the table variables to temp tables and making indexes on the columns that are used in JOINs or WHERE or GROUP BY clauses in subsequent queries. This is especially germane when the row sizes are large. When I bench mark and compare the two methods, I'd say that 75% of the time the #temp tables are considerable faster especially when number of rows are great (100,000 or higher)
However, in the case of user-defined functions you have no choice but to use a table variable.
yes, it is POSSIBLE, but only by having the declaration of the variable, population of the table variable, and usage of the table variable, ALL INSIDE THE DYNAMIC SQL.
not something you want to be doing, though.....
--psuedocodeSELECT myColumnFROM udtReturningSmallResultsetFromGroupByOverLargeNumberOfRecords (@myDateVariable)
--psuedocodeDECLARE @tblVariable TABLE(myColumn varchar(50) NOT NULL)INSERT @tblVariable(myColumn) SELECT myColumnFROM udtReturningSmallResultsetFromGroupByOverLargeNumberOfRecords (@myDateVariable)