Error executingDyaic Sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error executingDyaic Sql

Author

Topic

kka_anand
Starting Member

24 Posts

Posted - 2010-08-10 : 10:17:59

HiAll,

I have a dynamic Sql statement in an SP, when I run the SP it throws an error message :- Invalid object name '#temp_EMA_table'

If, I execute as a normal sql statement it works fine, where as in dymn-sql it throws an error.

Here is a sql statement
-----------------------
SELECT @Vr_C_SqlCommand = 'select A.naame, row_number() over (order by A.DAATE) n, CAST(null as decimal(8,2)) [emass] Into #temp_EMA_table from (select TOP 11 * from tablename_'+@PrmtType+' where naame = 'XYZ' and DAATE <= @PrmtDate) A; create clustered index ix_n on #temp_EMA_table(n)'
EXEC (@Vr_C_SqlCommand)

Regards
Anand

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-10 : 10:26:36

There is a lot of errors in you sql statement.
for example -
'XYZ' should be ''XYZ''
Table name with spaces is not accepted without brackets "[]".
did you declared @Prmtdate ?

Try to Print the command and Parse it and then execute.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

kka_anand
Starting Member

24 Posts

Posted - 2010-08-10 : 10:40:05

To make the query easier, i have truncated few paramenters. The query is correct and its executing fine. I have declared the paramaters correctly and the table name is dynamic and there is no space in between.

When I run the SP with a dynamic sql statement, it throws an error message :- Invalid object name '#temp_EMA_table'. I think the #temp_EMA_table was not recognized when it executes as a dynamic sql.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-10 : 10:48:59

Dynamically you are creating temporary table that is possible
look the below code working fine -

DECLARE @Vr_C_SqlCommand AS VARCHAR(MAX)
SELECT @Vr_C_SqlCommand = 'SELECT * INTO #temp_table FROM table'
EXEC ( @Vr_C_SqlCommand )
SELECT * FROM #temp_table

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

kka_anand
Starting Member

24 Posts

Posted - 2010-08-10 : 10:55:43

Yes, It suceedeed when I execute the SP.
If, I run the SP EXEC SP_EMAS, it thows an error msg

Msg 208, Level 16, State 0, Procedure SP_EMAS, Line 27
Invalid object name '#temp_table'.

kka_anand
Starting Member

24 Posts

Posted - 2010-08-15 : 11:49:47

Hi All,

This is the SP that creates problem. If you see the below SP, the commented sql statement works fine without any error until the table name has been passed as a parameter (@PrmType= 'REAL'). The parameter should concatenate with the table nane like this Srs_EDO_@PrmType. If I concatenate the parameter with the tabe name, it thows an error message - Invalid object name 'Srs_EDO_@PrmtMarketType'.

Then I convert the sql statement into dynamic sql statement which accepts the parameter and executed successfully. When I, run the SP using EXEC SP, it throws an error message Invalid object name '#temp_EMA_table'.

EXEC TestSP 'REAL'

ALTER PROCEDURE [dbo].[TestSP] (@PrmType varchar(10)) AS
DECLARE

@Vr_C_SqlCommand Varchar(2000)
BEGIN

SET NOCOUNT ON
BEGIN

-- select A.SCRIPT, row_number() over (order by A.DAATE) n, A.DAATE, CAST(null as decimal(8,2)) [EMAS] Into #temp_EMAS_table
-- from (select TOP 10 * from Srs_EDO_REAL where SCRIPT = 'AFSAL' and DAATE <= '10-JUL-2010' order by DAATE DESC) A
-- create clustered index ix_n on #temp_EMAS_table(n)

SELECT @Vr_C_SqlCommand = 'select A.SCRIPT, row_number() over (order by A.DAATE) n, A.DAATE, CAST(null as decimal(8,2)) [EMAS] Into #temp_EMAS_table from (select TOP 10 * from Srs_EDO_'+@PrmType+' where SCRIPT = ''AFSAL'' and DAATE <= ''10-jul-2010'' order by DAATE DESC) A; create clustered index ix_n on #temp_EMAS_table(n)'
EXEC (@Vr_C_SqlCommand)

select * from #temp_EMAS_table
DROP TABLE #temp_EMAS_table
END
END

Thanks in Advance!!!!!!

Regards
Anand

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 03:24:43

Yes this is the problem that if you creating temp table with the help of dynamic query it parsed and even execute but it does not exist anywhere.

For this you need to do one thing. First create temp table and then insert into that table.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 05:14:43

Or just try this as I found something recently.

Use global temporary table instead local temp table.
for that use prefix ## instead of # with the temp table name.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

kka_anand
Starting Member

24 Posts

Posted - 2010-08-16 : 15:07:31

Hi Vaibhav

I have prefixed ## instead of # with the temp table name. It is working.
Can you tell me why, the below query is erroring. How to suffix the table name using parameter?

@PrmtMarketType ='REAL'
select TOP 10 * from Srs_EDO_@PrmtType

Regards
Anand

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-17 : 02:49:06

quote:
Originally posted by kka_anand

Hi Vaibhav

I have prefixed ## instead of # with the temp table name. It is working.
Can you tell me why, the below query is erroring. How to suffix the table name using parameter?

@PrmtMarketType ='REAL'
select TOP 10 * from Srs_EDO_@PrmtType

Regards
Anand

"If I concatenate the parameter with the tabe name, it thows an error message - Invalid object name 'Srs_EDO_@PrmtMarketType'.

Then I convert the sql statement into dynamic sql statement which accepts the parameter and executed successfully."

this is your reply.

So you have to go for dynamic sql for that.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 04:40:44

"Use global temporary table instead local temp table"

Beware of two users running the Sproc at the same time. You can give the table some "unique name" to avoid that - e.g. converting a GUID to Varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources