Error using dynamic sql with variables

Question

Because I'm using dynamic variables to read linkedservers (excel files) I need to put my SQL statements into an executable string.

I've simplified my statements and included a SQL table, but get the same error on my @records Can anyone see what I'm doing wrong?

-- When I change it to the following: DECLARE @V_SQL NVARCHAR(1000) declare @records int

SET @V_SQL = 'SELECT ' + @records + ' = count(*) FROM MyTable WHERE page1id IS NULL'; EXEC (@V_SQL);

-- Returns this error: Conversion failed when converting the varchar value 'SELECT ' to data type int.

Answer 1

0

use sp_executesql

declare @V_SQL nvarchar(1000),            
        @records int            
            
SET @V_SQL = 'SELECT @records = count(*) FROM MyTable WHERE page1id IS NULL'            
            
exec sp_executesql @V_SQL, N'@records int OUTPUT', @records OUTPUT

answered Jul 13 '10 at 10:58 AM

Squirrel 1
1.6k ● 1 ● 3

@Squirrel Are you going to visit asksqlservercentral when they merge this site into theirs? They should move your rep (now karma) over.

Jul 13 '10 at 07:53 PM Scot Hauder

Oh.. didn't know that. Sure, i will be there

Jul 13 '10 at 10:03 PM Squirrel 1

Thanks. That did the trick.

Jul 20 '10 at 04:32 AM Tim 3 1

(comments are locked)

Error using dynamic sql with variables

Follow this question

Related Questions