Exec Failed Because the Name Not a Valid Identifier

exec failed because the name not a valid identifier?

Try this instead in the end:

exec (@query)

If you do not have the brackets, SQL Server assumes the value of the variable to be a stored procedure name.

OR

EXECUTE sp_executesql @query

And it should not be because of FULL JOIN.

But I hope you have already created the temp tables: #TrafficFinal, #TrafficFinal2, #TrafficFinal3 before this.

---

Please note that there are performance considerations between using EXEC and sp_executesql. Because sp_executesql uses forced statement caching like an sp.

More details here.

---

On another note, is there a reason why you are using dynamic sql for this case, when you can use the query as is, considering you are not doing any query manipulations and executing it the way it is?

the name is not a valid identifier. error in dynamic stored procudure

Some parameters in a query string are not parsed correctly, and you are using dynamic sql it must be executed by EXECUTE sp_executesql statement. This is the correct way to execute dynamic sql:

ALTER PROCEDURE  [dbo].[Asbabbazi_A]
    @name_product nvarchar(50),
    @first_price int,
    @final_price int,
    @collection_1 nvarchar(30),
    @id_state tinyint
AS
BEGIN
DECLARE @SQLstring nvarchar(1000)
DECLARE @PARAMS nvarchar(1000)  
set @SQLstring = 'SELECT IDproduct,name_product,first_price,final_price,max_registered_price,
                  date_record_shamsi,final_date_view_shamsi,
                   count_views,image_1,collection_1 from Table_asbabbazi where active=0 '
if(@name_product != 'no name')
set @SQLstring = @SQLstring + ' AND name_product  LIKE ''%' + @name_product  + '%''' + ' '
if (@final_price != 0)
set @SQLstring = @SQLstring +  ' AND first_price between  ' + CONVERT(nvarchar(1000), @first_price)  + ' AND ' + CONVERT(nvarchar(1000), @final_price) + ' '
if (@collection_1 != 'انتخاب کنید')
set @SQLstring = @SQLstring + ' AND collection_1 = ''' + @collection_1 + ''' '

if (@id_state != 0)
set @SQLstring = @SQLstring + ' AND id_state = ' + CONVERT(nvarchar(1000), @id_state) + ' '

EXECUTE sp_executesql @SQLstring
END

Not a valid Identifier T SQL Dynamic SQL inside a CTE

You need to move AS keyword closer to WITH and you need to reformat the query similarly to this:

;with CTE_RESULT AS -- NOTE: AS moved here
(
    SELECT
        CAMP_ABBR,
        ORIG_CALL_DATE,
        DONOR_ID,
        ACCOUNT_NUMBER_100,
        COMPANY_NAME_100,
        FIRST_NAME,
        LAST_NAME,
        PHONE,
        STREET1,
        STREET2,
        STATE,
        CITY,
        ZIP_CODE,
        WAVE_NAME,
        EMAIL,
        ORIGINAL_EMAIL,
        CODE1,
        DISPOSITION_CODE,
        DESCRIPTION
    FROM placeholder1 t1
    WHERE COLUMN IN(''01'',''02'')
) -- AS -- this AS here should be causing the error.
SELECT * FROM CTE_RESULT

Read more about Common Table Expressions syntax here: WITH common_table_expression.

SQL not valid identifier error

You can neither assign directly from an EXEC statement, nor can you directly assign a variable from inside an EXECd dynamic SQL (it's out of scope). This also shouldn't really be called from a function as SQL expects functions to be deterministic and this really isn't (In fact even if you fix your error, it still probably won't work.)

Assuming you change how you're calling this code you'll need to know how to get your value from the dynamic sql. If you want to pass a variable from a dynamic SQL string, you must use EXEC SP_ExecuteSQL with an output parameter.

Here is a simple example:

DECLARE @whole_no int;
DECLARE @SQL nvarchar(500);
DECLARE @Parms nvarchar(500);;

SET @SQL = N'SELECT @whole_noOUT = 1';
SET @Parms = N'@whole_noOUT int OUTPUT';

EXECUTE sp_executesql @SQL, @Parms, @whole_noOUT=@whole_no OUTPUT;
SELECT @WHOLE_NO;

In this example, we're setting a param definition, and an output parameter. In general, sp_executesql is the preferred method for calling dynamic sql since it can be parameterized which both increases security by avoiding the need to concatenating sql directly, but also allows plan reuse when your sql is fully parameterized.

I should also add the obligatory warning that your really shouldn't be building dynamic sql in such a cavalier manner as you open yourself up to SQL injection, either now or in the future. I understand that you cannot parameterize your particular query but, at the very least, you should be using QUOTENAME() on your column name instead of hardcoding the [].

Read more on sp_executesql here: http://msdn.microsoft.com/en-us/library/ms188001.aspx
And more on QUOTENAME() here: http://msdn.microsoft.com/en-us/library/ms176114.aspx

Dynamic SQL MERGE: Not a valid identifier

I suggest to use sp_executesql overEXEC:

DECLARE @sql NVARCHAR(MAX) =   
'MERGE dbo.temp_share_test AS t 
USING (SELECT * FROM dbo.temp_share_test WHERE grp_id = 2 AND co_code = ''A'') 
       AS s  
ON (t.grp_id = 2 AND t.co_code = ''H'' AND t.sp_no = s.sp_no 
    AND t.sp_code = s.sp_code) 
WHEN NOT MATCHED THEN INSERT (grp_id, co_code,sp_code,sp_no,sp_type,sp_colour) 
VALUES (2, ''H'',s.sp_code,s.sp_no,s.sp_type,s.sp_colour);'

EXEC sp_executesql @sql;

DBFiddle Demo

When dealing with dynamic SQL I highly recommend to read: The Curse and Blessings of Dynamic SQL

Msg 203, Level 16, State 2, is not a valid identifier

Please try this, changed execute @query to execute (@query):

ALTER proc getQuestion
    @qNo bigint,
    @total bigint,
    @next nvarchar(max)
as

begin 
    declare @hisa bigint
    set @hisa=@total/3

    if(@qNo<=@total/3)
    begin
      declare @query nvarchar(max)
      set @query=('select top(1) * from tlb_Question 
      inner join tlb_options on tlb_options.qID=tlb_Question.id and tlb_Question.qNumber=1 and tlb_Question.id not in ('+cast(@next as varchar)+')')
      --print @query
      execute (@query)
    end
end

---

Related Topics