Error using dynamic sql with variables

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.

more ▼

asked Jul 13 '10 at 08:05 AM in Default

Tim 3 1 gravatar image

Tim 3 1
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Jul 13 '10 at 10:58 AM

Squirrel 1 gravatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 13 '10 at 08:05 AM

Seen: 807 times

Last Updated: Jul 13 '10 at 08:05 AM