November 1, 2012 at 10:30 pm
set @STR = 'insert into #temp select GenericVacancyTitleInternalID, GenericVacancyTitleName, NULL ParentInternalID from ReferenceData.TB_GenericVacancyTitle gvt inner join Config.TB_Contract co on gvt.GenericVacancyTitleContractID = co.ContractInternalID where co.ContractExternalID = ''' + @ContractGUID + ''' and co.ContractStatusID = 1 and gvt.GenericVacancyTitleStatusID = 1 and GenericVacancyTitleLanguageID = ' + ltrim(rtrim(str(@LanguageInternalID))) + ' and GenericVacancyTitleName like ''%' + @FilterString + '%'' Order by GenericVacancyTitleLanguageID, ' + str(isnull(@SortFieldIndex,1)) +' ' + @OrderBy
exec @STR
November 1, 2012 at 10:48 pm
Instead of using INSERT INTO #tmp SELECT... FROM....., use SELECT...INTO #tmp FROM....
Is there a reason you are using dynamic SQL instead of compiled stored procedure?
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
November 1, 2012 at 10:55 pm
28.kanikasoni (11/1/2012)
set @STR = 'insert into #temp select GenericVacancyTitleInternalID, GenericVacancyTitleName, NULL ParentInternalID from ReferenceData.TB_GenericVacancyTitle gvt inner join Config.TB_Contract co on gvt.GenericVacancyTitleContractID = co.ContractInternalID where co.ContractExternalID = ''' + @ContractGUID + ''' and co.ContractStatusID = 1 and gvt.GenericVacancyTitleStatusID = 1 and GenericVacancyTitleLanguageID = ' + ltrim(rtrim(str(@LanguageInternalID))) + ' and GenericVacancyTitleName like ''%' + @FilterString + '%'' Order by GenericVacancyTitleLanguageID, ' + str(isnull(@SortFieldIndex,1)) +' ' + @OrderByexec @STR
Please post the ddl first ; also , the variables values that you have been using in between.
that is the good way to getting an answer ...
Either after NULL there is no delimiter or it's an alias.. see that is why you need to post the ddl..
also, what's the error message you are getting ???
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
November 1, 2012 at 10:57 pm
SQLMickey (11/1/2012)
Instead of using INSERT INTO #tmp SELECT... FROM....., use SELECT...INTO #tmp FROM....Is there a reason you are using dynamic SQL instead of compiled stored procedure?
I don't think it will work ,
the life of temps won't be outside the variables.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
November 1, 2012 at 11:09 pm
The temp table's scope is connected to the session, not the variable. After he uses exec @STR, he can select out of #temp. A table variable would not work because it can't be passed to the exec function.
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
November 1, 2012 at 11:36 pm
I am getting following errors
first time execution gives this error
Batch execution is terminated because of debugger request
second time execution gives this error
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe
third time it gives output
November 1, 2012 at 11:37 pm
is there any error ?
if yes please post error
and also please give purpose of doing this
2 thing which i have found
1)use exec(@str) instead of exec @STR
2)you are just inserting data into #temp not fetching data from it after exec(@str) you will not going to find #temp table as it will be lost after execution.
November 2, 2012 at 12:35 am
SQLMickey (11/1/2012)
The temp table's scope is connected to the session, not the variable. After he uses exec @STR, he can select out of #temp. A table variable would not work because it can't be passed to the exec function.
May be I understood wrong ;
What I meant is, that , If temp table is created inside variable , then after execution there won't be a temp table to select the data from.
So, "Select Into" inside the varable statement is no good ; since the temp table is created to be used afterwards.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
November 2, 2012 at 12:37 am
28.kanikasoni (11/1/2012)
I am getting following errorsfirst time execution gives this error
Batch execution is terminated because of debugger request
second time execution gives this error
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe
third time it gives output
it's a connection error that comes in SSMS not that much to worry about...
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
November 2, 2012 at 2:36 am
SQLMickey (11/1/2012)
The temp table's scope is connected to the session, not the variable. After he uses exec @STR, he can select out of #temp. A table variable would not work because it can't be passed to the exec function.
about table variables :
-- this works but irrelevant; since variabel can't be used afterwards
declare @STR varchar(max)
set @STR = 'declare @tablevar as table (a int);insert into @tablevar select 1; select * from @tablevar; '
exec (@str)
----------------
--- this doen't
declare @STR varchar(max)
declare @tablevar as table (a int);
set @STR = insert into @tablevar select 1; select * from @tablevar; '
exec (@str)
-- for temp tables
-- frst one works but irrelevent
--second one works well for temp tables, since it goes with the user session
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
November 2, 2012 at 10:23 am
You are right demofox about the INTO #temp. I didn't have SQL Server with me when I posted last night so I couldn't test my code. I appologize. I'll try not to do that again. :blush: 😀
Here is what I should have posted the first time.
By adding the definition of temp table prior to the execution of the dynamic SQL, you will be able to retrieve the data out of the dynamically executed SQL. This won't work with table variables, but it does work with temp tables. Here is a working example:
DECLARE@STR AS varchar(max)
CREATE TABLE #temp
(
FirstName varchar(50)
,LastName varchar(50)
)
SET @STR = 'INSERT INTO #temp SELECT FirstName, LastName FROM Employee ORDER BY LastName'
EXEC (@str)
SELECT
FirstName
,LastName
FROM
#temp
DROP TABLE #temp
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply