locked
how to execute an a NVARCHAR variable in a Stored procedure ? RRS feed

  • 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>

     

    Saturday, September 1, 2007 4:35 AM

Answers

  • 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. "

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 2, 2007 4:18 AM

All replies

  • User-319574463 posted

     Change the

    EXECUTE     @nvcVar
    to

    EXECUTE     (@nvcVar)

     

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


    Saturday, September 1, 2007 6:27 AM
  • 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>

     

    Saturday, September 1, 2007 7:24 AM
  • User-319574463 posted

     You can select from the table variable!

    You can insert rows of data into the table variable. 

    Saturday, September 1, 2007 9:01 AM
  • 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>

     

    Sunday, September 2, 2007 12:52 AM
  • 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. "

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 2, 2007 4:18 AM
  • 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 ?  

    Sunday, September 2, 2007 4:54 AM
  • 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.

    Sunday, September 2, 2007 9:05 AM
  • 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

    Saturday, April 30, 2011 4:27 AM