x

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, 2010 at 08:05 AM in Default

avatar 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, 2010 at 10:58 AM

avatar image

Squirrel 1
1.6k 1 3 5

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x56

asked: Jul 13, 2010 at 08:05 AM

Seen: 1051 times

Last Updated: Jul 13, 2010 at 08:05 AM

Copyright 2016 Redgate Software. Privacy Policy