how to execute an a NVARCHAR variable in a Stored procedure ?

Question

User-2032370290 posted

Hi fellows

I have a query in a NVARCHAR  variable I want to execute it , It supposed to create a "Table  Variable"  ,it is like this :

"DECLARE @ProductTotals TABLE <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

([SideTab]NvarChar(250),[Jun  1 2007 12:00AM] int,[Jun  2 2007 12:00AM] int,[Jun  3 2007 12:00AM] int,[Jun  4 2007 12:00AM] int,[Jun  5 2007 12:00AM] int,[Jun  6 2007 12:00AM] int,[Jun  7 2007 12:00AM] int,[Jun  8 2007 12:00AM] int,[Jun  9 2007 12:00AM] int,[Jun 10 2007 12:00AM] int,[Jun 11 2007 12:00AM] int,[Jun 12 2007 12:00AM] int,[Jun 13 2007 12:00AM] int,[Jun 14 2007 12:00AM] int,[Jun 15 2007 12:00AM] int,[Jun 16 2007 12:00AM] int,[Jun 17 2007 12:00AM] int,[Jun 18 2007 12:00AM] int,[Jun 19 2007 12:00AM] int,[Jun 20 2007 12:00AM] int,[Jun 21 2007 12:00AM] int,[Jun 22 2007 12:00AM] int,[Jun 23 2007 12:00AM] int,[Jun 24 2007 12:00AM] int)"

<o:p> </o:p>

And  it is stored in a variable  say  @nvcVar.

<o:p> </o:p>

I want to execute it

I did :

EXECUTE     @nvcVar

But it gives me this error :

Msg 203, Level 16, State 2, Procedure proc_Report_DailyReport_Karkard, Line 60<o:p></o:p>The name 'DECLARE @ProductTotals TABLE ([SideTab]NvarChar(250),[Jun  1 2007 12:00AM] int,[Jun  2 2007 12:00AM] int,[Jun  3 2007 12:00AM] int,[Jun  4 2007 12:00AM] int,[Jun  5 2007 12:00AM] int,[Jun  6 2007 12:00AM] int,[Jun  7 2007 12:00AM] int,[Jun  8 2007 12:00AM] int,[Jun  9 2007 12:00AM] int,[Jun 10 2007 12:00AM] int,[Jun 11 2007 12:00AM] int,[Jun 12 2007 12:00AM] int,[Jun 13 2007 12:00AM] int,[Jun 14 2007 12:00AM] int,[Jun 15 2007 12:00AM] int,[Jun 16 2007 12:00AM] int,[Jun 17 2007 12:00AM] int,[Jun 18 2007 12:00AM] int,[Jun 19 2007 12:00AM] int,[Jun 20 2007 12:00AM] int,[Jun 21 2007 12:00AM] int,[Jun 22 2007 12:00AM] int,[Jun 23 2007 12:00A' is not a valid identifier.<o:p></o:p><o:p> </o:p><o:p> </o:p>

What is the problem ?!

 

Thank you in advance<o:p> ,</o:p>

 

Answer 1

User-319574463 posted

 Change the

EXECUTE     @nvcVar
to

EXECUTE     (@nvcVar)

 

The key to this is the brackets that forces evaluation of the variable. 

Answer 2

User-2032370290 posted

 thank you the problem solved but another problem came up,

How can I use that table variable ?!

For example I want to insert something in it !?

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

 

Answer 3

User-319574463 posted

 You can select from the table variable!

You can insert rows of data into the table variable. 

Answer 4

User-2032370290 posted
Yes, of course it is possible to insert and select a normal table variable but in this case because the table variable (@ProductTotals ) has not been offcially defined so when you try to use it like :<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>INSERT INTO @ProductTotals<o:p></o:p>

      EXEC MyStoredProcedure

It gives you the error below :

Msg 1087, Level 15, State 2, Procedure proc_Report_DailyReport_Karkard, Line 62<o:p></o:p>Must declare the table variable "@ProductTotals".<o:p></o:p> <o:p></o:p>

 

Answer 5

User-319574463 posted

 In which case you will revert to using a temporary table (possibly ## instead of #) - this should get over your referencing problem.

BOL states "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table. "

Answer 6

User-2032370290 posted

thank you very much !

I Replaced   "DECLARE @ProductTotals TABLE " with "CREATE TABLE ##ProductTotals" and it workd but whats the difference between # and ## ? why it dosen't work with # as well ?  

Answer 7

User-319574463 posted

 The single # form is visible to only one process. The ## is visible to all processes (and can be read by all). Thus although you have got your S.p. to work you will need to consider how to make it multi-user - possibly by including the current process id.

Answer 8

User-1130705846 posted

USE [NorthwindNET]
GO

ALTER procedure [dbo].[Test_sp]
(
@ID int
)
as 
set nocount on

declare @statement nvarchar(200)

set @statement=N'
declare @tmp table
(
ID int,
Name nvarchar(15)
)

insert @tmp
select ID,Name from dbo.Category where 1=1
'
if(@ID is not null)
begin
 set @statement=@statement+N' and ID>='+cast(@ID as nvarchar(2))
end

set @statement=@statement+N' select * from @tmp'

print @statement
exec sp_executesql @statement